使用“GO"在交易中 [英] Using "GO" within a transaction

问题描述

我正在构建一个网络应用程序，该应用程序尝试在 App_Start 上安装/升级数据库.安装过程的一部分是确保数据库安装了 asp.net 功能.为此，我使用 System.Web.Management.SqlServices 对象.

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.

我的目的是在一个 SQL 事务中完成所有数据库工作，如果其中任何一个失败，回滚事务并保持数据库不变.

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.

SqlServices 对象有一个方法Install"，它接受一个 ConnectionString 而不是一个事务.所以我改为使用 SqlServices.GenerateApplicationServicesScripts 像这样:

the SqlServices object has a method "Install" that takes a ConnectionString but not a transaction. So instead I use SqlServices.GenerateApplicationServicesScripts like so:

string script = SqlServices.GenerateApplicationServicesScripts(true, SqlFeatures.All, _connection.Database);
SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, script, ...);

然后我使用企业库中的 SqlHelper.

and then I use the SqlHelper from the Enterprise Library.

但这会抛出一个带有大量错误的异常，下面有一些

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.

我认为在 SQL 事务中使用 GO 语句存在一些问题.

I'm presuming it's some issue with using the GO statement within an SQL Transaction.

以这种方式执行时，如何使生成的脚本工作.

How can I get this generated script to work when executed in this way.

推荐答案

GO 不是 SQL 关键字.

GO is not a SQL keyword.

它是客户端工具(如 SSMS)用来将整个脚本分成多个批次的批处理分隔符

It's a batch separator used by client tools (like SSMS) to break the entire script up into batches

您必须自己将脚本分成批处理或使用类似sqlcmd用-c GO"或osql处理GO"

You'll have to break up the script into batches yourself or use something like sqlcmd with "-c GO" or osql to deal with "GO"

这篇关于使用“GO"在交易中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！