Database Project - cross database references

  • SoCal_DBD

    Hall of Fame

    Points: 3051

    I have done a get of the most recent version of a database project from TFS (I'll call it Project A). This project has cross database references in some stored procedures and views. I have no trouble building the project because all these cross database references are showing as merely warnings and not errors. I created a brand new database project (Project B) based off an existing database, and when I build, any cross database references are coming up as errors instead of warnings and I cannot build the project. My manager and I have checked absolutely everything and compared the two projects and cannot figure out why Project A will build and Project B will not.

    Project A

    - There are no references to any other other databases in the project. I'm assuming this because I do not see any items under the References node in the project. I also scoured the .sqlproj XML file to see if there is anything in there that I'm not seeing in the GUI that might indicate a database reference. I found nothing.

    - Cross database references are NOT in the form of "INNER JOIN [$(ReferencedDBVar)].[dbo].[SomeTable]". They are the standard "INNER JOIN DBName.[dbo].[SomeTable]" (exactly that syntax with brackets where I've designated). The fact that they do not use [$(ReferencedDBVar)] is also making me think there are no references to other databases.

    - The properties of the project do NOT indicate that that any Transact-SQL warnings should be suppressed, nor is the checkbox for "Treat Transact-SQL warnings as errors" checked.

    - I've compared the query options (with the script open, right click and select Exectuion Settings, Query Options..) of procedures with cross database logic with those that do not and all the settings are the same.

    - I've compared the properties of procedures with cross database logic with those that do not and Build Action, ANIS Nulls and Quoted Identifiers values are the same.

    Project B

    - Since all settings are the same, the only thing I did was change the syntax to use square brackets around the schema and table name (completely grasping at straws at this point). This still did not work.

    How is Project A building successfully with cross database references, even though there are no database references configured in the project? Is there some other setting we are missing?

    -- EDIT:

    Might be onto something... The errors in Project B are coming from Views with cross database references. Project A has no views. All cross database references in Project A are in stored procedures only. I found a stored procedure in Project B with a cross database reference and it does not appear to be generating any errors on build (I filter my Errors on the currently open document and it's not generating errors). Looks like I need to dig into views vs. procedures with regard to cross database references.

    Thanks -

    Lisa

  • Phil Parkin

    SSC Guru

    Points: 243847

    I am assuming that you have a reason for not adding suitable database references, to make the errors and warnings disappear?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • SoCal_DBD

    Hall of Fame

    Points: 3051

    We created Project B simply to get our feet wet with working with database projects. We are helping another team out with Project A and wanted to familiarize ourselves with things before we did anything. Then we went down this rabbit hole. All traditional cross database references in Project A are within stored procedures. There are no views in Project A. Project B has stored procedures and views with traditional cross database references and if I exclude the views from the project (just to see what happens), sure enough it builds. So there is something different with how views with cross databases are treated during a build vs. stored procedures. I think I have enough to go on at this point. And since Project B is more of a sandbox, we may try to avoid going too far off course at this point.

  • MadAdmin

    SSChampion

    Points: 11260

    I had a similar issue building DacPacs cos it seems like it does validation on build.

    The workaround was to execute sqlpackage using command line as validation default is off with command line.

    Could be something similar.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply