Red Gate forums :: View topic - Creating single db from multiple dbs-Owner mapping issue
Return to RSS Feed Available

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

Creating single db from multiple dbs-Owner mapping issue

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

Joined: 29 Aug 2011
Posts: 12

PostPosted: Tue Jun 12, 2012 4:44 pm    Post subject: Creating single db from multiple dbs-Owner mapping issue Reply with quote

I want to use SQL Compare to compare stored procedure, function, and view objects from several existing databases against those in an empty database, with the intent of consolidating multiple databases into a single database with multiple schemas. In the source databases, the objects are all in the dbo schema, but in the target database, the objects will be in the schema having the same name as the source database, e.g. DB1, DB2, etc. The issue I am having is with the owner mapping. When I run the comparison, I set the owner mapping of the dbo schema of the source database to the corresponding schema in the target database, e.g. DB1, because I want SQL Compare to create all of the new objects in the DB1 schema of the target database, not the dbo schema. This works as expected. However, in many cases I have script code inside of my objects that reference some of the other databases by name, e.g. "DB2.dbo.OBJECTNAME", which seem to be changed automatically by SQL Compare to "DB1.OBJECTNAME", which is not what I want. Instead, I either want SQL Compare to leave the external database references alone, or ideally I would want it to automatically map them to the new schema that I created with the same name as the external database, e.g. DB2.OBJECTNAME. Is this possible? And if not, what would be the best way to accomplish what I am trying to do, i.e. creating a single database with multiple schemas from multiple source databases? Any help would be greatly appreciated. Thanks
Back to top
View user's profile Send private message

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

PostPosted: Thu Jun 14, 2012 8:04 pm    Post subject: Reply with quote

Hi Mary,

I've had a quick test of the scenario you describe and I seem to get the same thing. I'm not sure if my test is exactly the same process as you have, but I seem to get a similar result, in that as well as changing the schema on the object itself (a sproc in my case, and which I assume is correct) we seem to change it on any references inside the code of the procedure.

I wondered if synonyms would help, so I set one up to point to a table in another database, and changed my proc to do a "select from <synonym>" but Compare still bolts the new schema name on the front of that, which is odd, as there's nothing for it to actually want to replace there. Of course this is slightly less horrible, because you can have the synonym in the new database and still have it point to the correct external reference. The only problem is how much work it will be for you to change to using those...

At this point I'm not sure if what you're seeing is a bug or just intended behaviour and you have a slightly odd scenario, so I've asked the Compare team for their thoughts. If it's a bug then of course we'll raise a fix request for that, although I'm not sure as to a timescale for a fix should that happen.
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