Cross-database reference support -broken?

christianaccachristianacca Posts: 24 Bronze 1
edited September 7, 2017 9:14AM in ReadyRoll
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:

6kxy28269miv.png
Tagged:

Comments

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

    CREATE SYNONYM [dbo].[BinaryFileTable] FOR [$(Series5Doc)].[dbo].[BinaryFile]
    GO
  • 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?
    Daniel Nolan
    Product Manager
    Redgate Software
  • 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
  • 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.
    Daniel Nolan
    Product Manager
    Redgate Software
  • Thanks for the confirmation re expected behavior.

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

    Thanks
  • 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.
    Daniel Nolan
    Product Manager
    Redgate Software
  • abeierabeier Posts: 3 New member
    @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
  • 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.
    Daniel Nolan
    Product Manager
    Redgate Software
Sign In or Register to comment.