HowTo disable transactions in migration script?

[pil0t]

How to disable writing of this lines using ExecutionBlock.GetString():

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

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

In SqlCompare I check "Do not use transactions in synchronisation scripts"

in SDK there no such Options.
Tried NoSQLPlumbing, but it does't work.

This is a part of another problem.

I need SQL script for changing schema and data in single transaction.

[Brian Donahue]

Hi,

NoSQLPlumbing is in fact the option you are looking for. I'd say that the most likely culprit is that you had not applied your options in all the right places, for instance:

• In the Register method: stagingDB.Register(sourceConnectionProperties, Options.Default | Options.NoSQLPlumbing);
  
• In the CompareWith method: Differences stagingVsProduction = stagingDB.CompareWith(productionDB, Options.Default | Options.NoSQLPlumbing);
  
• And most importantly in the BuildFromDifferences method: work.BuildFromDifferences(stagingVsProduction, Options.Default | Options.NoSQLPlumbing, true);
  

If you want to run this in one big transaction using ADO .NET's SqlTransaction class, remember to strip all of the GO commands (batch separators) out as well. Here is an example of how to strip out the batch separators and run the synchronization through ADO .NET.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521