Use of transactions in the generated script

It's my understanding that when the script SQL Compare produces is executed, if any error is generated, all changes up to that point are rolled back and execution of the script stops. Is this correct?

The reason I ask is because of the following script fragment shown below. It looks to me like if the Alter Table step fails, the transaction is rolled back but another one is immediately started and the script continues.

Thanks for you clarification on this.

Randy Minder

ALTER TABLE [dbo].[ProjectPolicies] ADD
[OrganizationOverrideRowID] [int] NULL
GO

@TRANCOUNT>0 ROLLBACK TRANSACTION
GO

@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

Randy Minder April 22, 2005 08:28

Comments

1 comment

Hi Randy,

That seems to be the case. Each object modification is rolled up in its' own nested transaction:
```
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating &#91;dbo&#93;.&#91;SystemSW&#93;'
GO
CREATE TABLE &#91;dbo&#93;.&#91;SystemSW&#93;
&#40;
&#91;System&#93; &#91;int&#93; NOT NULL,
&#91;SW&#93; &#91;int&#93; NOT NULL
&#41;

GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
```
If the creation of table 'SystemSW' fails, the individual transaction is rolled back, but the 'larger' transaction is still being processed. The script should keep running all of the rest of the way through, then, at the end, the #tmperrors table is checked and the whole thing is rolled back if [Error]=1.
Brian Donahue April 23, 2005 15:03

0

Add comment

Please sign in to leave a comment.

How can we help you today?

Use of transactions in the generated script

Comments

Add comment