Database projects don't allow 3 parts object's names and cross-refs
-
Hello all,
There are three similar BI-environments (dev, test and prod) including several databases pro environment. Within each of them a structure has many cross-references between its databases (in views and sps )
Now we are going to start with SQL Database Projects (MS Server Data Tool 2015) and intend to get deployment process between enviroments dev->test->prod under our control (as far as it possible 🙂
Unfortunately it is not as simple as we waited for and given db-object's names provide a lot of issues: somethimes they include 3 pats ( [DBName].[SchemaName].[TableName] ) sometimes 2 parts ( [SchemaName].[TableName] ) and of cause all objects can origin from different DBs on a server.I tried to put all DB-Projects in one solution, to provide DB-references between them and to replace DBnames by objects with variables like $(DBName) -- no progress. 🙁 Is somewhere a clear HowTo-inctruction or can someone give me some an advice for a such case? I think it is strange, that a compiler cannot find references for objects also within a same DB as soons as it has a 3 parts-name. If I delete DBName-part from an object's name (doing from 3-part a 2-part) the issue disappears. But on one hand it is too big manuall affort and on the other hand it is not possible for objects refferenting to other DBs.
I guess I'm thinking in a wrong way. Please, could you give me a correct direction?Thanks a lot!
Alexander - alexander.parakhnevich 27977 - Friday, August 18, 2017 6:49 AM
Alexander, there is a way of handling this.
1) Use two-part naming for all references within the current database (ie, use three-part naming only when the DB being referred to is not the current DB).
2) Keep all DBs in the same solution (as you are doing)
3) When creating a DB reference, delete the default text in the 'Database variable' section, as in the following image, for a referenced database called 'Logging'
Notice how the 'Example usage' section gives you an indication that you're on the right track.
4) In the medium term, work to remove any 'circular' references (DB1 refers to objects in DB2 and vice versa), this will only give you grief. One-way references (eg, to a common 'Utilities' DB) are perfectly workable.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 - Phil Parkin - Friday, August 18, 2017 7:11 AMAlexander, there is a way of handling this.
1) Use two-part naming for all references within the current database (ie, use three-part naming only when the DB being referred to is not the current DB).
2) Keep all DBs in the same solution (as you are doing)
3) When creating a DB reference, delete the default text in the 'Database variable' section, as in the following image, for a referenced database called 'Logging'
Notice how the 'Example usage' section gives you an indication that you're on the right track.
4) In the medium term, work to remove any 'circular' references (DB1 refers to objects in DB2 and vice versa), this will only give you grief. One-way references (eg, to a common 'Utilities' DB) are perfectly workable.Hello Phil,
thanks a lot for a very quick answer and valued advice. Your point 3 was exactly my main problem, I didn't consider a default value in the marked field on your pic and after it was removed everything became clear.
And yes... we have circled references between DBs - this is the second big issue found after your post and it must be solved before we can go forward with DB projects , just because it is not technicaly possible to configure such mutual references within a solution.Once more time thank you! I think, many people like me will be glad to find your explanation heir. 🙂
Alexander
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply