Cross-database reference support

priyasinha

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?

priyasinha

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

-- <Migration ID="be08bb5e-434a-4916-800a-0e9c6515706b" />
GO
CREATE SYNONYM [dbo].[Syn_MyTable] FOR [$(OtherDb)].[dbo].[MyTable]
GO

...you can simply reference the synonym in place of the 3-part reference:

CREATE VIEW Foo 
AS 
SELECT ID 
FROM [Syn_MyTable]

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

abeier

@priyasinha@dnlnln

The reference project link appears to be broken.

http://download.ready-roll.com/RR_SampleProject_CrossDbRefWithSynonyms.zip

Do you have an updated location where we can download this example project?
 
Thank you.

abeier

@priyasinha@dnlnln

The reference project link appears to be broken.

http://download.ready-roll.com/RR_SampleProject_CrossDbRefWithSynonyms.zip

Do you have an updated location where we can download this example project?
 
Thank you.

dnlnln

Please find the sample project attached to this message.

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.