Support Center

Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Cross-database reference support (eg. view that reference data in another db)

Dylan Beattie Jun 12, 2013 03:44AM AEST

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?

Thanks,

Dylan

Up -6 rated Down
Daniel Nolan Jun 24, 2013 05:54PM AEST ReadyRoll Agent

Hi Dylan,

(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:
http://ready-roll.com/blog/2012/12/06/readyroll-1-4-variables-variables-variables/

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 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).

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. 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.

Best Regards,

Daniel

This question has received the maximum number of answers.

readyroll.support@red-gate.com
http://assets2.desk.com/
false
readyrollsql
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/en/portal/articles/autocomplete