Solved

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

Posted on 2015-02-02
2
119 Views
Last Modified: 2015-02-02
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
0
Comment
Question by:prosit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 13

Accepted Solution

by:
LIONKING earned 500 total points
ID: 40584534
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
 
LVL 2

Author Closing Comment

by:prosit
ID: 40584602
Quick and dirty works, thank you so much...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question