問題描述

我們可以在一個 SQL 事務中多次使用 GO 語句嗎?我有一個很長的 T-SQL 腳本，我想在 SQL Transaction 中運行它.如果一切順利，那么我會提交，否則會回滾.

Can We use GO statement mutiple times in a SQL Transaction. I am having a long T-SQL script and I want to run it in a SQL Transaction. If all goes well then I will commit otherwise will rollback.

但是，在運行該查詢時，我遇到了類似'create function must be the only statement in the batch'的錯誤.因為我正在創建和刪除許多函數和過程.

But, While running that query I got error like 'create function must be the only statement in the batch'. As I am creating and dropping many Functions and Procedures in that.

我沒有在腳本的任何地方使用 GO.我的問題是 - 我可以在那個長腳本中多次使用 GO 語句.因為，GO 創建了一個批處理，如果批處理第一次執行成功，但下次執行失敗，那么rollback transaction 語句是否能夠真正回滾已執行的事務?

I have not used GO anywhere in the script. My question is that - Can I use multiple times GO statement in that long script. Because, GO creates a batch and if batch executes successfully first time but fails next time then will rollback transaction statement be able to actually rollback that has been executed ?

我的腳本結構如下:

PRINT 'Transaction Started'
BEGIN TRY
    BEGIN TRAN

    Drop Function 
    ....
    ....
    Create Function
    ....
    ....
    Drop Procedure
    ....
    ....
    Lots of statements
    ....
    ....

    COMMIT TRAN
    PRINT 'Transaction Succeeded'
END TRY
BEGIN CATCH
    PRINT 'Transaction Failed'
    IF(@@TRANCOUNT > 0)
        ROLLBACK TRAN
END CATCH

我創建此腳本是為了在單個腳本中將一些更改從 newDB 遷移到 oldDB.

I am creating this script to migrate some changes from newDB to oldDB in a single script.

推薦答案

您正在混合概念.GO 不是 Transact-SQL 的概念，不是語言的一部分，并且不被 SQL Server 理解.GO 是工具批處理分隔符.sqlcmd.exe 和 SSMS 默認都使用 GO 作為批處理分隔符.批處理分隔符用于標識 SQL 源文件中的各個批處理.客戶端工具一次一批地向服務器發送(當然省略分隔符).

You are mixing concepts. GO is not a Transact-SQL concept, not part of the language, and not understood by SQL Server. GO is the tools batch delimiter. sqlcmd.exe and SSMS both are using, by default, GO as the batch delimiter. The batch delimiter is used to identify the individual batches inside the SQL source file. The client tool sends to the server one batch at a time (of course, omitting the delimiter).

交易可以跨批次進行.TRY/CATCH 塊不能.CREATE/ALTER 語句必須是批處理中唯一的語句(注釋不是語句，包含在函數過程體中的語句是包含的).

Transactions can span batches. TRY/CATCH blocks cannot. CREATE/ALTER statements must be the only statement in a batch (comments are not statements, and statements contained in a function procedure body are,well, contained).

通過啟動事務并在第一個錯誤時中止執行(-b at sqlcmd.exe start，或使用:on error exit in SSMS).

Something similar to what you want to do can be achieved by starting a transaction and abortign the execution on first error (-b at sqlcmd.exe start, or use :on error exit in SSMS).

但是在長事務中執行 DDL 是行不通的.特別是如果您打算將它與 DML 混合使用.我必須調查的大多數損壞都來自這種組合(Xact、DDL + DML、回滾).我強烈建議不要這樣做.

But doing DDL inside long transactions is not going to work. Specially if you plan to mix it with DML. Most corruptions I had to investigate come from this combination (Xact, DDL + DML, rollback). I strongly recommend against it.

安全部署架構更新的唯一方法是在出現問題時進行備份、部署和恢復.

The sole way to deploy schema updates safely is to take a backup, deploy, restore from backup if something goes wrong.

請注意，Dan 推薦的(動態 SQL)之所以有效，是因為 sp_executesql 會啟動一個新的內部批處理.此批次將滿足 CREATE/ALTER 限制.

Note that what Dan recommends (dynamic SQL) works because sp_executesql starts a new, inner, batch. This batch will satisfy the CREATE/ALTER restrictions.

這篇關于我們可以在 SQL 事務中多次使用“GO"嗎?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！