Extended Properties, Transactions

crancodercrancoder Posts: 28
edited January 14, 2008 11:58AM in SQL Compare Previous Versions
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);

Comments

  • 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);
  • 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.
    Software Developer
    Redgate Software
  • 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
Sign In or Register to comment.