Red Gate forums :: View topic - How does SQL Compare work out dependency order?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

How does SQL Compare work out dependency order?

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
ChrisGodfree



Joined: 10 May 2012
Posts: 7

PostPosted: Thu Sep 20, 2012 12:00 pm    Post subject: How does SQL Compare work out dependency order? Reply with quote

Facing a strange problem when deploying database changes that we cannot figure out. We successfully deployed a number of database changes to production earlier this week. Among the change were two views - call them A and B with B being referenced in A. When we try to deploy the changes to our demonstration database from the production database (same SQL Compare Command Line options) it fails with a message saying invalid reference to View B. Looking at the SQL scripts which have been generated, the demonstration release script is simply in alphabetical order but the production release script takes into account the dependencies between the views. Does anyone know what could have caused this? The only difference we can identify in the two processes is that the account we use for releasing to the production database has the db_owner role on both databases but the account we use for releasing to the demonstration database has db_reader on the production database and db_owner on the demonstration database.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Mon Sep 24, 2012 2:19 pm    Post subject: Reply with quote

Thanks for your post.

The internal way that SQL Compare works out dependencies is not something I'm aware of in any detail (it's pretty complex!)

In general it gets things in the correct order; but occasionally certain databases will cause trouble, especially in situations with circular dependency references and so on.

I'd be interested in a couple of tests though- firstly; can you test it using full permissions on both databases as described here? It may be that the reduced permissions on one of the databases are having an effect.

If that makes no difference, does the GUI behave any different to the command line? There's a couple of issues under investigation where the commandline sometimes yields slightly different results, and you may be encountering this.

If neither of the above help it may well be a problem specific to your DB's - if you're able to send snapshots (create these on the File menu) to us, referencing F0064853 in the subject line, we can see if there's anything obvious.
Back to top
View user's profile Send private message
ChrisGodfree



Joined: 10 May 2012
Posts: 7

PostPosted: Mon Sep 24, 2012 4:56 pm    Post subject: Reply with quote

Thanks James, looks like it is permissions.

The GUI behaves the same as the CL but if I'm using a Source database where I only have db_datareader and View Definition on schemas then I find the problem. I did notice that if I right-click on an object and attempt to View Dependencies in the Source database then no results are returned - i.e. no error message. A quick Google search comes back with this which makes sense:

Requires VIEW DEFINITION permission on the database and SELECT permission on sys.sql_expression_dependencies for the database. By default, SELECT permission is granted only to members of the db_owner fixed database role.

We can work around the issue so no problem, just slightly frustrating!
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Mon Sep 24, 2012 5:02 pm    Post subject: Reply with quote

That looks like it - unfortunately SQL Compare does require access to some higher level procedures (more-so if you use encrypted objects at all) to be able to correctly evaluate the database).
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