Extended Properties, Transactions

crancoder

I've been working with the SQL Compare API to generate a database synchronization script.

I've included the Options.IgnoreExtendedProperties enumeration*, but "EXEC sp_addextendedproperty" commands are still being added to the script.

Also, in comparing my script to one generated directly from the SQL Compare application, I find that my script is missing all TRANSACTION-related statements except for one, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE".

Thanks for your help.

*
Options o = Options.Default | Options.IgnoreExtendedProperties;

sourceDB.RegisterForDataCompare(new ConnectionProperties(textBoxServer.Text, textBoxDatabase.Text, textBoxUsername.Text, textBoxPassword.Text),o);

targetDB.RegisterForDataCompare(new ConnectionProperties(textBoxTargetServer.Text, textBoxTargetDatabase.Text, textBoxTargetUserName.Text, textBoxTargetPassword.Text), o);

crancoder

Sorry, I copied off the wrong script in the example given of extendedProperties:

Options o = Options.Default | Options.IgnoreExtendedProperties;

sourceDB.Register(new ConnectionProperties(textBoxServer.Text, textBoxDatabase.Text, textBoxUsername.Text, textBoxPassword.Text), o);

targetDB.Register(new ConnectionProperties(textBoxTargetServer.Text, textBoxTargetDatabase.Text, textBoxTargetUserName.Text, textBoxTargetPassword.Text), o);

Michelle T

Are you also passing that Options object to CompareWith when you compare the databases?

e.g. sourceDB.CompareWith(targetDB, o);

Not sure what is up with the lack of transactions - there are some things which can't be done transactionally, like full text catalog and some user/role creation / altering, but you should have a 'BEGIN TRANSACTION' near the start, a 'COMMIT TRANSACTION' near the end, and a lot of

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

all the way through your script.

crancoder

Thanks, Michelle.

I had some old code with NoSQLPlumbing included in the Options.

You are correct, I'd been using the wrong Options object in both CompareWith and Work.BuildFromDifferences