Please explain the CMD - executed by a job 1

[TheBugSlayer]

Hi guys,

There is a SQL Server job that deletes log files older than X number of days. It executes a DOS command that loops through all files that match the criteria, prints the command and executes it (DELete).

There are parts of the syntax that I don't understand such as the 2^>^&1 characters in the ForFiles command.

I know this sounds more like a DOS\Windows CMD question but since it's executed in SQL Server I thought some conversant admin could help me understand the whole thing here. The parameters too are a source of mystery (%V).

See the command below.

cmd /q /c "For /F "tokens=1 delims=" %v In ('ForFiles /P "G:\SQLData\MSSQL11.MSSQLSERVER\MSSQL\LOG" /m *_*_*_*.txt /d -30 2^>^&1') do if EXIST "G:\SQLData\MSSQL11.MSSQLSERVER\MSSQL\LOG"\%v echo del "G:\SQLData\MSSQL11.MSSQLSERVER\MSSQL\LOG"\%v& del "G:\SQLData\MSSQL11.MSSQLSERVER\MSSQL\LOG"\%v"

Thank you.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

 

[gmmastros]

I think the caret symbol (^) is used as a line continuation character in DOS commands.

http://blogs.msdn.com/b/oldnewthing/archive/2008/08/06/8835317.aspx

So... it's entirely possible that this was originally in a batch file and someone put it in your script without removing them even though they probably could be removed since your command appears to be on a single line.

This is mostly a guess.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Hi George,

Yes, the command can be executed in a batch file. But because it's one line only, it could be executed at the prompt too.

In my case, it is in the Command box of an Operating system (CmdExec)-type step of a SQL Server agent job...I am still setting it up. Then I will play with it, trying to execute it with and without those characters.

Thanks.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

 

[yelworcm]

It looks like they are just concatenating the STDERR output with the STDOUT output. Have a look at this linke:

http://www.robvanderwoude.com/redirection.php

Not terribly sure why they would want the STDERR output going to feed the next command. I suppose it could be a holdover from troubleshooting.

 

[SajidAttar]

This is what I could understand from above command...

Start cmd command window
with Echo turned off
execute the command and quit (don't leave the window open)


loop through each file
in log location path "G:\SQLData\MSSQL11.MSSQLSERVER\MSSQL\LOG"
files having pattern *_*_*_*.txt
and last modifed at current day - 30 days
Replaces STDERR with STDOUT (to have all output at single location)

If file exists
Write the file name (Echo)
Delete the file

Hope this helps.
Regards


Regards,


"Dream not what makes your sleep a pleasure, but what makes you work
hard to achieve it and forget your sleep (untill you achieve it)." -- SJD