HowTo disable transactions in migration script?
pil0t
Posts: 14
How to disable writing of this lines using ExecutionBlock.GetString():
@TRANCOUNT>0 ROLLBACK TRANSACTION
@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.
@TRANCOUNT>0 ROLLBACK TRANSACTION
@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.
Comments
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.