Execute sp_update_jobstep to change SQL Agent job step type 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?
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'