0

I have the query working correctly, but I want to be able to add a date value variable within the command.

Here is what I have so far:

DECLARE @varDate DATE

SET @varDate = CAST(DATEADD(day, -1, GETDATE()) AS DATE)

EXEC master..xp_cmdshell 'SQLCMD -E -S "EMERALDSERVER\LASERFICHERIO" -s"," -W -Q "SET NOCOUNT ON EXEC PullSageData.[dbo].[Report_Daily_Superintendent_Jobs]" | findstr /V /C:"-" /B > C:\Laserfiche\import\Reports\Reports_"+@varDate+".csv'

Where the @varDate is located should just replace with yesterdays date.

Example file name:

Reports_2018_10_31.csv

instead it outputs the filename:

Reports+@varDate+.csv

My guess is that entering a variable within single quotes isn't applied with double quotes and + like I have been seeing in so many examples.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
C. Ayers
  • 53
  • 1
  • 9

1 Answers1

1

You must create a new variable that contains everything and execute from it Example:

declare @varDate date
SET @varDate=CAST(DATEADD(day,-1,GETDATE()) as date)
declare @Script varchar (max) = 'SQLCMD -E -S "EMERALDSERVER\LASERFICHERIO" -s"," -W -Q "SET NOCOUNT ON EXEC PullSageData.[dbo].[Report_Daily_Superintendent_Jobs]" | findstr /V /C:"-" /B > C:\Laserfiche\import\Reports\Reports_varDate.csv'
declare @cmd varchar(max)= replace(@Script,'@varDate',@varDate)
EXEC master..xp_cmdshell @cmd
Arturo Velazquez Gutierrez
  • 26
  • 1
  • The only thing I modified was xp_cmdshell cannot use varchar(max), I just replaced with a large string size varchar(1000). Thanks! – C. Ayers Nov 02 '18 at 00:19
  • This will work, but you'll receive an error stating `Could not find stored procedure master..xp_cmdshell @cmd` To fix it, change the above execution to: `EXEC (master..xp_cmdshell @cmd)` – Attie Wagner Nov 02 '18 at 07:17