Bulk insert fails as dynamic sql

Question

I have this code in an SP that sets up a bulk insert:

begin try

    declare @sentFile nvarchar(255)
    declare @bulk_cmd nvarchar(1000) = ''
    declare @loadDate nvarchar(8) = Convert(nvarchar(8),@p_loadDate) -- @p_loadDate is char(8)

    set @StrImportFolder = N'D:\EMVImports\'
    set @sentFile = @StrImportFolder + N'etl_rnli_sent_'+ @loadDate + N'.txt'

    SET @bulk_cmd = N'BULK INSERT loadSent
                 FROM ''' + @sentFile + N'''
                 WITH (
                     FIRSTROW = 2
                     ,formatfile=''D:\EMVScripts\Sent_Format.xml'' 
                 )'
    Print @bulk_cmd
    EXECUTE sp_executesql @bulk_cmd

    --  more stuff happens here
end try

Inside my stored procedure, this fails with this error:

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

But the code printed out:

BULK INSERT loadSent    
FROM 'D:\EMVImports\etl_sent_20130529.txt'    
WITH (      
    FIRSTROW = 2      
    ,formatfile='D:\EMVScripts\Sent_Format.xml'      
)

works like a charm. I've no idea why it fails under sp_executesql.

Please check [here](http://stackoverflow.com/questions/4050790/bulk-insert-using-stored-procedure) — Romesh, Aug 09 '13 at 13:04
Hi Roms, I'd read that page earlier - am I missing something on there? — RichJohnstone, Aug 09 '13 at 13:09
You're saying `@bulk_cmd` is a `NVARCHAR` - try using `N` prefixes for all string literals that you're concatenating together into `@bulk_cmd`. Also: have you printed out `@bulk_cmd` before executing? Does it look ok? Stupid question: are you passing the right value into `@sent_file` ? — marc_s, Aug 09 '13 at 13:15
I've tried this and it worked `Declare @test nvarchar(50) SET @test = 'select top 10 * from table' execute sp_executesql @test`. Maybe `@sentFile` is `not nvarchar` and he should convert it to `nvarchar`? — Radu Gheorghiu, Aug 09 '13 at 13:19
@marc_s, i've edited the code with the print line and the code it printed looks fine and works fine! It's a mystery. — RichJohnstone, Aug 09 '13 at 13:27
Are you sure the definition for the `NVARCHAR` variable is long enough? Could you show where you declare the variable please? — Aaron Bertrand, Aug 09 '13 at 13:27
Please provide the complete error message. It should be longer. — MicSim, Aug 09 '13 at 13:47
the code in my SP is enclosed in `begin try` & `end try` - the error reported is captured and logged - that's all that I captured. — RichJohnstone, Aug 09 '13 at 13:55
Maybe you need to play around with the `ROWTERMINATOR`. What happens if you start importing from the first row and not the second one? — MicSim, Aug 09 '13 at 14:51
The xml format file contains the row terminator. Either way I tried it both with and without `ROWTERMINATOR` specified and it still failed. I tried changing it to row 1 also, still go good. — RichJohnstone, Aug 09 '13 at 15:55

score 0 · Answer 1 · answered Aug 09 '13 at 13:54

0

I am using much similar query. And it is working.

DECLARE @filepath nvarchar(500)
SET @filepath = N'e:\5-digit Commercial.csv'

DECLARE @bulkinsert NVARCHAR(2000)

SET @bulkinsert = 
       N'BULK INSERT ZIPCodes FROM ''' + 
       @filepath + 
       N''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')'

EXEC sp_executesql @bulkinsert

How do you set the value of @sentFile?

answered Aug 09 '13 at 13:54

AgentSQL

2,810
20
22

I've edited the code above to show how they're set. `@p_loadDate` is a parameter of the SP. – RichJohnstone Aug 09 '13 at 14:06

Jhabar · Answer 2 · 2013-12-17T12:36:08.817

We can not set the 'From' file path dynamically for BULK INSERT.

You are generating the path dynamically

set @sentFile = @StrImportFolder + N'etl_rnli_sent_'+ @loadDate + N'.txt'

Here the @loadDate is a variable component of the file name.

The working example given above uses fixed path event with a variable:

SET @filepath = N'e:\5-digit Commercial.csv',

Here the variable has a fix path for every case.

So, try to use a pre-define file path.

Bulk insert fails as dynamic sql

2 Answers2