I looked around and found some ideas about how to do this, but no definitive best way. One of the ideas was to use sp_start_job to kick off an SQL Server Agent job that runs the DTS package. If this is the best way to do it, then the next question would be, "How do I schedule a DTS package from a job and make it non-recurring?"
我环顾四周,发现了一些关于如何做到这一点的想法,但没有明确的最佳方式。其中一个想法是使用sp_start_job启动运行DTS包的SQL Server代理作业。如果这是最好的方法,那么接下来的问题是,“如何从作业中安排DTS包并使其不再发生?”
Thanks, Tim
3 个解决方案
#2
1
I wouldn't suggest tying this kind of functionality to a trigger. Triggers are supposed to be fast. I don't think there is any way to launch a DTS package that will be as fast as I would want a trigger to be. If this resonates with you, then I would suggest having your trigger simply insert a row into a special table, and then have a job that executes as often as you need for your purpose (every minute? every 10 seconds?) that monitors this table and kicks off the appropriate DTS package as needed.
我不建议将这种功能绑定到触发器。触发器应该很快。我不认为有任何方法可以启动DTS包,它将像我想要的触发器一样快。如果这引起了你的共鸣,那么我建议让你的触发器只是在一个特殊的表中插入一行,然后根据你的需要经常执行一个工作(每分钟?每10秒?)监视这个表并根据需要启动适当的DTS包。
#3
0
Instead of using xp_cmdshell, I did this:
我没有使用xp_cmdshell,而是这样做:
When a certain value in a table changes, the trigger uses msdb.sp_start_job to start a job. This job should not run on a schedule, only when initiated by a user. I set the job schedule to run one time, which is now in the past, and I unchecked the enabled box.
当表中的某个值更改时,触发器使用msdb.sp_start_job来启动作业。只有在用户启动时,才能按计划运行此作业。我将作业计划设置为运行一次,现在已经过去了,我取消选中启用的框。
This job has one step, which is DTSRun /~Z0xHEXENCRYPTEDVALUE. The DTS package copies some rows from this server to another server on a different platform and on success resets values in the table with the trigger for next time. The trigger checks a table value before calling sp_start_job, so that the job starts only under certain conditions, not every time.
这项工作有一步,即DTSRun / ~Z0xHEXENCRYPTEDVALUE。 DTS包将此服务器中的某些行复制到另一个平台上的另一个服务器,并且成功时会使用下次触发器重置表中的值。触发器在调用sp_start_job之前检查表值,以便作业仅在某些条件下启动,而不是每次都启动。
Since sp_start_job runs asyhchronously the trigger completes quickly. The only drawback to this is that I need to poll the value that was reset on success and either let the user know it worked, or after some time out period, it did not work.
由于sp_start_job以异步方式运行,因此触发器会快速完成。唯一的缺点是我需要轮询成功时重置的值,并让用户知道它有效,或者在一段时间后,它不起作用。
The alternative would be to use xp_cmdshell if I needed synchronous operation, which might not be a good idea from inside of a trigger.
如果我需要同步操作,那么替代方法是使用xp_cmdshell,这可能不是一个好主意,从触发器内部。
#1
#2
1
I wouldn't suggest tying this kind of functionality to a trigger. Triggers are supposed to be fast. I don't think there is any way to launch a DTS package that will be as fast as I would want a trigger to be. If this resonates with you, then I would suggest having your trigger simply insert a row into a special table, and then have a job that executes as often as you need for your purpose (every minute? every 10 seconds?) that monitors this table and kicks off the appropriate DTS package as needed.
我不建议将这种功能绑定到触发器。触发器应该很快。我不认为有任何方法可以启动DTS包,它将像我想要的触发器一样快。如果这引起了你的共鸣,那么我建议让你的触发器只是在一个特殊的表中插入一行,然后根据你的需要经常执行一个工作(每分钟?每10秒?)监视这个表并根据需要启动适当的DTS包。
#3
0
Instead of using xp_cmdshell, I did this:
我没有使用xp_cmdshell,而是这样做:
When a certain value in a table changes, the trigger uses msdb.sp_start_job to start a job. This job should not run on a schedule, only when initiated by a user. I set the job schedule to run one time, which is now in the past, and I unchecked the enabled box.
当表中的某个值更改时,触发器使用msdb.sp_start_job来启动作业。只有在用户启动时,才能按计划运行此作业。我将作业计划设置为运行一次,现在已经过去了,我取消选中启用的框。
This job has one step, which is DTSRun /~Z0xHEXENCRYPTEDVALUE. The DTS package copies some rows from this server to another server on a different platform and on success resets values in the table with the trigger for next time. The trigger checks a table value before calling sp_start_job, so that the job starts only under certain conditions, not every time.
这项工作有一步,即DTSRun / ~Z0xHEXENCRYPTEDVALUE。 DTS包将此服务器中的某些行复制到另一个平台上的另一个服务器,并且成功时会使用下次触发器重置表中的值。触发器在调用sp_start_job之前检查表值,以便作业仅在某些条件下启动,而不是每次都启动。
Since sp_start_job runs asyhchronously the trigger completes quickly. The only drawback to this is that I need to poll the value that was reset on success and either let the user know it worked, or after some time out period, it did not work.
由于sp_start_job以异步方式运行,因此触发器会快速完成。唯一的缺点是我需要轮询成功时重置的值,并让用户知道它有效,或者在一段时间后,它不起作用。
The alternative would be to use xp_cmdshell if I needed synchronous operation, which might not be a good idea from inside of a trigger.
如果我需要同步操作,那么替代方法是使用xp_cmdshell,这可能不是一个好主意,从触发器内部。