Property cannot be updated or deleted

Andy_PS

Property cannot be updated or deleted. Property 'SqlAssemblyFileLine' does not exist for 'dbo.up_CLR_RotaAppointmentTransfer'.

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

JonathanWatts

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?

Andy_PS

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

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?

JonathanWatts

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

Andy_PS

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

JonathanWatts

Andy,

Thanks, let us know how it goes or if you have any more problems.

Thanks,

Jonathan

Andy_PS

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