如果我在 SQL Server Management Studio 中取消执行请求，一系列选定的 SQL 语句将在什么时候停止？
我正在运行一堆数据库迁移脚本。 我发现自己面临一个相当紧迫的问题，即业务正在醒来并期望看到他们的数据，而他们的数据尚未完成迁移。 我还使应用程序脱机，它们确实需要重新启动。 实际上，“业务”是许多公司，因此我有许多脚本在一个查询窗口中运行 SP，如下所示：
EXEC [dbo].[MigrateCompanyById] 34
GO
EXEC [dbo].[MigrateCompanyById] 75
GO
EXEC [dbo].[MigrateCompanyById] 12
GO
EXEC [dbo].[MigrateCompanyById] 66
GO
每个 SP 调用大量其他子 SP 来迁移所需的所有数据。 我正在考虑取消查询，但我不确定执行将在什么时候取消。 如果下次 GO 能很好地取消的话我会很高兴。 如果它在公司迁移之一中途取消，那么我就完蛋了。
如果我无法取消，我可以更改 MigrateCompanyById SP 并注释所有子 SP 调用吗？ 这是否也会阻止下一个运行，同时完成当前正在运行的一个？
有什么想法吗？
I am running a bunch of database migration scripts. I find myself with a rather pressing problem, that business is waking up and expected to see their data, and their data has not finished migrating. I also took the applications offline and they really need to be started back up. In reality "the business" is a number of companies, and therefore I have a number of scripts running SPs in one query window like so:
EXEC [dbo].[MigrateCompanyById] 34
GO
EXEC [dbo].[MigrateCompanyById] 75
GO
EXEC [dbo].[MigrateCompanyById] 12
GO
EXEC [dbo].[MigrateCompanyById] 66
GO
Each SP calls a large number of other sub SPs to migrate all of the data required. I am considering cancelling the query, but I'm not sure at what point the execution will be cancelled. If it cancels nicely at the next GO then I'll be happy. If it cancels mid way through one of the company migrations, then I'm screwed.
If I cannot cancel, could I ALTER the MigrateCompanyById SP and comment all the sub SP calls out? Would that also prevent the next one from running, whilst completing the one that is currently running?
Any thoughts?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
实现受控取消的一种方法是添加包含取消标志的表。 当您想要取消执行时，您可以设置此标志，并且您的 SP 可以定期检查此标志，并在适当的情况下停止执行。
One way to acheive a controlled cancellation is to add a table containing a cancel flag. You can set this flag when you want to cancel exceution and your SP's can check this at regular intervals and stop executing if appropriate.
无论如何，我被迫取消了剧本。
这样做时，我注意到它会在当前执行语句之后取消，无论它位于 SP 执行链中的哪个位置。
I was forced to cancel the script anyway.
When doing so, I noted that it cancels after the current executing statement, regardless of where it is in the SP execution chain.
您是否将每个迁移存储过程中的代码与事务处理（BEGIN、COMMIT 等）括起来？ 这将使您能够相对轻松地回滚更改，具体取决于您在进程中所做的事情。
我见过的一种解决方案是，您有一个表，其中包含一条位值为 0 或 1 的记录，如果该记录为 0，则您的生产应用程序不允许用户群体访问，从而使您能够执行所需的任何操作，然后任务完成后将该标志设置为 1，以使生产能够继续。 考虑到您的环境，这可能不切实际，但可以确保没有用户会通过您的应用程序弄乱您的数据，直到您决定准备好弄乱它。
Are you bracketing the code within each migration stored proc with transaction handling (BEGIN, COMMIT, etc.)? That would enable you to roll back the changes relatively easily depending on what you're doing within the procs.
One solution I've seen, you have a table with a single record having a bit value of 0 or 1, if that record is 0, your production application disallows access by the user population, enabling you to do whatever you need to and then set that flag to 1 after your task is complete to enable production to continue. This might not be practical given your environment, but can give you assurance that no users will be messing with your data through your app until you decide that it's ready to be messed with.
您可以使用 此方法来报告脚本的执行进度。
现在的方式是每个存储过程都是它自己的事务。 因此，如果您取消脚本，您将只能部分更新到最后一次成功执行的存储过程。
但是，如果您需要全部更新或不需要更新，您可以将其全部放入单个事务中。
you can use this method to report execution progress of your script.
the way you have it now is every sproc is it's own transaction. so if you cancel the script you will get it update only partly up to the point of the last successfuly executed sproc.
you cna however put it all in a singel transaction if you need all or nothign update.