1

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;
0

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'
  • 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? – GHauan Feb 20 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. – Scott Hodgin Feb 20 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.