Red Gate forums :: View topic - Extended properties problem
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager 6
SQL Packager 6 forum

Extended properties problem

Search in SQL Packager 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
AdamY



Joined: 15 Oct 2010
Posts: 37

PostPosted: Tue Jun 14, 2011 11:03 pm    Post subject: Extended properties problem Reply with quote

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:
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:
Quote:
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.
Back to top
View user's profile Send private message
peter.peart
Site Admin


Joined: 02 Sep 2008
Posts: 362
Location: Top floor, RG towers with the cool kids

PostPosted: Thu Jun 16, 2011 8:42 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
AdamY



Joined: 15 Oct 2010
Posts: 37

PostPosted: Fri Jun 17, 2011 4:21 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
peter.peart
Site Admin


Joined: 02 Sep 2008
Posts: 362
Location: Top floor, RG towers with the cool kids

PostPosted: Fri Jun 24, 2011 4:33 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
AdamY



Joined: 15 Oct 2010
Posts: 37

PostPosted: Fri Oct 21, 2011 11:25 pm    Post subject: Update? Reply with quote

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.
Back to top
View user's profile Send private message
peter.peart
Site Admin


Joined: 02 Sep 2008
Posts: 362
Location: Top floor, RG towers with the cool kids

PostPosted: Thu Nov 03, 2011 9:23 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
AdamY



Joined: 15 Oct 2010
Posts: 37

PostPosted: Thu Nov 03, 2011 9:27 pm    Post subject: Clarification Reply with quote

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.
Back to top
View user's profile Send private message
peter.peart
Site Admin


Joined: 02 Sep 2008
Posts: 362
Location: Top floor, RG towers with the cool kids

PostPosted: Thu Nov 03, 2011 9:41 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
AdamY



Joined: 15 Oct 2010
Posts: 37

PostPosted: Thu Nov 03, 2011 9:49 pm    Post subject: Backups Reply with quote

Good idea. I'll get you a backup of the databases used. The SQL version is 10.50.1765. Thanks again for your help.
Back to top
View user's profile Send private message
AdamY



Joined: 15 Oct 2010
Posts: 37

PostPosted: Wed Dec 14, 2011 10:45 pm    Post subject: Root cause / resolution Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group