SQL Server - Run .exe from Sql Server Agent Powershell

Asked By M McGurk on 02-Jan-13 05:52 PM
I have a Sql Server Agent job set up to call a program. The program I am calling is located on the C: drive of the same server. What commands do I use to call the program and then tell the step to wait until the program is completed? The job has other steps that depend on this program finishing before they start. I have been searching on the web for the syntax but it is really confusing….  This is the first time I have worked with PowerShell

I can open PowerShell outside of Sql Server Agent and call the exe with this command:  C:\myfolder\myprogram.exe
The program runs fine.  But it does not work in SSA Powershell or CmdExec.
sundar k replied to M McGurk on 04-Jan-13 05:52 PM

Below is from http://technet.microsoft.com/en-us/library/hh213688.aspx Refer to step 8 where you can validate whether program is success and then proceed with rest of the steps To create a CmdExec job step

  1. Expand SQL Server Agent, create a new job or right-click an existing job, and then click Properties. For more information about creating a job, seehttp://technet.microsoft.com/en-us/library/ms186273.aspx.

  2. In the Job Properties dialog, click the Steps page, and then click New.

  3. In the New Job Step dialog, type a job Step name.

  4. In the Type list, choose Operating system (CmdExec).

  5. In Run as list, select the proxy account with the credentials that the job will use. By default, CmdExec job steps run under the context of the SQL Server Agent service account.

  6. In the Process exit code of a successful command box, enter a value from 0 to 999999.

  7. In the Command box, enter powershell.exe with parameters specifying the PowerShell script to be run.

  8. Click the Advanced page to set job step options, such as: what action to take if the job step succeeds or fails, how many times SQL Server Agent should try to execute the job step, and the file where SQL Server Agent can write the job step output. Only members of the sysadmin fixed server role can write job step output to an operating system file.

http://technet.microsoft.com/en-us/library/hh213688.aspx#Intro

M McGurk replied to sundar k on 04-Jan-13 06:49 PM
Thanks.  That works.  However, I found that the powers that be have blocked exe files from running from our Sql Servers...unfortunately they did not pass the info on.