Import csv file, trouble with the file name when given as parameter.

Hello,

I have this Stored Procedure:

USE [MyDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spImport852Daily]
	@fName nvarchar(100)
AS
BEGIN
	SET NOCOUNT ON;

BULK INSERT dbo.tbl852Daily
FROM ''' + @fName + '''
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

END
GO

Open in new window


Above doesn't work, I get this error:

Msg 4860, Level 16, State 1, Procedure spImport852Daily, Line 10
Cannot bulk load. The file "' + @fName + '" does not exist.

If I replace the @fName with a path to the file (c:\import.csv) then it works perfectly.

What am I doing wrong?

tnx
~j
LVL 2
prositAsked:
Who is Participating?
 
LIONKINGCommented:
Since the filename is a parameter, you would have to use dynamic SQL to execute this.
A quick and dirty way would be something like this:

ALTER PROCEDURE [dbo].[spImport852Daily]
	@fName nvarchar(100)
AS
BEGIN
	SET NOCOUNT ON;

DECLARE @sql VARCHAR(4000)

SET @sql = 'BULK INSERT dbo.tbl852Daily FROM ''' + @fName + ''' WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'' )'

EXEC(@sql)
END

Open in new window

0
 
prositAuthor Commented:
Quick and dirty works, thank you so much...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here