HowTo disable transactions in migration script?

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

HowTo disable transactions in migration script?

Postby pil0t » Mon Apr 27, 2009 5:49 pm

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.
pil0t
 
Posts: 8
Joined: Mon Apr 27, 2009 5:43 pm

Postby Brian Donahue » Mon Apr 27, 2009 6:53 pm

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
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 1 guest