Cross-database reference support
priyasinha
Redgate › Posts: 548 Silver 1
Note: This issues has been migrated from ReadyRoll support forum
We have a 'central' database, and then several 'auxiliary' databases which contain views into data in the central database.
I'm trying to import one of the auxiliary databases into a new ReadyRoll project. It's created the initial import script OK, but then failing when I try to Build (Verify Script) - when it gets to creating a view within the auxiliary database that references one of these cross-DB views. The exact message is "Invalid object name 'Foo'" - where Foo is a view that looks like
CREATE VIEW Foo AS SELECT Column1, Column2, Column3 FROM CentralDatabase.dbo.Foo
Does ReadyRoll explicitly support these kind of views? Explicitly NOT support them? Is there some workaround that I can use?
We have a 'central' database, and then several 'auxiliary' databases which contain views into data in the central database.
I'm trying to import one of the auxiliary databases into a new ReadyRoll project. It's created the initial import script OK, but then failing when I try to Build (Verify Script) - when it gets to creating a view within the auxiliary database that references one of these cross-DB views. The exact message is "Invalid object name 'Foo'" - where Foo is a view that looks like
CREATE VIEW Foo AS SELECT Column1, Column2, Column3 FROM CentralDatabase.dbo.Foo
Does ReadyRoll explicitly support these kind of views? Explicitly NOT support them? Is there some workaround that I can use?
Priya Sinha
Project Manager
Red Gate Software
Project Manager
Red Gate Software
Tagged:
Comments
Firstly, may I suggest having a bit of a read of the Multi-Database Support section:
https://documentation.red-gate.com/disp ... er6th,2012
This gives you a bit of background as to how set up cross-database references.
Project setup
After adding a database reference from your auxiliary db project, you use dynamic 3-part references within your auxiliary dbs views/stored procs/etc to refer to the central database tables. For example, instead of using this static syntax to refer to a table in another database: [MyOtherDb].[dbo].[MyTable], you use a SQLCMD variable in place of the database name, eg. [$(OtherDb)].[dbo].[MyTable].
Note that the DBSync tool will not currently replace the static 3-part references with dynamic references. For this reason, we recommend against using 3-part references with variables directly within your programmable objects (stored procedures/views/functions) etc. Instead, we suggest creating synonyms to refer to the table (or other) objects across the database boundary, and using the synonym within your programmable objects. This keeps a good separation between the parts of your solution that are dynamic (inter-database references) from the parts of the solution that are static (programmable objects).
Using Synonyms to simplify cross-database references
To make cross-database references easier to use, we recommend using Synonyms for your three-part database object references. This means that you can use a static object reference in your views/stored procs/etc, so that the DbSync tool doesn't overwrite your dynamic references during import. For example, if you add a migration to create a synonym called syn_MyTable pointing to the table [$(OtherDb)].[dbo].[MyTable], i.e.:
...you can simply reference the synonym in place of the 3-part reference:
Here's a sample ReadyRoll project you can download to see how synonyms work with cross-database references:
http://download.ready-roll.com/RR_SampleProject_CrossDbRefWithSynonyms.zip
Project Manager
Red Gate Software
The reference project link appears to be broken.
Do you have an updated location where we can download this example project?
Thank you.
The reference project link appears to be broken.
Do you have an updated location where we can download this example project?
Thank you.
Also, for anyone else reading this, please note that there is a limitation in the way that cross-database references currently work: values for the SQLCMD variables that reference other databases must be manually set. For more information, please see this thread.
Product Manager
Redgate Software