使用 sp_EXECUTESQL 管理数据库内的 SQL 执行失败并使用“GO”；陈述
我有一个应用程序将管理 DDL 更改和 TSql 可执行语句到 SQL Server 数据库的发布。
基本工作流程：开发人员提交 .sql 文件，收集文件，审查 SQL 并在发布周期中分配一个步骤，使用存储过程在数据库中执行发布，并将在单个 SQL 事务中循环执行 SQL 的步骤，使用sp_EXECUTESQL。如果发生任何错误，事务不会提交 DDL 更改。此进程管理同一实例上从单个数据库到多个数据库的 SQL。
我遇到的问题是，当提交 SQL 脚本时，它们包含 sp_EXECUTESQL 不支持的“GO”语句，并抛出“'GO'附近的语法不正确”错误。我可以通过解析“GO”关键字来拆分和分解大多数事务，但这不适用于其他数据库中的项目。一旦我对另一个数据库进行了某种更改，我就需要“GO”。例如，以下 hase 不能一起执行，不能拆分并作为两个语句执行：
USE [MyDatabaseOtherThanOneIAmExecutingFrom]
GO
Alter PROCEDURE [dbo].[DoSomething]
...
从语法上讲，以下语句不起作用，因此请求开发人员将其 sql 更改为以数据库名称为前缀将仅涵盖非 DDL SQL：
Alter PROCEDURE [MyDatabaseOtherThanOneIAmExecutingFrom].[dbo].[DoSomething]
原始要求是留在数据库中执行这些部署操作，因此编写一个简短的一次性应用程序来使用 SqlCommand 从 .Net 执行批处理不是一种选择。
是否有其他选项可以在数据库内处理此问题，或者我是否需要扩展到外部并创建一个应用程序来管理 SQL 步骤的执行？
I have an application which will manage releases of DDL changes and TSql Executable statements to a SQL server database.
Basic workflow: Developer submits .sql file, file is collected, SQL reviewed and assigned a step in a release cycle, release is executed in the database using a stored procedure and it will cycle through the steps executing the SQL within a single SQL transaction using sp_EXECUTESQL. If any errors occur the transaction does not commit the DDL changes. This process manages SQL from a single database to multiple database on the same instance.
The problem I have is that when the SQL scripts are submitted they contain "GO" statements which sp_EXECUTESQL does not support and throws the "Incorrect syntax near 'GO'" error. I can split and break up most transactions by parsing on the 'GO' keyword but this will not work for items that are in other databases. Once I have an ALTER of some sort against another database I need the 'GO'. e.g. the following hase ot be executed together, cannot be split and executed as two statements:
USE [MyDatabaseOtherThanOneIAmExecutingFrom]
GO
Alter PROCEDURE [dbo].[DoSomething]
...
Syntactically the following statement won't work so requesting the dev's to change their sql to prefix with a db name would only cover non DDL SQL:
Alter PROCEDURE [MyDatabaseOtherThanOneIAmExecutingFrom].[dbo].[DoSomething]
Original requirement was to stay within the database to perform these deployment actions so writing a short one off application to execute the batches from .Net using SqlCommand was not an option.
Is there another option to handle this within the database or do I need to extend outside and create an application to manage the SQL steps execution?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
只要您能用自己的话
通过解析“GO”关键字来拆分和分解大多数交易，我就看不出问题所在。只需这样做，然后使用相同的连接在 GO 之间按顺序（即批量）发出语句组，而不会中断后者。这本质上就是 SSMS 以及 sqlcmd.exe 和 osql.exe 解释“GO”的方式（请原谅这个双关语）。
As long as you can, by your own words,
split and break up most transactions by parsing on the 'GO' keyword, I just can't see the problem.Just do that and issue the groups of statements between GOs sequentially, i.e. in batches, using the same connection, without interrupting the latter. This is essentially how SSMS, as well as sqlcmd.exe and osql.exe, go about interpreting 'GO' (pardon the pun).
首先，最简单的解决方案是在 GO 上强制拆分并尝试使 sql 文件符合该要求。除此之外，您可以创建一个 SSIS 包，将文件路径传递给执行 SQL 任务并以这种方式执行它。如果这对您不起作用，那么您的最后一个解决方案是使用 SQL Server 管理对象，它允许您将整个脚本一起发送到服务器。要使用 SMO，您需要使用 C# 或 VB.NET 等语言构建一个小型应用程序，用于处理文件并将脚本传递给 SMO。
Microsoft SQL 功能包Server 2005 - 2005 年 11 月
（向下滚动到 Microsoft SQL Server 2005 管理对象集合）
First, you simplest solution is force the split on GO and try to get the sql files to conform to that. Barring that, you could create an SSIS package that passes the file paths to the Execute SQL task and execute it that way. If that doesn't work for you, then your last solution would be to use the SQL Server Management Objects which let you send the entire script to the server en masse. To use the SMO, you'd need to build a small app in something like C# or VB.NET that processes the files and passes the script to the SMO.
Feature Pack for Microsoft SQL Server 2005 - November 2005
(Scroll down to Microsoft SQL Server 2005 Management Objects Collection)
您可以嵌套 sp_executesql 调用。它有点丑陋，但它有效，并且允许您对其他数据库执行 DDL：
You can nest sp_executesql calls. It's a bit ugly, but it works, and allows you to execute DDL against other databases: