CODEWITHSUNDEEP
sqlsql-server-2008-r2
user1589188
user1589188

Reputation: 5736

Use sp_executesql to run xp_cmdshell with variable

Trying to run xp_cmdshell with sp_executesql without success, database is SQL Server 2008R2

Here is the SQL

DECLARE @sql nvarchar(max) = N'EXEC xp_cmdshell ''BCP "SELECT data FROM TableA WHERE id = @id" queryout C:\Temp\test.dat -T -N'''
EXEC sp_executesql @sql, N'@id numeric(19, 0)', @id = 1234

The error is

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Must declare the scalar variable "@id".

Please help, thank you!

Upvotes: 1

Views: 14505

Answers (2)

Mihai Hantea
Mihai Hantea

Reputation: 1743

The problem is that the variable @id doesn't exist in the scope of the procedure xp_cmdshell. In other words, @id is not being replaced with the actual value in the string SELECT data FROM TableA WHERE id = @id. Better replace the variable called @id and then execute your query. 

SET @sql = REPLACE(@sql,'@id',@id);

Your code updated:

DECLARE @id NUMERIC(19, 0)
SET @id = 3087

DECLARE @sql nvarchar(max) = N'EXEC xp_cmdshell ''BCP "SELECT data FROM TableA WHERE id = @id" queryout "C:\Temp\test.dat" -T -N '''
SET @sql = REPLACE(@sql,'@id',@id);
EXEC sp_executesql @sql

Upvotes: 2

Solomon Rutzky
Solomon Rutzky

Reputation: 48864

The error is due to the @id variable reference in your @sql string being in an escaped-sub-string (i.e. the BCP string being executed by xp_cmdshell). So you could just escape out of that sub-string.

DECLARE @id NUMERIC(19, 0)
DECLARE @sql NVARCHAR(MAX)

SET @id = 1234
SET @SQL = N'DECLARE @BCP NVARCHAR(4000); 
SET @BCP = ''BCP "SELECT data FROM TableA WHERE id = ''
           + CONVERT(NVARCHAR(30), @id) + N''" queryout C:\Temp\test.dat -T -N'';
PRINT @BCP
EXEC xp_cmdshell @BCP;
'

EXEC sp_executesql @sql, N'@id NUMERIC(19, 0)', @id = 1234

But that is still more effort than is needed here. Just concatenate the @id value into the string and then use EXEC(). There is no need to use sp_executesql as you don't need the performance benefit of caching the query plan.

DECLARE @id NUMERIC(19, 0)
DECLARE @sql NVARCHAR(MAX)
SET @id = 1234
SET @SQL = N'EXEC xp_cmdshell ''BCP "SELECT data FROM TableA WHERE id = '  
           + CONVERT(NVARCHAR(30), @id) + N'" queryout C:\Temp\test.dat -T -N'''

PRINT @SQL
EXEC(@SQL)

Upvotes: 2

Related Questions