Property cannot be updated or deleted

• 
  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?

  JonathanWatts March 05, 2007 11:25

  - 0 +
• 
  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.

  Andy_PS March 06, 2007 04:07

  - 0 +
• 
  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?

  Andy_PS March 06, 2007 05:33

  - 0 +
• 
  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

  JonathanWatts March 06, 2007 06:06

  - 0 +
• 
  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_PS March 06, 2007 09:25

  - 0 +
• 
  Andy,
  
  Thanks, let us know how it goes or if you have any more problems.
  
  Thanks,
  
  Jonathan

  JonathanWatts March 06, 2007 09:38

  - 0 +
• 
  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 help You've been very speedy and hit the mark every time with the problems I've had

  Andy_PS March 06, 2007 11:38

  - 0 +

Add comment

Please sign in to leave a comment.