0

As I was going to deploy a change to our Ola Hallengren indexing scripts, I noticed on some SQL Servers we have indexing job running as an "Operating System (CmdExec)" step instead of being called as a "Transact-SQL script (T-SQL)" step.

I'm using CMS to deploy this using sp_update_jobstep and I'm wanting everything as "Transact-SQL script (T-SQL)".

It doesn't appear that there's a way to change the step (unless I'm getting the terminology confused in this Microsoft article)

use msdb
go

exec sp_update_jobstep @job_name = N'IndexOptimize_USER_DATABASES', 
                       @step_id = 1, 
                       @command = N'EXECUTE [dbo].[IndexOptimize], @Databases = ''USER_DATABASES'', @LogToTable = ''Y'', @LockTimeOut = 300'

Is there a way to change the step type?

3
  • 1
    Just as an FYI, there's a reason why Ola had them as CmdExec. Prior to 2012, Agent terminated TSQL steps on errors hence using CmdExec instead of TSQL. Commented Mar 9, 2020 at 18:23
  • Good to know. I'm guessing we should be OK since we're a SQL 2016 shop. Not sure how one server has CmdExec and the others have TSQL. This particular instance must've not gotten his newest version.
    – jdids
    Commented Mar 9, 2020 at 18:34
  • 1
    Yes, earlier versions of his scripts did indeed use CmdExec, so that is a likely cause. Commented Mar 10, 2020 at 7:46

2 Answers 2

2

You can use the @subsystem=N'TSQL' parameter of the sp_update_jobstep to alter the Step-Type. Refer to sp_add_jobstep for a list of valid subsystem values.

Example:

EXEC msdb.dbo.sp_update_jobstep @job_name = N'test'
    ,@step_id = 1
    ,@subsystem = N'TSQL'
    ,@command = N'EXECUTE [dbo].[IndexOptimize], @Databases = ''USER_DATABASES'', @LogToTable = ''Y'', @LockTimeOut = 300'
1
  • Thanks Scott, this did the trick
    – jdids
    Commented Mar 10, 2020 at 13:16
0

More generally, if you want to script out a change for Agent jobs & you're not sure how - you can use the ⎎ Script ▾ button in the GUI to script out all the changes you've staged in the current session.

For example, if I want to change that same job IndexOptimize_USER_DATABASES to no longer log to table, I'd follow something like the below screencapped steps...

stage the change in the jobstep text box and press "ok"

select the location to export your script

...which would give me a new query window with the following text.

USE [msdb]
GO
EXEC msdb.dbo.sp_update_jobstep @job_id=N'cbbf1dc7-d128-499e-91d3-4df88ada2e5f', @step_id=1 , 
        @command=N'EXECUTE [dbo].[IndexOptimize]
@Databases = ''USER_DATABASES'',
@LogToTable = ''N'''
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.