Incorrect syntax near @File

I am using this bulk insert command in procedure below. I am passing variable @.File inside of the procedure and I do not know the right syntax for it. Could you pls help me. When I enter the path for the file like 'C:\imp_file.csv' it works.

Thanks

ALTER procedure sp_BulkInsert1
@.File varchar(1000)

AS

BULK INSERT SQL_Tests.dbo.xRSA FROM @.File
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
CODEPAGE = 'RAW',
TABLOCK
)Because the syntax requires a constant, you can't use a variable... At least not directly anyway!ALTER procedure sp_BulkInsert1
@.File varchar(1000)
AS

EXECUTE ('BULK INSERT SQL_Tests.dbo.xRSA FROM ''' + @.File + '''
WITH
(
DATAFILETYPE = ''char''
, FIELDTERMINATOR = '',''
, ROWTERMINATOR = ''\n''
, CODEPAGE = ''RAW''
, TABLOCK
)' )

RETURN
GO-PatP|||:) Because the syntax requires a constant, you can't use a variable... At least not directly anyway!ALTER procedure sp_BulkInsert1
@.File varchar(1000)
AS

EXECUTE ('BULK INSERT SQL_Tests.dbo.xRSA FROM ''' + @.File + '''
WITH
(
DATAFILETYPE = ''char''
, FIELDTERMINATOR = '',''
, ROWTERMINATOR = ''\n''
, CODEPAGE = ''RAW''
, TABLOCK
)' )

RETURN
GO-PatP

Why do I need to use Execute Command?|||If you check BOL for the syntax of the BULK INSERT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp) command, you'll notice that the syntax requires a constant for the file name. The only way I know to make a variable appear as a constant is to execute it indirectly, via the EXECUTE statement. We're basically working around a limitation in the supoorted syntax.

-PatP|||Because it's dynamic sql...

Pat I can't get the injection in to the vien...maybe you can...I'm sure it can be done

USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(8000))
GO

CREATE PROC sp_BulkInsert1
@.File varchar(1000)
AS

EXECUTE ('BULK INSERT myTable99 FROM ''' + @.File + '''
WITH
(
DATAFILETYPE = ''char''
, FIELDTERMINATOR = '',''
, ROWTERMINATOR = ''\n''
, CODEPAGE = ''RAW''
, TABLOCK
)' )

RETURN
GO

DECLARE @.x varchar(1000)
SELECT @.x = 'c:\config.sys' + '''' + ' GO SELECT ' + '''' + 'Lets execute some damaging sql' + '''' + ' GO'
EXEC sp_BulkInsert1 @.x
GO

DROP PROC sp_BulkInsert1
DROP TABLE myTable99
GO|||I'd use one of my quote fixers. I'm having to shoot from the hip since my system is toast at the moment, but it goes something like:CREATE FUNCTION dbo.FixQuote(@.pcIn VARCHAR(8000)) RETURNS VARCHAR(8000)
BEGIN
RETURN Replace(@.pcIn, '''', ''')
ENDGiven that little function, you could wrap it around the parameter to inhibit code injection. Note that it is MUCH better to prevent the injection at the source (the client/middleware machine) rather than trying to inhibit it at SQL Server.

-PatP

INCORRECT SYNTAX NEAR "STRING" FOR ALTER SQL

HELP

I am trying to create a new column for every file in a folder

but i keep getting an sql exception - incorrect syntax near ' whatever the value of the file name is'

it works if i just type in the value directly

my code look like this

fsofolder = CreateObject("Scripting.FileSystemObject")
folder = fsofolder.GetFolder("the path to the Files\")
files = folder.Files
For Each objfile In files
sname = objfile.Name

cmd3.CommandText = "ALTER TABLE NEW ADD " & "' " & sname & " ' " & " nvarchar(MAX)"

DatabaseConnection.Open()

Try

cmd3.Connection = DatabaseConnection
cmd3.ExecuteNonQuery()
Catch ex As SqlException
MsgBox(ex.Message)
End Try

DatabaseConnection.Close()

The syntax should be Alter TabletablenameADD COLUMNcolumnname datatype

There is no place for apostophe delimiters in the syntax, and the word COLUMN is needed too.

|||

Thanks

I figured out what the problem was

cmd3.CommandText = "ALTER TABLEtablename ADD " & "'[" & sname & "]" & " nvarchar(MAX)"

It was not accepting eg Q45654656.txt as a column name

but accepting [Q45654656]

|||

database objects can't have a '.' in their names

|||

It did actually

I missed-type in the last post

the difference was the [] that enclosed the string

it accepted

sname = [textfile.txt]

but not

sname = textfile.txt

as the column name

|||

I have another question however,

is it possible to have in one string a sql command to insert into database tableonlyif the column is empty or NULL ?

maybe something like

cmd.CommandText = "INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...) WHERE Columnvalue is NULL"

I appreciate the help

|||

Well, the a

fredi:

I have another question however,

is it possible to have in one string a sql command to insert into database tableonlyif the column is empty or NULL ?

maybe something like

cmd.CommandText = "INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...) WHERE Columnvalue is NULL"

I appreciate the help

Well, why don't you type in that sql statement for yourself and tell us? :)

However, wanting to do what you asked does not make sense to me. I could understand if you said you wanted to update a column only if it was null, because presumably you don't want to lose the old value. By definition, if you want to insert a record, the record shouldn't already exist, so how could a non-existent record have a value in any column?

FYI, it is possible to say (instead of the VALUES (value1, etc.)), SELECT value1, value2, etc.

|||

well here is what i am trying to do and able to do so far

-look into a folder

-create a database table in sql server using the create sql command

-alter the table and create a column named for each file name in the folder

-read each of the text file data into each column

however if i run the code again it adds the textfile data into the same columns again

I just need a statement to say if the column already has data then don't do the all the above steps

I hope this explains my situation

These work:

cmd2.CommandText = "CREATE TABLE " & DatabaseTableName & "(" & ISTCOLUMN& " nvarchar(MAX))"
cmd3.CommandText = "ALTER TABLE " & DatabaseTableName & " ADD " & sname1 & " nvarchar(MAX)"

cmd4.CommandText = "INSERT INTO " & DatabaseTableName & "( " & sname1 & " )" & "VALUES ( '" & filefields(i) & "' )"

How will I check if sname column is Null and only insert the values of filefields into it?

thanks

|||

Am I correct in saying the following?

If the column exists in the table, then you must have populated it with a value?

Because if that is true, then all you have to do is query INFORMATION_SCHEMA.COLUMNS and find out if the column exists.

If that is not true, then you can query the table to see if the column exists.

If it does, query the table to see if it has a row at all, and if so, a value in the column you are interested in.

If yes, do nothing.

If no, update the record.

Now, I have to tell you that what you are doing almost certainly violates relational data modeling.

I would be EXTREMELY SUSPICIOUS of a database design that required me to add a column to a table for every file in a directory.

The odds of this being a good database design are very, very low. Lower than the chance of my being hit by lightning this year.

Standard relational theory would tell us to create a ROW, not a COLUMN, for every file in the directory.

I am not telling you that your database design is wrong. I am telling you that it is very likely wrong, and that you should re-think your approach to be very, very sure the approach you are taking is the right one.

How many files might there be in the directory? Did you know there are limits as to how many columns can be defined for a table? Will you have more than that limit? Did you know that there are limits as to the number of bytes that can be returned for a row in a query? How many filename columns with their values will it take to go over that limit?

See <http://technet.microsoft.com/en-us/library/ms143432.aspx> for details on sql server limits.

Please reconsider your design or - to educate us all - explain why the situation you are in requires such an unusual design.

|||

Thanks David,

If the column exists in the table, then you must have populated it with a value?

is not true. I first create an empty table with at least one column then I add more columns as they show up (i.e as the text files get created). That might not be as important now as the structure of the database itself.

To say that I am fairly new to Database design would be an understatement. Thanks for enlightening me. I am still in an early stage of the design phase and you just showed me how flawed the database would be if I end up going over limits. I would reconsider my approach.

|||

Glad to have helped! I've got 25 years of computing mistakes behind me, so it's easier for me to recognize them.. Some of them are old friends. :)

So, to wrap up this thread, the correct answer is "Don't do it."

Inconsistent Subscription Success

Hi,
I've got a production SQL Reporting Services installation and have
created some file share subscriptions for one of the reports.
Sometimes the subscriptions work and sometimes they don't. My customer
has now had enough and wants to have them working all of the time
(unsurprisingly!).
I have spent the whole day testing various things to try to get some
consistency and haven't been able to prove anything. If, for example,
I create 5 new subscriptions (either to run all at the same time or to
run one minute after each other), any number of the subscriptions will
run properly (ie. and will create the file on the file share) -
sometimes none will run, sometimes a few will run, sometimes all five
will run. I have just created eight new subscriptions in an absolutely
identical manner and only one of the eight ran properly (and it wasn't
the first or last one).
I cannot find any error messages anywhere in the system, ie. in event
viewer, in the SQL RS logs, in the SQL logs, etc., and as nothing is
changing on the system - ie. local user rights, NTFS permissions, IIS
permissions, SQL permissions, etc. - I can't work out why the
subscriptions work sometimes and not others.
I've looked through a lot of the Google postings and can see that other
people have similar things.
If anyone can offer any suggestions so that I can get SQL RS to work
properly, please let me know - I'll really appreciate it.
Cheers,
Rich
(MCSE MCSD MCDBA)Hello Richard,
Can you see all the corresponding jobs created for your subscriptions? When
a subscription does not work, can you see that the job in SQL Server Agent is
running or was triggered as expected?
Ricardo.
"richard.warner@.zurich.com" wrote:
> Hi,
> I've got a production SQL Reporting Services installation and have
> created some file share subscriptions for one of the reports.
> Sometimes the subscriptions work and sometimes they don't. My customer
> has now had enough and wants to have them working all of the time
> (unsurprisingly!).
> I have spent the whole day testing various things to try to get some
> consistency and haven't been able to prove anything. If, for example,
> I create 5 new subscriptions (either to run all at the same time or to
> run one minute after each other), any number of the subscriptions will
> run properly (ie. and will create the file on the file share) -
> sometimes none will run, sometimes a few will run, sometimes all five
> will run. I have just created eight new subscriptions in an absolutely
> identical manner and only one of the eight ran properly (and it wasn't
> the first or last one).
> I cannot find any error messages anywhere in the system, ie. in event
> viewer, in the SQL RS logs, in the SQL logs, etc., and as nothing is
> changing on the system - ie. local user rights, NTFS permissions, IIS
> permissions, SQL permissions, etc. - I can't work out why the
> subscriptions work sometimes and not others.
> I've looked through a lot of the Google postings and can see that other
> people have similar things.
> If anyone can offer any suggestions so that I can get SQL RS to work
> properly, please let me know - I'll really appreciate it.
> Cheers,
>
> Rich
> (MCSE MCSD MCDBA)
>|||Hi, Ricardo.
Thanks for your reply.
Yes - the jobs all show in the SQL Server Agent jobs view in Enterprise
Manager. They show as Succeeded (<date> <time>). As SQL server
considers them to have succeeded, there is no entry in the event log.
During some of the testing, I modified one of the jobs (Notification
tab) so that it wrote to the Windows application event log "whenever
the job completes", and all that did was write an entry to the event
log to say the job had completed successfully!
When I look in the Execution Log table of the SQL RS database, I can
see that not all of the subscriptions show there.
When I look in the Subscriptions table of the SQL RS database, I can
see all of the subscriptions, but the ones that didn't work properly
still show a LastRun time of <NULL>.
Cheers,
Rich|||Hello Richard,
It seems that there is a mismatch between the jobs in SQL and the
subscriptions. Have you tried recreating the jobs? Stop the ReportServer
service, then delete all SQL Agent jobs related to reporting services (all
jobs that have category "Report Server"), and then start the ReportServer
service. It will recreate the necessary SQL Agent jobs.
Ricardo.
"richard.warner@.zurich.com" wrote:
> Hi, Ricardo.
> Thanks for your reply.
> Yes - the jobs all show in the SQL Server Agent jobs view in Enterprise
> Manager. They show as Succeeded (<date> <time>). As SQL server
> considers them to have succeeded, there is no entry in the event log.
> During some of the testing, I modified one of the jobs (Notification
> tab) so that it wrote to the Windows application event log "whenever
> the job completes", and all that did was write an entry to the event
> log to say the job had completed successfully!
> When I look in the Execution Log table of the SQL RS database, I can
> see that not all of the subscriptions show there.
> When I look in the Subscriptions table of the SQL RS database, I can
> see all of the subscriptions, but the ones that didn't work properly
> still show a LastRun time of <NULL>.
> Cheers,
>
> Rich
>|||As you suggested, we stopped the ReportServer service, deleted the
Report Server SQL Agent jobs, then restarted the ReportServer service,
and the Report Server jobs were recreated in the SQL Agent. However,
it hasn't helped resolve the problem.
I have just created five new run-once subscriptions - each configured
to run one minute after the last. The first three were successful, the
fourth wasn't, and the fifth was. Looking in the SQL Agent, all of
them show as Succeeded (<date> <time>). Again - all five subscriptions
were created in an absolutely identical manner.
Have you got any other suggestions?
Cheers,
Rich|||Hello Richard,
What is the status of the subscription in the Subscriptions page? Does it
show that all subscription run and all of them were successful?
In the logs, can you see the calls for all five subscriptions?
Ricardo.
"richard.warner@.zurich.com" wrote:
> As you suggested, we stopped the ReportServer service, deleted the
> Report Server SQL Agent jobs, then restarted the ReportServer service,
> and the Report Server jobs were recreated in the SQL Agent. However,
> it hasn't helped resolve the problem.
> I have just created five new run-once subscriptions - each configured
> to run one minute after the last. The first three were successful, the
> fourth wasn't, and the fifth was. Looking in the SQL Agent, all of
> them show as Succeeded (<date> <time>). Again - all five subscriptions
> were created in an absolutely identical manner.
> Have you got any other suggestions?
> Cheers,
>
> Rich
>|||Hi, Ricardo.
Thanks for your quick reply again.
Sorry - I was mistaken in my last mail - three of the subscriptions ran
successfully and two of them didn't (not four and one, as I'd said).
In the Subscriptions page, the three successful subscriptions show as
"File xx.xx was written to xx", and the two unsuccessful subscriptions
show as "New subscription".
Which log are you referring to? If you're referring to the
ReportServer_<date>_<time>.log file in the SQL RS LogFiles directory,
then yes - I can see a line for the creation of each of the five
subscriptions. The line is as follows:
"aspnet_wp!subscription!bf4!<date>-<time>:: Subscription Created for
report /<folder>/<report> at <date>T<time> by <me>"
This line occurs five times and corresponds exactly with the times that
I created the subscriptions.
Cheers,
Rich|||Hello Richard,
If the subscription stays at "New subscription" then it means it hasn't run,
or (I think) something happened and it is retrying. Has the process
dealocked? Are you trying to write the same filename all the time? Is it
possible that there was a sharing violation? In the logs, after the
subscription was queued the first time, can you see whether RS is queing the
two "missing" subscriptions again? Has the status changed in the
Subscriptions page?
Ricardo.
"richard.warner@.zurich.com" wrote:
> Hi, Ricardo.
> Thanks for your quick reply again.
> Sorry - I was mistaken in my last mail - three of the subscriptions ran
> successfully and two of them didn't (not four and one, as I'd said).
> In the Subscriptions page, the three successful subscriptions show as
> "File xx.xx was written to xx", and the two unsuccessful subscriptions
> show as "New subscription".
> Which log are you referring to? If you're referring to the
> ReportServer_<date>_<time>.log file in the SQL RS LogFiles directory,
> then yes - I can see a line for the creation of each of the five
> subscriptions. The line is as follows:
> "aspnet_wp!subscription!bf4!<date>-<time>:: Subscription Created for
> report /<folder>/<report> at <date>T<time> by <me>"
> This line occurs five times and corresponds exactly with the times that
> I created the subscriptions.
> Cheers,
>
> Rich
>|||Hi, Ricardo.
Are you referring to the aspnet_wp.exe process? If so, there are no
events in the event log showing that the process has deadlocked and
been restarted.
Each subscription is created to write to a different file name.
I don't think it's possible that there was a sharing violation. In
each case, the file doesn't exist before the subscription runs and
nothing tries to open the file subsequently.
In the log file, RS isn't queuing the two missing subscriptions again.
The status hasn't changed in the Subscriptions page.
In the ReportServerService_<date>_<time>.log file, I can see the
successful subscriptions being run. These ran today at 13:23, 13:25
and 13:27. The missing ones were scheduled to run at 13:24 and 13:26.
Here is a typical section of the log from the successful subscriptions:
ReportingServicesService!dbpolling!a20!02/11/2005-13:27:04::
EventPolling processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!dbpolling!d64!11/02/2005-13:27:04::
EventPolling processing item ce6bf843-1a37-4c0a-aa76-218284fafc1a
ReportingServicesService!library!d64!11/02/2005-13:27:04:: Schedule
69782008-e708-4927-9eeb-f1640c7ec996 executed at 11/02/2005 13:27:04.
ReportingServicesService!schedule!d64!11/02/2005-13:27:04:: Creating
Time based subscription notification for subscription:
63b17fae-0d44-4d2e-8a75-c0ac68ebd080
ReportingServicesService!library!d64!11/02/2005-13:27:04:: Schedule
69782008-e708-4927-9eeb-f1640c7ec996 execution completed at 11/02/2005
13:27:04.
ReportingServicesService!dbpolling!d64!11/02/2005-13:27:04::
EventPolling finished processing item
ce6bf843-1a37-4c0a-aa76-218284fafc1a
ReportingServicesService!dbpolling!a20!02/11/2005-13:27:04::
NotificationPolling processing 1 more items. 1 Total items in internal
queue.
ReportingServicesService!dbpolling!d64!11/02/2005-13:27:04::
NotificationPolling processing item
ceeebad5-0578-4b59-af37-5fd8305ddbac
ReportingServicesService!library!d64!11/02/2005-13:27:04:: i INFO: Call
to RenderFirst( '/<folder>/<report>' ) !-- this line modified by me in
Google post to hide folder/report name
ReportingServicesService!library!d64!11/02/2005-13:27:06:: i INFO:
Initializing EnableExecutionLogging to 'True' as specified in Server
system properties.
ReportingServicesService!notification!d64!11/02/2005-13:27:06::
Notification ceeebad5-0578-4b59-af37-5fd8305ddbac completed. Success:
True, Status: File E5.pdf was written to \\<server>\<share>,
DeliveryExtension: Report Server FileShare, Report: ARMReport1, Attempt
0 !-- this line modified by me again
ReportingServicesService!dbpolling!d64!11/02/2005-13:27:06::
NotificationPolling finished processing item
ceeebad5-0578-4b59-af37-5fd8305ddbac
I have created 12 more subscriptions running at various intervals (1
minute, 2 minutes, 3 minutes, 5 minutes and 10 minutes) - mixing these
intervals up to see if it's anything to do with how far apart the jobs
are running. I know this is grabbing at straws, but I'm happy to try
anything! :-> I've just realised that the results from these
subscriptions will be in soon, so I'll hold on before posting this and
will include the results ...
New results from 12 subscriptions:
1 - (Time = 15:40) - Didn't run
2 - (Time = 15:41) - Didn't run
3 - (Time = 15:43) - Ran
4 - (Time = 15:45) - Ran
5 - (Time = 15:48) - Didn't run
6 - (Time = 15:50) - Didn't run
7 - (Time = 15:52) - Didn't run
8 - (Time = 15:53) - Ran
9 - (Time = 15:55) - Ran
10 - (Time = 15:58) - Didn't run
11 - (Time = 16:03) - Didn't run
12 - (Time = 16:13) - Didn't run
That seems fairly inconculsive to me!
Any other thoughts?
Cheers,
Rich|||Hello Richard,
I am talking about the process in the database. Are you running the reports
from a stored procedure, or a simple query? If you run the stored procedures
or queries in Query Analyzer at those intervals, does it always run and
return data? Do they deadlock?
Ricardo.
"richard.warner@.zurich.com" wrote:
> Hi, Ricardo.
> Are you referring to the aspnet_wp.exe process? If so, there are no
> events in the event log showing that the process has deadlocked and
> been restarted.
> Each subscription is created to write to a different file name.
> I don't think it's possible that there was a sharing violation. In
> each case, the file doesn't exist before the subscription runs and
> nothing tries to open the file subsequently.
> In the log file, RS isn't queuing the two missing subscriptions again.
> The status hasn't changed in the Subscriptions page.
> In the ReportServerService_<date>_<time>.log file, I can see the
> successful subscriptions being run. These ran today at 13:23, 13:25
> and 13:27. The missing ones were scheduled to run at 13:24 and 13:26.
> Here is a typical section of the log from the successful subscriptions:
> ReportingServicesService!dbpolling!a20!02/11/2005-13:27:04::
> EventPolling processing 1 more items. 1 Total items in internal queue.
> ReportingServicesService!dbpolling!d64!11/02/2005-13:27:04::
> EventPolling processing item ce6bf843-1a37-4c0a-aa76-218284fafc1a
> ReportingServicesService!library!d64!11/02/2005-13:27:04:: Schedule
> 69782008-e708-4927-9eeb-f1640c7ec996 executed at 11/02/2005 13:27:04.
> ReportingServicesService!schedule!d64!11/02/2005-13:27:04:: Creating
> Time based subscription notification for subscription:
> 63b17fae-0d44-4d2e-8a75-c0ac68ebd080
> ReportingServicesService!library!d64!11/02/2005-13:27:04:: Schedule
> 69782008-e708-4927-9eeb-f1640c7ec996 execution completed at 11/02/2005
> 13:27:04.
> ReportingServicesService!dbpolling!d64!11/02/2005-13:27:04::
> EventPolling finished processing item
> ce6bf843-1a37-4c0a-aa76-218284fafc1a
> ReportingServicesService!dbpolling!a20!02/11/2005-13:27:04::
> NotificationPolling processing 1 more items. 1 Total items in internal
> queue.
> ReportingServicesService!dbpolling!d64!11/02/2005-13:27:04::
> NotificationPolling processing item
> ceeebad5-0578-4b59-af37-5fd8305ddbac
> ReportingServicesService!library!d64!11/02/2005-13:27:04:: i INFO: Call
> to RenderFirst( '/<folder>/<report>' ) !-- this line modified by me in
> Google post to hide folder/report name
> ReportingServicesService!library!d64!11/02/2005-13:27:06:: i INFO:
> Initializing EnableExecutionLogging to 'True' as specified in Server
> system properties.
> ReportingServicesService!notification!d64!11/02/2005-13:27:06::
> Notification ceeebad5-0578-4b59-af37-5fd8305ddbac completed. Success:
> True, Status: File E5.pdf was written to \\<server>\<share>,
> DeliveryExtension: Report Server FileShare, Report: ARMReport1, Attempt
> 0 !-- this line modified by me again
> ReportingServicesService!dbpolling!d64!11/02/2005-13:27:06::
> NotificationPolling finished processing item
> ceeebad5-0578-4b59-af37-5fd8305ddbac
> I have created 12 more subscriptions running at various intervals (1
> minute, 2 minutes, 3 minutes, 5 minutes and 10 minutes) - mixing these
> intervals up to see if it's anything to do with how far apart the jobs
> are running. I know this is grabbing at straws, but I'm happy to try
> anything! :-> I've just realised that the results from these
> subscriptions will be in soon, so I'll hold on before posting this and
> will include the results ...
> New results from 12 subscriptions:
> 1 - (Time = 15:40) - Didn't run
> 2 - (Time = 15:41) - Didn't run
> 3 - (Time = 15:43) - Ran
> 4 - (Time = 15:45) - Ran
> 5 - (Time = 15:48) - Didn't run
> 6 - (Time = 15:50) - Didn't run
> 7 - (Time = 15:52) - Didn't run
> 8 - (Time = 15:53) - Ran
> 9 - (Time = 15:55) - Ran
> 10 - (Time = 15:58) - Didn't run
> 11 - (Time = 16:03) - Didn't run
> 12 - (Time = 16:13) - Didn't run
> That seems fairly inconculsive to me!
> Any other thoughts?
> Cheers,
>
> Rich
>|||Hi, Ricardo.
Sorry for the delay in my reply. I've made slight progress in that I
know that one of the two SQL RS IIS servers is causing a problem (I
probably should have said before that we have two SQL RS servers
talking to one SQL RS database). By stopping the ReportServer service
on the second SQL RS server, all of the subscriptions now work (even
though the subscriptions are only configured on one of the servers, and
are only saving files on the same server, etc - so as far as I was
concerned, the second server wasn't involved). I'll look into this
more tomorrow, but for now my solution is just to keep that service
stopped on the second server and lose the resilience that the second
SQL RS server was providing.
Thanks again for your help.
Rich

including fields that are not measurement in the fact table

Hi,

I built a cube from a flat file. All of my dimension key fields and measurements fields are from that flat file. The problem that the flat file still have othe fields that were not used as dimension key or measurement and the cube user want to see these other fields as part of the cube. If these other fields are not dimension or measurement how I should bring them to the cube to be seen by the cube user?

Please help!

Aref

Unfortunately No.

In order to return the information to the client application, you will have to include a column as part of the measure group or dimension. Take a look at the dimensions of type Fact, they are intended for the cases like yours.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Maybe what your looking for is a junk dimension.

Look at:

http://rkimball.com/html/designtipsPDF/DesignTips2003/KimballDT48DeClutter.pdf

I think gives you a more clean solution for your problem.

Including CSS and XSLT in same XML file?

Is it possible to include both a CSS and XSLT in the same XML file? If
so, are there any tricks to this to make it work? I'm new to XML, but
from my initial observations, XSLT does not look like the tool to do the
work of my CSS file. XSLT seems more like a layout scripting language
so far, but seems like it would be tedious to define TD and TH tags.
So, I'm trying to handle this with CSS files in addtion to XSLT files.
I tried putting the following lines in my XML file, but I'm not seeing
the formating changes in the result.
<?xml-stylesheet type="text/xsl" href="http://links.10026.com/?link=calendar.xsl"?>
<?xml-stylesheet type="text/css" href="http://links.10026.com/?link=calendar.css"?>
Can this be done? If so, what am I doing wrong?
Thanks in advance.
Robert
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
"Robert Taylor" <anonymous@.devdex.com> wrote in message
news:uXV6bYlTEHA.1168@.TK2MSFTNGP11.phx.gbl...
[snip]
> I tried putting the following lines in my XML file, but I'm not seeing
> the formating changes in the result.
> <?xml-stylesheet type="text/xsl" href="http://links.10026.com/?link=calendar.xsl"?>
> <?xml-stylesheet type="text/css" href="http://links.10026.com/?link=calendar.css"?>
> Can this be done? If so, what am I doing wrong?
You need to put the link to the css file in the output of the XSL...
Bryant
|||Bryant,
Thanks. I'll try this out.
Robert
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Include File Concept

Hi All
I wanted to check if there is any feature like the INCLUDE file concept in ASP.
I have set of variables that I need to declare in each of the Stored Procedures by default, so that I can maintain this list in single place, dont have to worry about changing the list for every SP in the database.
Does SQL Server provide any such feature.
If not, is this planned in YUKON?You could try creating a template for use in Query Analyzer. You can modify
one of the existing procedure and save it as another name. See BOL:Using
Templates in Query Analyzer for more information.
"Prasanna" <pprabhu@.pbs.solutionsiq.com> wrote in message
news:FD16045E-A2F1-4534-88A2-65DD471DA1EE@.microsoft.com...
> Hi All
> I wanted to check if there is any feature like the INCLUDE file concept in
ASP.
> I have set of variables that I need to declare in each of the Stored
Procedures by default, so that I can maintain this list in single place,
dont have to worry about changing the list for every SP in the database.
> Does SQL Server provide any such feature.
> If not, is this planned in YUKON?|||Hello,
Templates are boilerplate files containing SQL scripts that help you create
objects in the database. There are however, unable to help maintain the set
of variables in a single place.
Currently, SQL Server does not provide this feature you require. As I
understand, SQL Server Yukon will provide .NET Programming Features, but
because Yukon is not a released version of SQL Server, we are unable to
guarantee it will have the feature like the INCLUDE file concept in ASP.
Thanks for understanding.
For additional information regarding .NET programming Features, please
refer to the following article:
SQL Server Yukon: .NET Programming Features
http://server1.msn.co.in/sp03/teched/pop5.html
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.
Thanks for using MSDN newsgroup
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

in what situations principal database might become unavailable ?

Is it correct to say

in any case exept when data or log file not available pricipal db will be availabe

and manual failover could be done only from from principal?

Refer to ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/00a5fbc0-af53-46a7-bfb1-405b9ad2427b.htm BOL topic in this case for your doubts.

IN SQL SERVER BCP

Hi,

I have to insert a data from text file to Sql Server Using BULK INSERT ie BCP. Throgh qurey wise it execute fine.
But using stored procedure it does not work..here just i have to pass 1 parameter ie file name. The err is

" Could not bulk insert. File '@.BasicFile' does not exist." How can i solve the prolblem ?

Thanks and Regards,

ArulIt looks like you are trying to use a variable filename for your source file. As far as I know, you will need to use dynamic SQL to accomplish this.

SET @.SQL = "BULK INSERT myTable '"+@.PathFileName+"' WITH (FIELDTERMINATOR = ',') "
EXEC (@.SQL)

Terri