SQL Agent Job: Type Operating System (CmdExec) : Token Syntax Issue

• cody853

  Old Hand

  Points: 311

  More actions

  March 13, 2014 at 9:25 am

  #289414

  Hi All,

  I am struggling figuring out the token from a CMDEXEC job (as opposed to TSQL Job). It is not an option to execute the command by enabling the executing CMDs via TSQL, which is why I am using the agent. I have seen the Microsoft Help Site on tokens but all examples seem to be oriented to TSQL Job Type.

  I am trying to delete a particular trace file and at same time keeping the SQL Directory dynamic.

  Can you help correct this statement? Taking it a step further is adding in "deleting if file exist".

  del $(ESCAPE_SQUOTE(SQLDIR)) + "\Log\TestTrace.trc"

  Thank you for the help.

• jasona.work

  SSC Guru

  Points: 50185

  More actions

  March 14, 2014 at 5:36 am

  #1697094

  Couple questions about this:

  1. Can you execute any other CMDEXEC steps? Ignore what you're trying to do, try something simple first, like "DIR > c:\temp\dirlist.txt" and see if that works.

  2. What is the actual, full error being returned by the job?

  3. Is this just a plain-jane DOS command, or are you using Powershell? The "$(ESCAPE_SQUOTE(SQLDIR))" has me leaning towards Powershell, likely SQLPS (Powershell with the SQL module)

  Jason

• cody853

  Old Hand

  Points: 311

  More actions

  March 14, 2014 at 6:26 am

  #1697111

  Hi Jason,

  Thanks for the reply.

  1. Can you execute any other CMDEXEC steps? Ignore what you're trying to do, try something simple first, like "DIR > c:\temp\dirlist.txt" and see if that works.

  I can confirm that I can execute other commands. I actually had the delete with hard coded file path prior to doing the token and it works perfectly.

  2. What is the actual, full error being returned by the job?

  "The system cannot find the file specified. Process Exit Code 1. Step failed.

  3. Is this just a plain-jane DOS command, or are you using Powershell? The "$(ESCAPE_SQUOTE(SQLDIR))" has me leaning towards Powershell, likely SQLPS (Powershell with the SQL module)

  This is a plain-jane DOS command. Just a simple deletion of a file.

  Thanks,

  Cody

• jasona.work

  SSC Guru

  Points: 50185

  More actions

  March 14, 2014 at 7:05 am

  #1697125

  OK, I've got a thought then. It looks like, from reading the BOL on tokens, that they are for TSQL job steps. Try prefixing the command with the "(OSCMD)" token, and see what happens.

  Also, if your install directory has spaces in it "Program Files\" etc, you'll need to wrap it in double-quotes or DEL is going to stop at the first space.

• cody853

  Old Hand

  Points: 311

  More actions

  March 14, 2014 at 12:51 pm

  #1697263

  Okay Jasona, I got it to work. Thank you for your help.

  For the sake of future readers, here is the solution and how I got there.

  I did an echo to txt file so I could see what it was actually storing and to make sure the variable was working.

  echo $(ESCAPE_NONE(SQLDIR)) >> C:\"Program Files"\"Microsoft SQL Server"\MSSQL11.CODY\MSSQL\Log\sqltest.txt

  Output to Text looked like the following which is what was expected:

  C:\Program Files\Microsoft SQL Server\MSSQL11.CODY\MSSQL

  And to cover the "Program Files"\"Microsoft SQL Server" quotes.. I knew it would be hard to replace the string and get that correct in a command execution.

  However if you quote the whole string it will also work.

  The final statement looked like del "$(ESCAPE_NONE(SQLDIR))\Log\TestTrace.trc"