Cross-database reference support -broken?

christianacca

I've followed the guidance here: Cross-database reference support

But when I run readyroll (press the create database button), the deployment fails with the following error:

'Series5Doc' scripting variable not defined
Ram.Series5.Db
...Migrations\1.0.0\001.sql

What am I doing wrong?

Here is the screen shot of creating the database reference:

christianacca

Here's the line in migration (001.sql) that is failing:

CREATE SYNONYM [dbo].[BinaryFileTable] FOR [$(Series5Doc)].[dbo].[BinaryFile]
GO

dnlnln

Thanks for posting that additional code. I can confirm that this is a bug, and appears to have possibly occurred as a result of changes to the supporting functionality within the SSDT project system.

As a work-around, try adding $(Series5Doc) manually to the SQLCMD Variables tab with the appropriate database name as the default value. Does that allow you to deploy successfully?

christianacca

OK, thanks for confirming the status.

Yup, I knew that I could manually create a variable as you suggest.

Once the regression is fixed can you confirm whether the following is the expected behavior:

1. A SQLCMD variable will be made available to reference in migrations without having to manually create one myself
2. The variable will be added to the "SQLCMD package (.sql file)" alongside the variables I have manually created
3. The value of this variable added to the "SQLCMD package (.sql file)" will be the database defined in the connection string used by referenced database project

Out of interest, what other behavior is added to the readyroll build system aside from making a variable available?

Thanks
Christian

dnlnln

Hi Christian,

Apologies for the delay in responding. That is indeed the expected behaviour of the variables when database references are added to the project. The only additional thing that database refs do is ensure that the solution is deployed in the correct order of dependency, i.e. if ProjectB depends on ProjectA, it ensures that ProjectA deploys first.

In point-of-fact, there wasn't a substantial uptake of the Add Database Reference feature mentioned in the above forum post (which was released back in 2012). Due to the amount of up-front work involved in setting up the synonyms, most customers tend to just set the deployment order in the VS solution (instead of adding db references) and simply leave their cross-db objects refs as static references, i.e. SELECT * FROM OtherDb.dbo.MyTable). Of course, this only work in scenarios where the database names are the same each environment.

christianacca

Thanks for the confirmation re expected behavior.

What's the ETA for a release with a fix for this bug?

Thanks

dnlnln

The team is currently re-evaluating the handling cross-database references based on feedback from a number of customers. I don't have an ETA on a fix as yet, however once there is some news I'll be sure to post an update.

Thanks for your patience.

abeier

@dnlnln

dnlnln said:

The team is currently re-evaluating the handling cross-database references based on feedback from a number of customers. I don't have an ETA on a fix as yet, however once there is some news I'll be sure to post an update.

Thanks for your patience.

Hi Daniel,
Any update if this issue was fixed and if there are any other known issues currently with cross database support?  Our team has been using ReadyRoll on other projects but just came across the first project that has a need for cross database functionality and are deciding the "best way" to handle it.

Thank you

dnlnln

abeier said:

Hi Daniel,
Any update if this issue was fixed and if there are any other known issues currently with cross database support?  Our team has been using ReadyRoll on other projects but just came across the first project that has a need for cross database functionality and are deciding the "best way" to handle it.

Thank you

Unfortunately there have been no further developments on this issue.

The recommended approach for doing multi-database development at this stage is to simply retain the static names used in three and four part object references (e.g. SELECT * FROM [OtherDb].[dbo].[TableName] or SELECT * FROM [OtherServer].[OtherDb].[dbo].[TableName]) and set the Build Order in the Solution Explorer to reflect the dependencies between each database/project.

Any questions please let me know.