When using sp_add_jobstep, are there any limits on @command when I choose @subsystem to 'TSQL', say, maybe I can only set @command to some simple, single tsql like:

'select * from o2o.dbo.sth'

Or could it be complicated like this?:

use hxo2o 
  declare @last_sync_date_updater nvarchar(2000)
  declare @tb_name nvarchar(200)
  declare tb_name_cur cursor for
    select table_name from dbo.o2o_tables

  open tb_name_cur
  fetch next from tb_name_cur into @tb_name

  while @@fetch_status = 0
    set @last_sync_date_updater =
        'update dbo.o2o_tables set table_last_sync_date = (select '
        + case
        when @tb_name = 'trace_user_actions' then 'max(operation_date_time)'
        else ' dbo.InlineMax(max(create_time), max(update_time)) ' end
        + 'from dbo.' + @tb_name + ')'

    exec (@last_sync_date_updater)

    fetch next from tb_name_cur into
close tb_name_cur
deallocate tb_name_cur

If not, should I use another @subsystem instead? Or some other methods? I tried to google and stack for the issue but labor in vain.


It can be as complicated as you want, no issues with that. My own recommendation, for better manageability, if the tsql you are planning to use on the job step is too long, better create a stored procedure. This way you just call the sp from the job step and you do all your modifications there. And on the job itself just take care of the scheduling.

  • 1
    thanks and your recommendation seems to be one loose coupling resolution for my issue, I'll try it.
    – yamasa
    Oct 10 '16 at 0:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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