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?
(Posting the resolution that I provided in our offline discussion on JUN 12TH, 2013)
Cross-database references are supported by ReadyRoll, however there is an amount of initial set-up work required.
Firstly, may I suggest having a bit of a read of the “Multi-Database Support” section of this blog post:
This gives you a bit of background as to how set up cross-database references.
After adding a database reference from your auxiliary db project, you use dynamic 3-part references within your auxiliary db’s 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: [MyCentralDatabase].[dbo].[MyTable], you use a SQLCMD variable in place of the database name, eg. [$(MyCentralDatabase].[dbo].[MyTable].
Note that the DBSync tool will not currently replace the static 3-part references with dynamic references (you would need to edit the generated script after Import to do this).
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. For example, if you create a synonym called “MyCentralDatabase_dbo_MyTable” pointing to the table [$(MyCentralDatabase)].[dbo].[MyTable], you can simply reference the synonym in place of the 3-part reference, eg. CREATE VIEW Foo AS SELECT Column1, Column2, Column3 FROM [MyCentralDatabase_dbo_MyTable]
To help populate your auxiliary databases with synonyms for the tables contained within the central database, I have attached a script to this answer which you can add to the “Pre-Deployment” folder of each of your auxiliary database projects. This script will run on every deployment to ensure that all tables have synonyms created for them. Just replace the values for the @SynonymPrefix and @DatabaseToReference variables to suit your database solution.
Going forward, this is an area of ReadyRoll that we’d like to improve, so if you have any feedback on the usability of this approach please let me know.
This question has received the maximum number of answers.