2

We are trying to run a BCP-query dynamically to put some data to files. However, when using the following code, it does not seem that sp_executesql understands that @RId inside the BCP query is a parameter. Is there any way to send that parameter value all the way in? When running this, the error message is: Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Must declare the scalar variable "@RId".

Query:

DECLARE @REPORTID Nvarchar(500) = 'SomeID_Resend2019-02-20 11:38:24.040';
DECLARE @Path varchar(500) = '\\server\folder\_FileName.csv';
DECLARE @SQL nvarchar(500);
SET @SQL
 = N'master.dbo.xp_cmdshell ''BCP "SELECT RowValues FROM ServerName.dbo.TableName WHERE ReportId = @RId" queryout @Path -t, -T -N -S SERVERNAME\INSTANCENAME''';
EXEC sp_executesql @SQL,
 N'@RId nVARCHAR(500), @Path VARCHAR(500)',
 @REPORTID,
 @Path;
asked Feb 20, 2019 at 11:26

1 Answer 1

1

I think you'll have to externalize those parameters when generating the dynamic SQL. Something like:

DECLARE @REPORTID Nvarchar(500) = 'SomeID_Resend2019-02-20 11:38:24.040';
DECLARE @Path varchar(500) = '\\server\folder\_FileName.csv';
DECLARE @SQL nvarchar(500);
SET @SQL
 = N'master.dbo.xp_cmdshell ''BCP "SELECT RowValues FROM ServerName.dbo.TableName WHERE ReportId = ''''' + @REPORTID + '" queryout ' + @Path + ' -t, -T -N -S SERVERNAME\INSTANCENAME''';
PRINT @SQL
--EXEC sp_executesql @SQL

The PRINT statement reveals the generated dynamic SQL

master.dbo.xp_cmdshell 'BCP "SELECT RowValues FROM ServerName.dbo.TableName WHERE ReportId = ''SomeID_Resend2019-02-20 11:38:24.040" queryout \\server\folder\_FileName.csv -t, -T -N -S SERVERNAME\INSTANCENAME'
answered Feb 20, 2019 at 11:57
2
  • But won't that negate the purpose of parameterization in sp_executesql? We wanted to use that to protect against injection. With your example I think it will be vulnerable to sql injection? Commented Feb 20, 2019 at 12:52
  • @GHauan - I think you'll have to validate your input parameters (outside of the sp_executesql) before building the dynamic SQL to execute the BCP command. I'm not sure how susceptible BCP would be to SQL injection (especially the Queryout), but you could certainly code up a before sp_executesql with parameters to select for a valid reportid. Commented Feb 20, 2019 at 13:16

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.