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 go begin 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 begin 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 @tb_name end close tb_name_cur deallocate tb_name_cur end
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.