SQL 在事务中使用“GO”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/971177/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using "GO" within a transaction
提问by Greg B
I'm building a web app that attempts to install/upgrade the database on App_Start. Part of the installation procedure is to ensure that the database has the asp.net features installed. For this I am using the System.Web.Management.SqlServices object.
我正在构建一个尝试在 App_Start 上安装/升级数据库的 Web 应用程序。安装过程的一部分是确保数据库安装了 asp.net 功能。为此，我使用 System.Web.Management.SqlServices 对象。
My intention is to do all the database work within an SQL transaction and if any of it fails, roll back the transaction and leave the database untouched.
我的目的是在 SQL 事务中完成所有数据库工作，如果其中任何一个失败，则回滚事务并保持数据库不变。
the SqlServices object has a method "Install" that takes a ConnectionString but not a transaction. So instead I use SqlServices.GenerateApplicationServicesScripts like so:
SqlServices 对象有一个方法“Install”，它接受一个 ConnectionString 而不是一个事务。因此，我改为使用 SqlServices.GenerateApplicationServicesScripts，如下所示：
string script = SqlServices.GenerateApplicationServicesScripts(true, SqlFeatures.All, _connection.Database);
SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, script, ...);
and then I use the SqlHelper from the Enterprise Library.
然后我使用企业库中的 SqlHelper。
but this throws an Exception with a butt-load of errors, a few are below
但这会抛出一个带有大量错误的异常，下面是一些
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near the keyword 'USE'.
Incorrect syntax near the keyword 'CREATE'.
Incorrect syntax near 'GO'.
The variable name '@cmd' has already been declared. Variable names must be unique within a query batch or stored procedure.
I'm presuming it's some issue with using the GO statement within an SQL Transaction.
我认为在 SQL 事务中使用 GO 语句存在一些问题。
How can I get this generated script to work when executed in this way.
以这种方式执行时，如何让这个生成的脚本工作。
回答by gbn
GO is not a SQL keyword.
GO 不是 SQL 关键字。
It's a batch separator used by client tools (like SSMS) to break the entire script up into batches
它是客户端工具（如 SSMS）使用的批处理分隔符，用于将整个脚本分成批处理
You'll have to break up the script into batches yourself or use something like sqlcmdwith "-c GO" or osql to deal with "GO"
您必须自己将脚本分成批处理，或者使用类似sqlcmd和“-c GO”或 osql 来处理“GO”
回答by CodeGrue
I just want to add that if you name your transaction, you can include multiple GO sections within in and they will all roll back as a unit. Such as:
我只想补充一点，如果您为事务命名，则可以在其中包含多个 GO 部分，它们都将作为一个单元回滚。如：
BEGIN TRANSACTION TransactionWithGos;
GO
SET XACT_ABORT ON; -- Roll back everything if error occurs in script
GO
-- do stuff
GO
COMMIT TRANSACTION TransactionWithGos;
GO
回答by Wyatt Barnett
Poor man's way to fix this: split the SQL on the GO statements. Something like:
解决这个问题的穷人的方法：在 GO 语句上拆分 SQL。就像是：
private static List<string> getCommands(string testDataSql)
{
string[] splitcommands = File.ReadAllText(testDataSql).Split(new string[]{"GO\r\n"}, StringSplitOptions.RemoveEmptyEntries);
List<string> commandList = new List<string>(splitcommands);
return commandList;
}
[that was actually copied out of the app I am working on now. I garun-freaking-tee this code]
[这实际上是从我现在正在开发的应用程序中复制出来的。我吓坏了这个代码]
Then just ExecuteNonQuery()over the list. Get fancy and use transactions for bonus points.
然后就ExecuteNonQuery()在列表上。花哨并使用交易获得奖励积分。
# # # # # BONUS POINTS # # # # # #
#####奖励积分######
How to handle the transaction bits really depends on operational goals. Basically you could do it two ways:
如何处理事务位实际上取决于操作目标。基本上你可以通过两种方式做到这一点：
a) Wrap the entire execution in a single transaction, which would make sense if you really wanted everything to either execute or fail (better option IMHO)
a) 将整个执行过程包装在一个事务中，如果您真的希望所有内容要么执行要么失败，这将是有意义的（恕我直言，更好的选择）
b) Wrap each call to ExecuteNonQuery()in its own transaction. Well, actually, each call is its own transaction. But you could catch the exceptions and carry on to the next item. Of course, if this is typical generated DDL stuff, oftentimes the next part depends on a previous part so one part failing will probably bugger the whole pooch.
b) 将每个调用包装ExecuteNonQuery()在其自己的事务中。嗯，实际上，每个调用都是它自己的事务。但是您可以捕获异常并继续执行下一项。当然，如果这是典型的生成 DDL 的东西，通常下一部分取决于前一部分，所以一个部分失败可能会困扰整个狗。
回答by cmsjr
EDITas pointed out below I specified tran where batch was actually correct.
如下所述进行编辑，我指定了 tran，其中批处理实际上是正确的。
The issue is not that you cannot use GO within a transaction so much as GO indicates the end of a batch, and is not itself a T-SQL command. You could execute the whole script using SQLCMD, or split it on the GO's and execute each batch in turn.
问题不在于您不能在事务中使用 GO，因为 GO 表示批处理结束，并且本身不是 T-SQL 命令。您可以使用 SQLCMD 执行整个脚本，或者将其拆分为 GO 并依次执行每个批处理。
回答by Nihir Mandowara
GO is batch seperator in SQL. It means completion of one entire batch. For Example, a variable defined in one batch @ID can't be accessed after 'GO' statement. Please refer the code below for understanding
GO 是 SQL 中的批处理分隔符。这意味着完成一整批。例如，在'GO' 语句之后不能访问在一个批处理@ID 中定义的变量。请参考下面的代码理解
Declare @ID nvarchar(5);
set @ID = 5;
select @ID
GO
select @ID
回答by MichelZ
Actually, you can use GO statements, when you use the Microsoft.SqlServer.Management.Smo extensions:
实际上，当您使用 Microsoft.SqlServer.Management.Smo 扩展时，您可以使用 GO 语句：
var script = GetScript(databaseName);
var conn = new SqlConnection(connectionString.ConnectionString);
var svrConnection = new ServerConnection(conn);
var server = new Server(svrConnection);
server.ConnectionContext.ExecuteNonQuery(script);
http://msdn.microsoft.com/de-de/library/microsoft.sqlserver.management.smo.aspx
http://msdn.microsoft.com/de-de/library/microsoft.sqlserver.management.smo.aspx
All you need is the SQL CLR Types and SQL Management objects packages deployed from: http://www.microsoft.com/en-us/download/details.aspx?id=29065(SQL 2012 version)
您只需要从以下位置部署的 SQL CLR 类型和 SQL 管理对象包：http: //www.microsoft.com/en-us/download/details.aspx?id=29065（SQL 2012 版本）
回答by KM.
test your scripts:
测试你的脚本：
- restore a production backup on a backup server
- run run scripts with GOs
- 在备份服务器上恢复生产备份
- 使用 GO 运行运行脚本
install your scripts:
安装你的脚本：
- shut down application
- go to single user mode
- backup database
- run scripts with GOs, on failure restore backup
- go back to multi user mode
- restart application
- 关闭应用程序
- 进入单用户模式
- 备份数据库
- 使用 GO 运行脚本，失败时恢复备份
- 回到多用户模式
- 重新启动应用程序
回答by Mark Whitfeld
Just thought I'd add my solution here... replace the 'GO' with a separator that ExecuteNonQueryunderstands i.e. the ';' operator. Use this function to fix your script:
只是想我会在这里添加我的解决方案......用ExecuteNonQuery理解的分隔符替换“GO”，即“;” 操作员。使用此函数来修复您的脚本：
private static string FixGoDelimitedSqlScript(string script)
{
return script.Replace("\r\nGO\r\n", "\r\n;\r\n");
}
It may not work for complex nested T-SQL with ';' operators in them, but worked for me and my script. I would have used the splitting method listed above, but I was restricted by the library I was using so I had to find another workaround. Hope this helps someone!
它可能不适用于带有“;”的复杂嵌套 T-SQL 操作符，但对我和我的脚本有用。我本来会使用上面列出的拆分方法，但是我受到我使用的库的限制，所以我不得不找到另一种解决方法。希望这对某人有帮助！
PS. I am aware that the ';' operator is a statement separator and the 'GO' operator is a batch separator, so any advanced scripts will probably not be fixed by this.
附注。我知道';' 运算符是一个语句分隔符，而“GO”运算符是一个批处理分隔符，因此任何高级脚本可能不会被此修复。
