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 :( ?
+ @fullname+
probably needs to be+ @fullname+ '
– SQLMenace Jan 20 '13 at 17:39