tsql stored procedure bulk insert

[Guest]

Hi,

The following code works perfectly:

cfquery datasource="#SESSION.DSN#">
  BULK INSERT dbo.Syndicated
  FROM '#selectedfile#'
  WITH
  (
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n',
   FIRSTROW = 2
  )
</cfquery>

I created this stored procedure:

CREATE

PROCEDURE dbo.

uspInsertSyndicatedRecords

@selectedfile

varchar(2000

)

AS

BEGIN

BULK INSERT

Syndicated

FROM

'@selectedfile'

WITH

(

FIELDTERMINATOR = ','

,

ROWTERMINATOR = '\n'

,

FIRSTROW =

2

)

END

___________

I'm getting an error: 

The file "@selectedfile" does not exist.

Anyone know if there are issues with the file path in a stored procedure.  #selectedfile# = C:\ColdFusion8\wwwroot\cfdocs\mis35\luxury.csv

Thanks!

cfwild

[-__cfSearching__-]

IIRC, you cannot use a dynamic filename unless you use dynamic sql.  Also, do not enclose variable names in quotes. That prevents the variable from being evaluated.

[TA-Selene]

Unfortunately, dynamic queries don't work within TSQL - meaning you can't substibute table and column names with variables.  You can only use variables for the comparison values or select values.

However, you can create a variable, build the SQL statement in that variable and execute it:

DECLARE @SQLString VARCHAR(max)

SET @SQLString = 'SQL Start ....' + @MyVar + ' .... SQLEnd'

EXEC(@SQLString)

Now, if the variable being concatinated is not a string, you will need to cast/convert it to string, which just adds tons of fun to the equation.

Hope this helps!