1
declare @DATADIR varchar(128)
set @DATADIR= 'C:\SQL2012\SH_DATA'

msg:Command(s) completed successfully.

EXECUTE('bulk insert CHANNELS from '''+@DATADIR+'\CHANNELS.txt''
        With (firstrow = 2,
 fieldterminator=''|'',
 KEEPNULLS,
rowterminator=''\n'',
 MAXERRORS = 10000)
 ')

msg:Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@DATADIR".

I am using SQL2012.

1 Answer 1

2

You shouldn't concatenate inside EXEC like that. Instead, try this (most importantly, it lets you debug your statement before executing it):

DECLARE @datadir VARCHAR(128) = 'C:\SQL2012\SH_DATA';

DECLARE @sql NVARCHAR(MAX) = N'BULK INSERT dbo.CHANNELS FROM ''' 
  + @DATADIR + '\CHANNELS.txt'' WITH ...;';

PRINT @sql;
-- EXEC sp_executesql @sql;

Also, you need to execute all three of these statements together. You can't execute the declares, then execute the remainder independently, which it seems like you did above.

2
  • Aaron Be,rtrand
    – Nina
    Commented Jan 23, 2014 at 0:04
  • Aaron Bertand, I tried aganin and msg. came with repeat from "bulk insert...."then I saw my table" Countries" was empty?strange?
    – Nina
    Commented Jan 23, 2014 at 0:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.