Add a date to output filename using SQLCMD from within SQL Agent CmdExec?

Question

I want to run a weekly extract from a SQL Server database using SQLCMD under SQL Agent. Because I need to save multiple extracts in the same share, I want to use the current date as part of the extract's file name. When doing this from the command line, I use:

sqlcmd -S POC -i "\\org-data\data\dept\share\registry\SQLCMD\extractdata.sql" -s "|" -W -h-1 -o "\\org-data\data\dept\share\registry\Extracts\extractdata.%date:~-4,4%%date:~-10,2%%date:~-7,2%.txt"

and it works perfectly.

When I place the same statement into a CmdExec under SQL Agent, my date becomes a syntax error -- ("The filename, directory name, or volume label syntax is incorrect")

How do others handle this? Thanks.

Benjamin · Accepted Answer · 2015-04-02 17:38:48Z

5

Try using the SQL Server Agent tokens. They are described in MSDN article, "Use Tokens in Job Steps". The DATE token provides the current date in YYYYMMDD format. For your example use:

"...\Extracts\extractdata.$(ESCAPE_DQUOTE(DATE)).txt"

answered Apr 2, 2015 at 17:38

Benjamin

612 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

scottpetersen Over a year ago

I wanted to mention that this post was helpful for me today - I needed to make the output file name of a job step have a date stamp and the SQL Server Agent tokens example was a fast solution with zero coding. So, I just put the token in the "Output File Name" box on the advanced tab like this: C:\Users\Public\Documents\Test_$(ESCAPE_DQUOTE(DATE)).txt

Ganesh Hegde Over a year ago

This worked well for me. Not sure why this isn't accepted as answer

smolchanovsky · Accepted Answer · 2019-04-16 19:47:11Z

0

This isn't working for me

echo off
sqlcmd -m 1 -S 10.108.96.210\QA832 -U Exception -P Password1 -i E:\KCM_UAT\Exception.sql -o C:\Test_$(ESCAPE_DQUOTE(DATE)).txt  -W -h-1 -s "    "
set /p delExit=Press the ENTER key to exit...:

The file is written out like this

Test_$(ESCAPE_DQUOTE(DATE)).txt

edited Apr 16, 2019 at 19:47

smolchanovsky

1,8712 gold badges18 silver badges30 bronze badges

answered Apr 16, 2019 at 17:07

tdipl

11 bronze badge

Collectives™ on Stack Overflow

Add a date to output filename using SQLCMD from within SQL Agent CmdExec?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related