Comments
7 comments
-
Andy,
You may have encountered a known issue with SQL Compare. Can you check at SQL Compare is not trying to update an extended property on an object that it has rebuild? In this case the extended property will not exist on the object. It might be easier to dump the script out to file and execute it manually with a SQL editor to check this.
Regards,
Jonathan.
P.S.
Out of curiosity what task are you trying to solve with the SQL Toolkit? -
We're using it to make any needed schema changes when we update.
It seems like a good way of updating any version to any version.
We take a snapshot of the latest database schema and then run the compare against that and the live database. It saves having to script all the changes by hand. -
Yep its trying to alter the extended properties.
Altering extended properties Msg 15217, Level 16, State 2, Procedure sp_updateextendedproperty, Line 36 Property cannot be updated or deleted. Property 'SqlAssemblyFileLine' does not exist for 'dbo.up_CLR_RotaAppointmentTransfer'. (1 row(s) affected) Creating extended properties Msg 15233, Level 16, State 1, Procedure sp_addextendedproperty, Line 37 Property cannot be added. Property 'AutoDeployed' already exists for 'PremierSoftware.SqlServer.BusinessData'. (1 row(s) affected) Msg 15233, Level 16, State 1, Procedure sp_addextendedproperty, Line 37 Property cannot be added. Property 'SqlAssemblyProjectRoot' already exists for 'PremierSoftware.SqlServer.BusinessData'. (1 row(s) affected) The database update failed
Is there any current work arounds? -
Hi Andy,
If you aren't worried about extended properties there is an option to disable comparing and synchronising extended properties. If you do want extended properties to be synchronised then you will have to do a comparison and synchronisation in two passes, once with extended properties disabled to sync the objects and then again with extended properties enabled which should just then correctly sync the extended properties. Sorry, I know it is not great, but we are looking into a fix for this at the moment which will hopefully make it into the next release of SQL Compare.
Regards
Jonathan -
Unfortunatly we use extended properties to provide meta data to the DAL.
I'll try the 2 pass approach, since it'll only be done during an update the extended time needed shouldn't be too much of a concern -
Andy,
Thanks, let us know how it goes or if you have any more problems.
Thanks,
Jonathan -
That seemed to work fine and seems to be the last of the problems
(for now
)
Here's my final code#region SynchroniseSchema public static void SynchroniseSchema(string server, string database, string snapshotPath) { ConnectionProperties connectionProperties = new ConnectionProperties(server, database); //AJ: Fudge for problem with Extended Properties SynchroniseSchemaExcludingExtendedProperties(connectionProperties, snapshotPath); SynchroniseSchema(connectionProperties, snapshotPath); } public static void SynchroniseSchema(string server, string database, string username, string password, string snapshotPath) { ConnectionProperties connectionProperties = new ConnectionProperties(server, database, username, password); //AJ: Fudge for problem with Extended Properties SynchroniseSchemaExcludingExtendedProperties(connectionProperties, snapshotPath); SynchroniseSchema(connectionProperties, snapshotPath); } private static void SynchroniseSchema(ConnectionProperties liveConnectionProperties, string snapshotPath) { Database liveDatabase = new Database(); liveDatabase.Register(liveConnectionProperties, Options.Default); Database snapshotDatabase = new Database(); snapshotDatabase.LoadFromDisk(snapshotPath); Differences differences = liveDatabase.CompareWith(snapshotDatabase, Options.Default); foreach (Difference difference in differences) { difference.Selected = true; } Work work = new Work(); work.BuildFromDifferences(differences, Options.Default, false); ExecutionBlock executionBlock = work.ExecutionBlock; BlockExecutor blockExecutor = new BlockExecutor(); blockExecutor.ExecuteBlock(executionBlock, liveDatabase.ConnectionProperties.ServerName, liveDatabase.ConnectionProperties.DatabaseName, liveDatabase.ConnectionProperties.IntegratedSecurity, liveDatabase.ConnectionProperties.UserName, liveDatabase.ConnectionProperties.Password); } //AJ: Fudge for problem with Extended Properties //AJ: To solve the problem use a 2 pass approach, //AJ: first without Extended Properties and then with. //AJ: http://www.red-gate.com/MessageBoard/viewtopic.php?t=4433 private static void SynchroniseSchemaExcludingExtendedProperties(ConnectionProperties liveConnectionProperties, string snapshotPath) { Database liveDatabase = new Database(); liveDatabase.Register(liveConnectionProperties, Options.Default | Options.IgnoreExtendedProperties); Database snapshotDatabase = new Database(); snapshotDatabase.LoadFromDisk(snapshotPath); Differences differences = liveDatabase.CompareWith(snapshotDatabase, Options.Default | Options.IgnoreExtendedProperties); foreach (Difference difference in differences) { difference.Selected = true; } Work work = new Work(); work.BuildFromDifferences(differences, Options.Default | Options.IgnoreExtendedProperties, false); ExecutionBlock executionBlock = work.ExecutionBlock; BlockExecutor blockExecutor = new BlockExecutor(); blockExecutor.ExecuteBlock(executionBlock, liveDatabase.ConnectionProperties.ServerName, liveDatabase.ConnectionProperties.DatabaseName, liveDatabase.ConnectionProperties.IntegratedSecurity, liveDatabase.ConnectionProperties.UserName, liveDatabase.ConnectionProperties.Password); } #endregion
I could have put the fudge into the 'SynchroniseSchema' method to bring everything into a more oop style, but in this case I decided to keep the fudge separate so it's easy to see what needs changing when the issue is fixed.
Thanks for all your helpYou've been very speedy and hit the mark every time with the problems I've had
Add comment
Please sign in to leave a comment.
Had a search on the forums and the web in general and I can't see what would cause this :S
If you need aany more info then just ask