Solved

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

Posted on 2015-02-02
2
117 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
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

756 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