SQL on SQL Server- unable to run query which is stored in variable and executed using exec

Question

I am trying to execute the following SQL in SQL Server 2008-

DECLARE @sql nvarchar, @fullname nvarchar;

SET @fullname='1patents_corrected.csv';
SET @sql = 'BULK INSERT GooglePatentsIndividualDec2012.dbo.patent from ' + @fullname+ '     WITH  ( DATAFILETYPE = "char", FIELDTERMINATOR = "^", ROWTERMINATOR =  "\n" );'

EXEC(@sql)

However I am getting this error--

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'B'.

What am I doing wrong here?

UPDATE-- I changed the query viz. specified a size for each varchar variable. Now the code is like this--

 DECLARE @MyCounter int;
 DECLARE @Fileprefix nvarchar(1000), @Filesuffix nvarchar(1000), @fullname nvarchar(1000), @Counter_string nvarchar(1000), @sql nvarchar(1000);

 SET @MyCounter = 1;
 SET @Fileprefix= 'C:\Arvind_gpd\patents\';
 SET @Filesuffix='data_corrected.csv';

 WHILE (@MyCounter < 10)

 BEGIN;
 Set @Counter_string= Cast(@MyCounter AS varchar(1) );
 Set @fullname = (@Fileprefix+ @Counter_string + @Filesuffix );
 SET @sql = 'BULK INSERT GooglePatentsIndividualDec2012.dbo.patent from ' + @fullname+ 
 '  WITH  ( DATAFILETYPE = "char", FIELDTERMINATOR = "^", ROWTERMINATOR = "\n" );'
 EXEC(@sql);
 SET @MyCounter = @MyCounter + 1;
 END;  
 GO

However I am now getting a different error--

 Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'C:'. Msg 319, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Now what am I doing incorrectly :( ?

print out the @sql string and take a look at it. This + @fullname+ probably needs to be + @fullname+ ' — SQLMenace
– SQLMenace, Commented Jan 20, 2013 at 17:39
@SQLMenace sorry that was a typo when I inserted the code into my edit :( ... now I have pasted the code directly from SQL Server Management studio into the edit above... still getting the same error :( — Arvind
– Arvind, Commented Jan 20, 2013 at 18:28
do this double the ticks ('') and get rid of double ticks (") ` SET @sql = 'BULK INSERT GooglePatentsIndividualDec2012.dbo.patent from ''' + @fullname+ ''' WITH ( DATAFILETYPE = ''char'', FIELDTERMINATOR = ''^'', ROWTERMINATOR = ''\n'' );' ` — SQLMenace
– SQLMenace, Commented Jan 20, 2013 at 18:54

SQLMenace · Accepted Answer · 2013-01-20 16:29:27Z

7

change

DECLARE @sql nvarchar

to

DECLARE @sql nvarchar(2000)

by default the size is 1

The same applies to @fullname nvarchar; give it a size

answered Jan 20, 2013 at 16:29

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

marc_s Over a year ago

+1 that's a good piece of advice in general - always define an explicit length for any string-related items in SQL Server!

usr Over a year ago

This is a horrible default. It should not have a default, or default to something reasonable.

Arvind Over a year ago

@SQLMenace pls refer my update- now I am getting a different error :(

Lloyd Banks · Accepted Answer · 2013-01-20 18:42:16Z

1

You need to quote the file name when using BULK INSERT.

Change

SET @sql = 'BULK INSERT GooglePatentsIndividualDec2012.dbo.patent 
from ' + @fullname+ 
WITH  ( DATAFILETYPE = "char", FIELDTERMINATOR = "^", ROWTERMINATOR ="\n" );'

to

SET @sql = 'BULK INSERT GooglePatentsIndividualDec2012.dbo.patent 
FROM ''' + @fullname + '''
WITH  ( DATAFILETYPE = ''char'', FIELDTERMINATOR = ''^'', ROWTERMINATOR =''\n'' )'

You'll notice that I have instances of multiple single quotes bunched together. Double single quotes stand for escape in SQL Server (ie. I am quoting a quote within another quote).

Also, I see you use a lot of semicolons in your code. No need to put them in when you are writing in T-SQL

answered Jan 20, 2013 at 18:42

Lloyd Banks

36.9k58 gold badges172 silver badges264 bronze badges

2 Comments

Martin Smith Over a year ago

"Not ending Transact-SQL statements with a semicolon" is deprecated and will be discontinued in a future release.

Lloyd Banks Over a year ago

Whoa, didn't know that. Thanks for the heads up

Collectives™ on Stack Overflow

SQL on SQL Server- unable to run query which is stored in variable and executed using exec

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest