Extended properties problem

AdamY

I have SQL Packager 6.4. I use it to compare 2 databases and build a script (so I can add a few lines of extra code). I noticed that the script has this line:

PRINT N'Dropping extended properties'
GO
EXEC sp_dropextendedproperty N'MS_Description', NULL, NULL, NULL, NULL, NULL, NULL
GO

I thought that was odd, but ignored it.

Then I use Packager to build an exe based on the script. But when I run the exe to upgrade my database, I get this error:

Property cannot be updated or deleted. Property 'MS_Description' does not exist for 'object specified.

I can just remove that line of code from the script and it will work, but I wanted to see if this is a bug or if I'm doing something wrong. BTW, I do not want to "ignore extended properties" because I want the script to account for those changes - if there are any.

peter.peart

Hi there,

Thanks for your post. I haven't been able to re-create this I'm afraid. I can of course re-create the drop, however it seems that there's an issue in that either A) the extended property never existed in the first place on the target or The script being generated has already performed a drop on that DB.

Can you confirm, are you creating a package based on the difference between DB1 and DB2 and then running that on DB3, or are you diffing DB1 and DB2 and then running the .exe on 2?

Also, I don't suppose it's possible to get copy schemas of all DB's is it?

Pete

AdamY

The package is based on a compare of DB2 (new version of DB) and DB1 (old version of DB), then it is executed against DB1.

The only change between the 2 in this case is a few lines of code in a stored proceedure. The sproc does have extended properties on it, but they didn't change at all. So the script generated is a very simple "alter proc" -- with the additon of the odd call to drop all extended properties.

This hasn't been an issue before, but this is my first time alerting this DB (the previous times have all been creation of the DB). The DB is in SQL Server 2008 R2. I can't provide a full copy of the DB schema at this time.

peter.peart

Hi Adam,

I'm using 2008R2 too and I'm sorry to say that I still haven't been able to reproduce this I'm afraid.

If we could get a copy of the schema that would be a big help, and I could then raise a bug around it.

Pete

AdamY

Peter - I emailed a copy of the schemas for the 2 databases you requested on 6/28/2011. Any progress on recreating the issue? It continues to happen for me - even after I got a new computer and reinstalled the Red gate tools. Thanks for the help.

peter.peart

Hi Adam,

Apologies, I was not in the office last week. We still have not been able to repro the error based on the scripts you provided. I ran the scripts on a 2008R2 instance and then created a package to update Compass from MyDB. Everything worked as I would have expected it to with no errors, when running the actual package itself.

Unless we can repro it internally, it's going to be nigh on impossible to figure out why it's doing that and make a code change so it works as you would expect it to.

Pete

AdamY

If it helps, the changes are being made to the [CompassionWeb] database, not the [Compass] database. The [CompassionWeb] db is the one with the extended properties on most of the objects (for SQL Doc). So I compare [CompasionWeb] (orig) to [CompassionWeb] (new) and that is what generates that statement that I have to delete.

Also, I know this isn't a real urgent problem, but I'd be happy to do a quick Skype with someone to share my desktop and at least show them what I'm doing.

peter.peart

Hi Adam,

Even going from Compass to MyDB I'm still not getting any errors.

I think about the only hope we're going to have is if we have backups of the source and target DB, and you can confirm exactly what versions of SQL Server you're running on.

A skype or remote session won't help, assuming you're just doing a straight upgrade from one to the other through SQL Packager.

Pete

AdamY

Good idea. I'll get you a backup of the databases used. The SQL version is 10.50.1765. Thanks again for your help.

AdamY

I found the cause of the "problem". All my fault.

I haven't determined exactly what I was doing wrong, but it had something to do with comparing DB1 to DB3, but thinking I was comparing DB1 to DB2.

Sorry for wasting your time. I tested this 4-5 times and must have made the same mistake each time. My apologies.