Search code examples
sql-servert-sqljobs

Create a CmdExec Job Step with t-sql inside

I want to execute a .exe with sql server. For security reasons I can't use the xp_cmdshell. So I decided to create job with a CmdExec Step.

The .exe file must receive 2 parameters. The problem is I never know the parameter.

Ex : I want to give 2 date, today and today + 1 day.

It is easy to do in T-sql, so it is possible to use t-sql INSIDE a CmdExec step ?

Solution

  • Frist,Create a Job with CmdExec step, command like this.

    EXEC test.exe @Parm1, @Parm2

    After that, in your code to execute .exe

     -- Update Job Step with real parameter
 UPDATE msdb.dbo.sysjobsteps 
 SET command = REPLACE(REPLACE(command,'@Parm1','NewParm1') ,'@Parm2','NewParm2')
 WHERE job_id = @YouJobIDHere
 AND step_id = @StepId 

 -- start job
  EXEC =  msdb.dbo.sp_start_job @job_name = @CustomJobName