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