sqlt-sqlvariableswildcardxp-cmdshell

Using LIKE wildcard with % operator in SQL xp_cmdshell

I am trying to use the % operator in the xp_cmdshell like below.

DECLARE @OutputFilePath nvarchar(max);
SET @OutputFilePath = '\\shared data\Everyone Share\';
DECLARE @ExportSQL nvarchar(max);
SET @ExportSQL = N'
EXEC master.dbo.xp_cmdshell
  ''bcp "SELECT FileID, NAME FROM [Process].[dbo].[Vendor] WHERE FileName LIKE ''%'' + AAMG + ''%''" queryout "' + @OutputFilePath + '\OutputData.csv" -T -c -t -S WIN-db02''
' 

EXEC(@ExportSQL)

But it is throwing me an error saying Incorrect syntax near '%'

If i just remove the LIKE part, the entire code works perfectly fine.

Solution

  • (reproduce here from comments. it is easier to read here)

    You need to double up the single quote around the %

    SET @ExportSQL = N'
EXEC master.dbo.xp_cmdshell
  ''bcp "SELECT FileID, NAME FROM [Process].[dbo].[Vendor] WHERE FileName LIKE ''''%'''' + AAMG + ''''%''''" queryout "' + @OutputFilePath + '\OutputData.csv" -T -c -t -S WIN-db02''
'

    Looks like AAMG is a constant string and not a column name. In this case, change the dynamic query to

    LIKE ''''%AAMG%''''"