Extended properties problem

Packages and compresses schema and contents of SQL Server databases

Moderators: JonathanWatts, Chris Auckland, David Atkinson, david connell, Anu Deshpande, Michelle Taylor, james.billings, Marianne

Extended properties problem

Postby AdamY » Tue Jun 14, 2011 11:03 pm

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:
Code: Select all
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.
AdamY
 
Posts: 41
Joined: Fri Oct 15, 2010 8:24 pm

Postby peter.peart » Thu Jun 16, 2011 8:42 pm

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 B) 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
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
peter.peart
Site Admin
 
Posts: 362
Joined: Tue Sep 02, 2008 9:58 am
Location: Top floor, RG towers with the cool kids

Postby AdamY » Fri Jun 17, 2011 4:21 pm

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.
AdamY
 
Posts: 41
Joined: Fri Oct 15, 2010 8:24 pm

Postby peter.peart » Fri Jun 24, 2011 4:33 pm

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
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
peter.peart
Site Admin
 
Posts: 362
Joined: Tue Sep 02, 2008 9:58 am
Location: Top floor, RG towers with the cool kids

Update?

Postby AdamY » Fri Oct 21, 2011 11:25 pm

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.
AdamY
 
Posts: 41
Joined: Fri Oct 15, 2010 8:24 pm

Postby peter.peart » Thu Nov 03, 2011 9:23 pm

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
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
peter.peart
Site Admin
 
Posts: 362
Joined: Tue Sep 02, 2008 9:58 am
Location: Top floor, RG towers with the cool kids

Clarification

Postby AdamY » Thu Nov 03, 2011 9:27 pm

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.
AdamY
 
Posts: 41
Joined: Fri Oct 15, 2010 8:24 pm

Postby peter.peart » Thu Nov 03, 2011 9:41 pm

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
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
peter.peart
Site Admin
 
Posts: 362
Joined: Tue Sep 02, 2008 9:58 am
Location: Top floor, RG towers with the cool kids

Backups

Postby AdamY » Thu Nov 03, 2011 9:49 pm

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
 
Posts: 41
Joined: Fri Oct 15, 2010 8:24 pm

Root cause / resolution

Postby AdamY » Wed Dec 14, 2011 10:45 pm

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.
AdamY
 
Posts: 41
Joined: Fri Oct 15, 2010 8:24 pm


Return to SQL Packager 6

Who is online

Users browsing this forum: No registered users and 1 guest