November 4, 2015 at 8:36 am
Ok, I have some stored procedures that are using EXEC and I am updating them to use sp_executesql instead.
In the same proc, I've tested just a straight simple sql command with one param inside the where clause like this:
DECLARE @param NCHAR(1) = 6
DECLARE @sqlTest NVARCHAR(max) = 'SELECT * from IOErrors WHERE VENDORNO = @testParam'
EXEC sp_executesql @sqlTest,
N'@testParam NCHAR(1)',
@testParam = @param
This works just fine. So, I know the debugger is functioning properly. (I had to reboot from a problem with the debugger, so that's solved)
But in the actual stored procedure logic, it is doing an insert into a temptable from a local file system txt file via OPENROWSET like this:
DECLARE @sql NVARCHAR(MAX) =
N'INSERT INTO #custom_ImportWebContent' + CHAR(13) + CHAR(10) +
N'SELECT I.[ItemId], REPLACE(ORS.[ProductId],CHAR(34),''''), REPLACE(ORS.[SKUNo],CHAR(34),''''), REPLACE(ORS.[Title],CHAR(34),''''), REPLACE(ORS.[ImageFile],CHAR(34),'''') ' + CHAR(13) + CHAR(10) +
N'FROM OPENROWSET(' + CHAR(13) + CHAR(10) +
N' BULK''' + @LocalSQLFilePath + '''' + CHAR(13) + CHAR(10) +
N', FORMATFILE=''' + @LocalSQLFmtFilePath + '''' + CHAR(13) + CHAR(10)
IF ISNULL(@FirstRow,0) > 0
SET @sql = @sql + N', FIRSTROW = [highlight=#ffff11]@paramFirstRow [/highlight]' + CHAR(13) + CHAR(10)
IF ISNULL(@LastRow,0) > 0
SET @sql = @sql + N', LASTROW = [highlight=#ffff11]@paramLastRow[/highlight] , ROWS_PER_BATCH = CAST(@paramLastRow AS NVARCHAR(10)) ' + CHAR(13) + CHAR(10)
In the above code, @LocalSQLFilePath and @LocalSQLFmtFilePath are 'outside the single quotes' and are treated as regular params to concatenate into the dynamic SQL...
However, and this is the big one, I modified it so that @paramFirstRow and @paramLastRow are 'inside the single quotes' and are treated as just part of the string, to later be replaced in the call to sp_executesql like this:
DECLARE @FirstRowCHAR NVARCHAR(10) = CAST(@FirstRow as NVARCHAR(10)) -- these come in as INT so I need to cast to NVARCHAR for use in dynamic sql string.. don't I?
DECLARE @LastRowCHAR NVARCHAR(10) = CAST(@LastRow as NVARCHAR(10))
EXEC sp_executesql @sql,
N'@paramFirstRow NVARCHAR(10), @paramLastRow NVARCHAR(10)',
@paramFirstRow = @FirstRowCHAR, @paramLastRow = @LastRowCHAR
Just as in my first example at the top, this last line should work the same, correct?
FYI, I already know that one MUST use dynamic SQL when trying to use a param in OPENROWSET, I know that.. which is why this proc was written to use dynamic sql in the first place.
The error I am getting suggests that the params are not being replaced. Here's the error:
"Incorrect syntax near '@paramFirstRow'"... is there some trick for this I am missing? Or does OPENROWSET just not work with sp_executesql and param replacement and must just use dynamic sql only?...
Thanks for your time.
November 9, 2015 at 8:16 am
Just doing a favour for other SSC Members.
Ok, I have some stored procedures that are using EXEC and I am updating them to use sp_executesql instead.
In the same proc, I've tested just a straight simple sql command with one param inside the where clause like this:
DECLARE @param NCHAR(1) = 6
DECLARE @sqlTest NVARCHAR(max) = 'SELECT * from IOErrors WHERE VENDORNO = @testParam'
EXEC sp_executesql @sqlTest,
N'@testParam NCHAR(1)',
@testParam = @param
This works just fine. So, I know the debugger is functioning properly. (I had to reboot from a problem with the debugger, so that's solved)
But in the actual stored procedure logic, it is doing an insert into a temptable from a local file system txt file via OPENROWSET like this:
DECLARE @sql NVARCHAR(MAX) =
N' INSERT INTO #custom_ImportWebContent' + CHAR(13) + CHAR(10) +
N' SELECT I.[ItemId], REPLACE(ORS.[ProductId],CHAR(34),''''), REPLACE(ORS.[SKUNo],CHAR(34),''''), REPLACE(ORS.[Title],CHAR(34),''''), REPLACE(ORS.[ImageFile],CHAR(34),'''') ' + CHAR(13) + CHAR(10) +
N' FROM OPENROWSET(' + CHAR(13) + CHAR(10) +
N' BULK ''' + @LocalSQLFilePath + '''' + CHAR(13) + CHAR(10) +
N' , FORMATFILE = ''' + @LocalSQLFmtFilePath + '''' + CHAR(13) + CHAR(10)
IF ISNULL(@FirstRow,0) > 0
SET @sql = @sql + N' , FIRSTROW = [highlight=#ffff11]@paramFirstRow [/highlight]' + CHAR(13) + CHAR(10)
IF ISNULL(@LastRow,0) > 0
SET @sql = @sql + N' , LASTROW = [highlight=#ffff11]@paramLastRow[/highlight] , ROWS_PER_BATCH = CAST(@paramLastRow AS NVARCHAR(10)) ' + CHAR(13) + CHAR(10)
In the above code, @LocalSQLFilePath and @LocalSQLFmtFilePath are 'outside the single quotes' and are treated as regular params to concatenate into the dynamic SQL...
However, and this is the big one, I modified it so that @paramFirstRow and @paramLastRow are 'inside the single quotes' and are treated as just part of the string, to later be replaced in the call to sp_executesql like this:
DECLARE @FirstRowCHAR NVARCHAR(10) = CAST(@FirstRow as NVARCHAR(10)) -- these come in as INT so I need to cast to NVARCHAR for use in dynamic sql string.. don't I?
DECLARE @LastRowCHAR NVARCHAR(10) = CAST(@LastRow as NVARCHAR(10))
EXEC sp_executesql @sql,
N'@paramFirstRow NVARCHAR(10), @paramLastRow NVARCHAR(10)',
@paramFirstRow = @FirstRowCHAR, @paramLastRow = @LastRowCHAR
Just as in my first example at the top, this last line should work the same, correct?
FYI, I already know that one MUST use dynamic SQL when trying to use a param in OPENROWSET, I know that.. which is why this proc was written to use dynamic sql in the first place.
The error I am getting suggests that the params are not being replaced. Here's the error:
"Incorrect syntax near '@paramFirstRow'"... is there some trick for this I am missing? Or does OPENROWSET just not work with sp_executesql and param replacement and must just use dynamic sql only?...
Thanks for your time.
November 9, 2015 at 8:34 am
From MSDN
FIRSTROW = first_row , Specifies the number of the first row to load. The default is 1. This indicates the first row in the specified data file. The row numbers are determined by counting the row terminators. FIRSTROW is 1-based.
Does the DSQL work if you remove the Highlight tags?
November 9, 2015 at 9:13 am
OPENROWSET doesn't allow variables. You need to concatenate the values.
I'm assuming certain things in this example:
DECLARE @LocalSQLFilePath NVARCHAR( 500) = 'c:\path\file.txt',
@LocalSQLFmtFilePath NVARCHAR( 500) = 'c:\path\file.fmt',
@FirstRow int = 2,
@LastRow int ;
DECLARE @sql NVARCHAR(MAX) =
N' INSERT INTO #custom_ImportWebContent' + CHAR(13) + CHAR(10) +
N' SELECT I.[ItemId], REPLACE(ORS.[ProductId],CHAR(34),''''), REPLACE(ORS.[SKUNo],CHAR(34),''''), REPLACE(ORS.[Title],CHAR(34),''''), REPLACE(ORS.[ImageFile],CHAR(34),'''') ' + CHAR(13) + CHAR(10) +
N' FROM OPENROWSET(' + CHAR(13) + CHAR(10) +
N' BULK ''' + @LocalSQLFilePath + '''' + CHAR(13) + CHAR(10) +
N' , FORMATFILE = ''' + @LocalSQLFmtFilePath + '''' + CHAR(13) + CHAR(10);
IF @FirstRow > 0
SET @sql = @sql + REPLACE( N' , FIRSTROW = @paramFirstRow' + CHAR(13) + CHAR(10), '@paramFirstRow', @FirstRow);
IF @LastRow > 0
SET @sql = @sql + REPLACE( N' , LASTROW = @paramLastRow, ROWS_PER_BATCH = @paramLastRow ' + CHAR(13) + CHAR(10), '@paramLastRow', @LastRow);
SET @sql = @sql + ');'
print @sql;
EXEC sp_executesql @sql;
The ISNULL is not needed because a NULL value is not greater than 0.
November 9, 2015 at 9:21 am
By the way, it seems that you're using INSERT INTO SELECT...FROM OPENROWSET when a BULK INSERT with a proper format file would be more appropriate, unless the double quotes are inconsistent in the same column.
This can give you some orientation about format files: http://www.sqlservercentral.com/articles/BCP+(Bulk+Copy+Program)/105867/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply