Solved

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

Posted on 2015-02-02

1 solution

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

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

Join The Community

2 Comments

LVL 13

Accepted Solution

by:LIONKING

LIONKING earned 500 total points

ID: 405845342015-02-02

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

LVL 2

Author Closing Comment

by:prosit

ID: 405846022015-02-02

Quick and dirty works, thank you so much...

Featured Post

Question has a verified solution.

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

How to replicate SQL 2008 database to SQL 2014 by using Initialize from Backup.

Article by: lcohan

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…

How to implement Parallel Executions in SSIS?

Article by: Dung

I have a large data set and a SSIS package. How can I load this file in multi threading?

Executing a SQL Script from a Unix Shell Script and Passing Parameters

Video by: Steve

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.