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.

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.

  • Aaron Be,rtrand – Nina Jan 23 '14 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 Jan 23 '14 at 0:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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