December 15, 2015 at 11:03 am
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
December 15, 2015 at 11:19 am
I am assuming that you have a reason for not adding suitable database references, to make the errors and warnings disappear?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 15, 2015 at 11:27 am
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.
December 17, 2015 at 7:37 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply