DateStamp on file output

[Llazwas]

I have a job that queries a view then exports the contents to a text file using cbp and xp_cmdshell. Everything works fine but I'm trying to add a datestamp on the file name and can't figure it out. Any assistance is greatly appreciated.

DECLARE @_FileName VARCHAR(256)
SELECT @_FileName = 'c:\payment_initiative' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30), GETDATE(), 20), '-', ''), ' ', ''), ':', '') + '.txt'
EXEC master..xp_cmdshell 'bcp "SELECT * FROM ITMAS500_app.dbo.Payment_Initiative" queryout = @_FileName -T -c '

 

[markros]

Try

DECLARE @FileName VARCHAR(256), @cmd varchar(4000)
SELECT @FileName = 'c:\payment_initiative' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30), GETDATE(), 20), '-', ''), ' ', ''), ':', '') + '.txt' 
set @cmd = 'bcp "SELECT * FROM ITMAS500_app.dbo.Payment_Initiative" queryout = "' + @FileName +'" -T -c  ' 

EXEC master..xp_cmdshell @cmd

PluralSight Learning Library

 

[Llazwas]

Thanks Markros. I tried it and it's still not working. It's actually not generating the file at all. If I perform the basic output such as below it works:

EXEC master..xp_cmdshell 'bcp "SELECT * FROM MAS500_app.dbo.vPayment_Initiative" queryout "C:\payment_initiative.txt" -T -c'

Oddly enough it doesn't error out and the logs show success when I try to add the date stamp code.

 

[markros]

Try to output the result of cmd first, e.g.
print @cmd

This way it may be easier to figure this problem out.

PluralSight Learning Library