CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Arvind
Arvind

Reputation: 6474

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

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 :( ?

Upvotes: 1

Views: 1230

Answers (2)

Lloyd Banks
Lloyd Banks

Reputation: 36659

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

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135111

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

Upvotes: 7

Related Questions