June 27, 2018 at 3:34 am
tl;dr: How do you organize your multiple SSDT projects that comprise a single solution?
I've got a DW with these logical layers: Extract/Cleanse/Transform/DataWarehouse/DataMart.
Thus far, I've used separate, distinct Integration Services solutions: New --> Project --> Integration Services Project --> Give it a name, Default Location, Solution Name = Project Name, Create Directory for solution.
I'm just now starting to use SQL Server Database Projects, and want to use them to manage database schema changes. With that in mind, I have done the following:
In the file system, I've got <Root Path>\MyDWName\Extract|Cleanse|Transform|DataWarehouse|DataMart folders.
Because I used the same name for the two project types, they are merged at the file system level: for example, I have bin | dbo | Import Schema Logs | <other schema folders>, plus *.dtsx, *.sqlproj, *.dtproj, etc files, all in the same folder.
This was "unexpected", and perhaps a lesson learned.
I'm also experiencing "headaches" when I try to build the project. Many of the Database project have build issues - from my Googling, it seems this is typical after an initial database import. I've configured the build options to temporarily exclude projects as I slowly work through the issues, but this too is a headache. I thought I could just build a project separate from the entire solution, rather than the entire solution at once (with in simple RMB or menu choice, rather than having to edit the configuration all the time).
So, back to the tl;dr: If you have a set of databases that comprise a solution/DW/application, and you have both SQL Server Database projects and Integration Services projects relevant to that solution/DW/application, how do you organize your work?
June 27, 2018 at 5:28 am
Scott In Sydney - Wednesday, June 27, 2018 3:34 AMtl;dr: How do you organize your multiple SSDT projects that comprise a single solution?I've got a DW with these logical layers: Extract/Cleanse/Transform/DataWarehouse/DataMart.
Thus far, I've used separate, distinct Integration Services solutions: New --> Project --> Integration Services Project --> Give it a name, Default Location, Solution Name = Project Name, Create Directory for solution.
I'm just now starting to use SQL Server Database Projects, and want to use them to manage database schema changes. With that in mind, I have done the following:
- Created a new blank solution MyDWName. This is the overall name of the DW.
- Created two solution folders Database and SSIS in the solution.
- In the Database solution folder, created new SQL Server Database projects Extract/Cleanse/Transform/DataWarehouse/DataMart.
- In the SSIS solution folder, created Integration Services Import Project Wizard project, and imported them into matching Extract/Cleanse/Transform/DataWarehouse/DataMart projects
My thinking was that I could have a "one stop shop" solution where all my relevant bits would be located in the single solution.In the file system, I've got <Root Path>\MyDWName\Extract|Cleanse|Transform|DataWarehouse|DataMart folders.
Because I used the same name for the two project types, they are merged at the file system level: for example, I have bin | dbo | Import Schema Logs | <other schema folders>, plus *.dtsx, *.sqlproj, *.dtproj, etc files, all in the same folder.
This was "unexpected", and perhaps a lesson learned.
I'm also experiencing "headaches" when I try to build the project. Many of the Database project have build issues - from my Googling, it seems this is typical after an initial database import. I've configured the build options to temporarily exclude projects as I slowly work through the issues, but this too is a headache. I thought I could just build a project separate from the entire solution, rather than the entire solution at once (with in simple RMB or menu choice, rather than having to edit the configuration all the time).
So, back to the tl;dr: If you have a set of databases that comprise a solution/DW/application, and you have both SQL Server Database projects and Integration Services projects relevant to that solution/DW/application, how do you organize your work?
One of the biggest factors to bear in mind here is that the more projects you add to a solution, the slower VS performs.
Our solutions contain logical groups of projects, of the same type – no mixing of DB and SSIS projects, because there are never any direct dependencies between the two. If you have any cross-database dependencies (we have a 'utility' database, for example, which contains useful functions called from several other DBs), all of the dependent DBs need to be in the same project, in order for the solution to build without warnings (after adding appropriate references).
In your scenario, how many databases do you have in each layer? Are there thousands of objects in each? As usual, there is no single rule which works in all situations, but 'logical groups' is the term I recommend you bear in mind when assessing this.
But before you even get to this stage, it sounds like you have some refactoring to do, to fix up your build warnings. And if you have any bi-directional database dependencies (database A references objects in database B and vice versa), you are going to have to fix them: SSDT supports only uni-directional dependencies.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 27, 2018 at 6:56 am
Phil Parkin - Wednesday, June 27, 2018 5:28 AMOne of the biggest factors to bear in mind here is that the more projects you add to a solution, the slower VS performs.Our solutions contain logical groups of projects, of the same type – no mixing of DB and SSIS projects, because there are never any direct dependencies between the two. If you have any cross-database dependencies (we have a 'utility' database, for example, which contains useful functions called from several other DBs), all of the dependent DBs need to be in the same project, in order for the solution to build without warnings (after adding appropriate references).
In your scenario, how many databases do you have in each layer? Are there thousands of objects in each? As usual, there is no single rule which works in all situations, but 'logical groups' is the term I recommend you bear in mind when assessing this.
But before you even get to this stage, it sounds like you have some refactoring to do, to fix up your build warnings. And if you have any bi-directional database dependencies (database A references objects in database B and vice versa), you are going to have to fix them: SSDT supports only uni-directional dependencies.
There are 5 databases in the DW:
Extract: ~ 30 tables * 2 (stg and ext schema), ~ 30 views, a few synonyms
Cleanse: ~ 30 tables, ~ 30 views, ~ 30 synonyms
Transform: ~ 45 tables, ~ 45 views, ~ 30 synonyms
DataWarehouse: ~ 30 tables, ~ 60 views, ~ 30 SP's, ~ 35 synonyms
DataMart: still being constructed, but will be 2 really wide de-normalized tables plus a few (< 10?) views.
So, in general, relatively small number of tables. A few tables of 100-300million rows, most much smaller.
Any references to other databases should be handled via synonyms. I don't have any references to three-level objects; instead I use synonyms.
The build warnings are due to problems with the reference to master. I posted that one on StackOverflow: https://stackoverflow.com/questions/51057659/vs-2017-database-project-unresolved-reference-to-object-error
Ok, so based on your reply, at a minimum I should keep database and integration services projects in separate solutions. My big problems right now are the database projects; they are fine in SSMS, but have issues after the import into SSDT. I'm wondering if I should just keep all the projects separate - one solution, one project. Then just keep them organized on the file system with the usual directory structure?
June 27, 2018 at 7:01 am
Does your utility database just contain functions, or stored procedures as well? I tried and tried to use a tools or utilities database to store SP's that would inherit the context of the current database, but could never get that to work. The only way I could make it work was put them in master, name them sp_* (I use a naming convention - sp_MoH_* to prevent future naming collisions), and mark them system objects. My preference would have been not to touch master at all.
June 27, 2018 at 8:02 am
Phil Parkin - Wednesday, June 27, 2018 5:28 AM(we have a 'utility' database, for example, which contains useful functions called from several other DBs), all of the dependent DBs need to be in the same project, in order for the solution to build without warnings (after adding appropriate references).
Does your utility database just contain functions, or stored procedures as well? I tried and tried to use a tools or utilities database to store SP's that would inherit the context of the current database, but could never get that to work. The only way I could make it work was put them in master, name them sp_* (I use a naming convention - sp_MoH_* to prevent future naming collisions), and mark them system objects. My preference would have been not to touch master at all.
To do that does require the creation of 'system' stored procs in master, as far as I know.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 28, 2018 at 10:58 pm
Hi All,
More input on this...
I ended up using a lot of synonyms based on this: https://www.sqlservercentral.com/Forums/1916222/How-do-I-code-a-threelevel-table-name-in-SSDT-that-will-honour-environment-settings-in-deployed-job. I'm following Jeff Moden's and Steve Jone's advice.
I'm also using a lot of views, but I'm not using 3 level names anywhere in the views/SP's/etc. In summary, lots of views, and views use synonyms to "reach across" to other databases via two-level names.
The dependencies are as follows:
Extract: One synonym to a UDF in master
Cleanse: One synonym to a UDF in master, ~ 30 synonyms to tables in Extract.
Transform: Two synonyms to UDF's in master, ~ 30 synonyms to tables in Cleanse, 4 synonyms to tables in DataWarehouse, 1 synonym to a view in DataWarehouse.
Note: During the Transform processing, the first thing that needs to be done is 4 master "keys" tables need to be updated in the DataWarehouse layer to create SK's. Those tables are then joined back to the Cleanse tables during the transform processing.
DataWarehouse: One synonym to a utility view in Extract, ~ 30 synonyms to tables in Transform.
CREATE SYNONYM [trn].[EPISODE] FOR [RLDX_dev_Transform].[trn].[EPISODE] to CREATE SYNONYM [trn].[EPISODE] FOR [$(Transform)].[trn].[EPISODE]Thanks...
June 29, 2018 at 7:35 am
Scott In Sydney - Thursday, June 28, 2018 10:58 PMHi All,More input on this...
I ended up using a lot of synonyms based on this: https://www.sqlservercentral.com/Forums/1916222/How-do-I-code-a-threelevel-table-name-in-SSDT-that-will-honour-environment-settings-in-deployed-job. I'm following Jeff Moden's and Steve Jone's advice.
I'm also using a lot of views, but I'm not using 3 level names anywhere in the views/SP's/etc. In summary, lots of views, and views use synonyms to "reach across" to other databases via two-level names.
The dependencies are as follows:
Extract: One synonym to a UDF in master
Cleanse: One synonym to a UDF in master, ~ 30 synonyms to tables in Extract.
Transform: Two synonyms to UDF's in master, ~ 30 synonyms to tables in Cleanse, 4 synonyms to tables in DataWarehouse, 1 synonym to a view in DataWarehouse.
Note: During the Transform processing, the first thing that needs to be done is 4 master "keys" tables need to be updated in the DataWarehouse layer to create SK's. Those tables are then joined back to the Cleanse tables during the transform processing.DataWarehouse: One synonym to a utility view in Extract, ~ 30 synonyms to tables in Transform.
I had to create a "custom" dacpac of my master database via SSDT SQL Server Object Explorer, and use that for the reference to master.
- I had to add the 4 database projects into one single solution. Otherwise I could not add the references between the different databases. I did not want to add references via static dacpac's that continually required updating.
- I eliminated many of the build errors by refactoring the synonyms from
CREATE SYNONYM [trn].[EPISODE] FOR [RLDX_dev_Transform].[trn].[EPISODE]toCREATE SYNONYM [trn].[EPISODE] FOR [$(Transform)].[trn].[EPISODE]- BUT, I cannot add the reference to Transform in the DataWarehouse project, due to circular reference. I admit that Transform references DataWarehouse, and DataWarehouse references Transform, but they are different objects; it doesn't cause a circular reference at the object level.
Is there anything I can do to coax the database projects to work with this configuration? AFAIK, I was designing this in a good manner IAW the advice in the link above. Note the code works fine in the Integration Services projects and from within SSMS. I just can't get this shoehorned into the Database projects (Extract and Cleanse work, Transform and DataWarehouse fail).Thanks...
Edit: Duh, separating the projects into one or two projects/solution (just the minimum dependencies), with the references as needed, should work. But is there a better approach?
I use three-part names when calling things like utility functions. This works fine and resolves within SSDT, as long as a database reference is added and all databases are in the same solution.
I don't know about using SYNONYMs in database projects.
I don't really understand the problem you had with connection strings in SSIS. The way I do it is as follows:
1) The 'default ' connection string (ie, the one contained in the package) is to databases on my own (or another developer's) development machine (we're all using localhost, so this works).
2) After deploying the package to QA or Production, connection strings are overridden by SSISDB environment variables.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 1, 2018 at 6:58 pm
Phil Parkin - Friday, June 29, 2018 7:35 AMI use three-part names when calling things like utility functions. This works fine and resolves within SSDT, as long as a database reference is added and all databases are in the same solution.I don't know about using SYNONYMs in database projects.
I don't really understand the problem you had with connection strings in SSIS. The way I do it is as follows:
1) The 'default ' connection string (ie, the one contained in the package) is to databases on my own (or another developer's) development machine (we're all using localhost, so this works).
2) After deploying the package to QA or Production, connection strings are overridden by SSISDB environment variables.
Hi Phil,
First of all, I appreciate your input. It feels like Data Warehousing --> Integration Services doesn't get the same traffic as some of the other forums. So thanks, much appreciated.
The problem isn't with connection strings, the problem is in using three-level names in views, and promoting that code through dev/test/prod.
My databases are all on the same server, and are named (assume the application is named Acme) Acme_dev_Extract, Acme_dev_Cleanse, Acme_dev_Transform, etc. Replace "dev" with "prod" for the prod environment.
If I follow Jeff Moden's and Steve Jones's advice to never use three-level names in my code, but use synonyms instead, then, when I promote from dev to prod, all I have to do is change my synonyms. And if I use SSDT database projects, all I have to do is change the reference database name.
There is nothing wrong with the Transform layer using the Cleanse layer as a source, and joining with a table in the DataWarehouse layer.
And there is nothing wrong with the DataWarehouse layer using the Transform layer as a source, and implementing SCD2 processing.
But if I try to do this using synonyms and references, SSDT calls this a circular reference. Which it isn't.
I've entered a bug at https://feedback.azure.com/forums/908035-sql-server. I'll be curious what Microsoft says.
July 2, 2018 at 5:17 am
Scott In Sydney - Sunday, July 1, 2018 6:58 PMHi Phil,First of all, I appreciate your input. It feels like Data Warehousing --> Integration Services doesn't get the same traffic as some of the other forums. So thanks, much appreciated.
The problem isn't with connection strings, the problem is in using three-level names in views, and promoting that code through dev/test/prod.
My databases are all on the same server, and are named (assume the application is named Acme) Acme_dev_Extract, Acme_dev_Cleanse, Acme_dev_Transform, etc. Replace "dev" with "prod" for the prod environment.
If I follow Jeff Moden's and Steve Jones's advice to never use three-level names in my code, but use synonyms instead, then, when I promote from dev to prod, all I have to do is change my synonyms. And if I use SSDT database projects, all I have to do is change the reference database name.
There is nothing wrong with the Transform layer using the Cleanse layer as a source, and joining with a table in the DataWarehouse layer.
And there is nothing wrong with the DataWarehouse layer using the Transform layer as a source, and implementing SCD2 processing.
But if I try to do this using synonyms and references, SSDT calls this a circular reference. Which it isn't.I've entered a bug at https://feedback.azure.com/forums/908035-sql-server. I'll be curious what Microsoft says.
As I'm sure you are aware, having dev/QA/Prod on the same server (and presumably the same instance) is a disaster-in-waiting. Is there any chance that you could at least get separate SQL instances in place for the different environments? It won't solve all of the possible issues, but it will make your process cleaner (and help avoid the possibility of QA DBs updating Prod DBs...).
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 31, 2018 at 1:48 am
Phil Parkin - Monday, July 2, 2018 5:17 AMAs I'm sure you are aware, having dev/QA/Prod on the same server (and presumably the same instance) is a disaster-in-waiting. Is there any chance that you could at least get separate SQL instances in place for the different environments? It won't solve all of the possible issues, but it will make your process cleaner (and help avoid the possibility of QA DBs updating Prod DBs...).
Yes we're in the process of upgrading our SQL Servers, and will have separate servers for Dev and Prod. However, this is a red herring, and irrelevant to my original post.
I still think is a design bug in SQL Database projects, and I'm not aware of any workaround.
See also https://feedback.azure.com/forums/908035-sql-server/suggestions/34717561-ssdt-database-project-incorrectly-flags-reference. Of course, no reply from Microsoft.
July 31, 2018 at 5:38 am
Scott In Sydney - Tuesday, July 31, 2018 1:48 AMPhil Parkin - Monday, July 2, 2018 5:17 AMAs I'm sure you are aware, having dev/QA/Prod on the same server (and presumably the same instance) is a disaster-in-waiting. Is there any chance that you could at least get separate SQL instances in place for the different environments? It won't solve all of the possible issues, but it will make your process cleaner (and help avoid the possibility of QA DBs updating Prod DBs...).Yes we're in the process of upgrading our SQL Servers, and will have separate servers for Dev and Prod. However, this is a red herring, and irrelevant to my original post.
I still think is a design bug in SQL Database projects, and I'm not aware of any workaround.
See also https://feedback.azure.com/forums/908035-sql-server/suggestions/34717561-ssdt-database-project-incorrectly-flags-reference. Of course, no reply from Microsoft.
If the 'design bug' you are referring to is the fact that database projects cannot contain circular references, there is no workaround that I am aware of.
In my opinion, however, this is not a bug. I've worked in environments where the databases were not under SSDT control and circular references were rife (so database A contains views which reference database B and vice versa).
I think that you would argue that this is (technically, at least) fine, because there is no circular reference at the object level.
However, if you try to deploy either of these databases to a new instance, you'll get a missing object reference error (can't remember the exact text of the error) and the deployment fails. It's a mess. Allowing only unidirectional references avoids this.
Having separate servers does at least mean that you can rationalise your database naming. No need for the dev/prod text as part of the name any more & this should make the promotion of changes through the different environments easier to manage.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 31, 2018 at 7:45 pm
If the 'design bug' you are referring to is the fact that database projects cannot contain circular references, there is no workaround that I am aware of.
No that's not what I'm asserting. I'm happy if SSDT disallows circular references. But it's not a circular reference if it's not a circle.
In my opinion, however, this is not a bug.
In my opinion it is.
I think that you would argue that this is (technically, at least) fine, because there is no circular reference at the object level.
Yes that's exactly what I'm saying. AFAIK there is NO issue with this from a database design perspective, and AFAIK it doesn't even violate best practice (except for the database names, which is not germane to this issue). Yet SSDT chokes on this - I won't be able to use SQL Database projects to manage my DW objects. I'll have to stick with SSMS, which is a pity, as I really wanted to learn SQL Database projects and use it for this work I'm doing.
However, if you try to deploy either of these databases to a new instance
This is moot; since SSDT chokes on this, I can't "deploy". If I generate scripts in SSMS (without a USE statement), then run the scripts in the correct order against a new database(s), it "deploys" just fine. Although admittedly I'd have to do a global search-and-replace on the synonym definitions.
Allowing only unidirectional references avoids this.
See above. No but it makes Microsoft's job easier (and SQL Database projects less useful), since SSDT doesn't have to consider whether the reference is actually circular. If Database A updates a table in Database B, then Database A uses that table in Database B in joins with other tables, SSDT says this is a circular reference; I assert it is not. More specifically, if Database A has a synonym to Database B.TableA, and Database B has synonyms back to Database A.<some tables>, SSDT says this is a circular reference; I assert it is not...it's not a circle.
Hopefully Microsoft will fix this in a later release.
(Heck, maybe I should just bite the bullet and use a three-level name for this one table, rather than a synonym. I'd have to test if that would make SSDT happy.)
Having separate servers does at least mean that you can rationalise your database naming. No need for the dev/prod text as part of the name any more & this should make the promotion of changes through the different environments easier to manage.
I agree, but sometimes you have to live with what you got. I can't throw up my hands at my customer, say you've got an idiot DBA, fix this or I refuse to work with it. I wouldn't be employed very long.
I posted my environment in the previously mentioned threads, got the advice to use synonyms from Steve Jones and Jeff Moden, two MVP's. I implemented their advice and got on with it. Until SSDT SQL Database projects choked on this structure.
I'm going to have to refactor my code anyway since the new server environment will change things, but AFAIK it will just be connection definitions. I'll still use synonyms instead of three level table references, and SSDT will still choke on this even without "dev" or "prod" in the database names. So I think you're raising issues that aren't germane to the problem and just obfuscate the real issue with SSDT.
Thanks for your input on this thread. Much appreciated. Perhaps this may help someone who finds this thread in the future.
August 1, 2018 at 6:23 am
This is an interesting thread and I appreciate your well-thought-out comments, which have certainly made me think.
There is one part of your response which I'd like to take a little further, and that is the part about deploying databases which reference each other to a new instance. I built a very simple test script to check this out, as follows:
CREATE DATABASE db1;
GO
CREATE DATABASE db2;
GO
USE db1;
GO
CREATE TABLE t1
(
id INT
);
USE db2;
GO
CREATE TABLE t1
(
id INT
);
GO
CREATE VIEW v1
AS
SELECT id
FROM db1.dbo.t1;
GO
USE db1;
GO
CREATE VIEW v1
AS
SELECT id
FROM db2.dbo.t1;
GO
After running the above, you will have databases db1 and db2, each containing a view v1 which references table t1 in the 'other' database. By your definition, this is not a circular reference.
If you now use SSMS to script one of these databases and run that script on a clean instance, you'll get an error:
Msg 208, Level 16, State 1, Procedure v1, Line 5 [Batch Start Line 106]
Invalid object name 'db2.dbo.t1'.
There is no need to use array Jeff Moden and Steve Jones against me. I'm not arguing against their advice.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 6, 2018 at 1:22 am
There is no need to use array Jeff Moden and Steve Jones against me. I'm not arguing against their advice.
I'm not using their advice against you. Rather, I'm using their advice to support and defend my approach, since I'm an inexperienced SQL Server developer/user (2.5 years or so).
This is closer to my actual DW, although of course nowhere near the exact DW. Remember, the goal is to not use 3-level object references in the T-SQL:
SET NOCOUNT ON
USE master;
GO
IF DB_ID('db2') IS NOT NULL BEGIN
ALTER DATABASE [db2] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE db2;
END
IF DB_ID('db1') IS NOT NULL BEGIN
ALTER DATABASE [db1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE db1;
END
GO
CREATE DATABASE db1;
GO
CREATE DATABASE db2;
GO
USE db1;
GO
CREATE SCHEMA [d2] -- NOT d__b__2
GO
CREATE SCHEMA [tmp]
GO
CREATE TABLE tmp.t1
(
id INT
);
DECLARE @id INT = 1
WHILE @id <= 10 BEGIN
INSERT tmp.t1 SELECT @id
SET @id+=2
END
USE db2;
GO
CREATE SCHEMA [d1] -- NOT d__b__1
GO
CREATE SCHEMA [tmp]
GO
CREATE TABLE tmp.t2
(
id INT
);
GO
DECLARE @id INT = 2
WHILE @id <= 10 BEGIN
INSERT tmp.t2 SELECT @id
SET @id+=2
END
CREATE SYNONYM [d1].[t1] FOR [db1].[tmp].[t1]
GO
CREATE VIEW v1 AS
SELECT ID FROM d1.t1
GO
USE db1
GO
CREATE SYNONYM [d2].[t2] FOR [db2].[tmp].[t2]
GO
CREATE VIEW v2 AS
SELECT ID FROM d2.t2
GO
USE master;
GO
If I submit this code in SSMS 17.x against SQL Server 2014 Developer, it runs fine.
Now launch SSDT:
------ Build started: Project: db1, Configuration: Debug Any CPU ------
C:\Program Files (x86)\Microsoft Visual Studio\2017\SQL\MSBuild\15.0\Bin\Roslyn\csc.exe /noconfig /nowarn:1701,1702,2008 /nostdlib+ /errorreport:prompt /warn:4 /define:DEBUG;TRACE /errorendlocation /preferreduilang:en-US /highentropyva+ /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.6.1\mscorlib.dll" /debug+ /debug:full /optimize- /out:obj\Debug\db1.dll /ruleset:"C:\Program Files (x86)\Microsoft Visual Studio\2017\SQL\Team Tools\Static Analysis Tools\\Rule Sets\MinimumRecommendedRules.ruleset" /subsystemversion:6.00 /target:library /warnaserror- /utf8output "C:\Users\MyUserid\AppData\Local\Temp\.NETFramework,Version=v4.6.1.SqlClrAttributes.cs"
Loading project references...
Loading project files...
Building the project model and resolving object interdependencies...
Validating the project model...
C:\Users\MyUserid\source\repos\DELETEME3\db1\d2\Synonyms\t2.sql(1,30): Warning: SQL71562: Synonym: [d2].[t2] has an unresolved reference to object [db2].[tmp].[t2].
C:\Users\MyUserid\source\repos\DELETEME3\db1\dbo\Views\v2.sql(3,16): Error: SQL71501: View: [dbo].[v2] has an unresolved reference to object [d2].[t2].
C:\Users\MyUserid\source\repos\DELETEME3\db1\dbo\Views\v2.sql(3,8): Error: SQL71501: View: [dbo].[v2] has an unresolved reference to object [d2].[t2].[ID].
C:\Users\MyUserid\source\repos\DELETEME3\db1\dbo\Views\v2.sql(3,8): Error: SQL71501: Computed Column: [dbo].[v2].[ID] has an unresolved reference to object [d2].[t2].[ID].
Done building project "db1.sqlproj" -- FAILED.
Build FAILED.
------ Build started: Project: db2, Configuration: Debug Any CPU ------
C:\Program Files (x86)\Microsoft Visual Studio\2017\SQL\MSBuild\15.0\Bin\Roslyn\csc.exe /noconfig /nowarn:1701,1702,2008 /nostdlib+ /errorreport:prompt /warn:4 /define:DEBUG;TRACE /errorendlocation /preferreduilang:en-US /highentropyva+ /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5\mscorlib.dll" /debug+ /debug:full /optimize- /out:obj\Debug\db2.dll /ruleset:"C:\Program Files (x86)\Microsoft Visual Studio\2017\SQL\Team Tools\Static Analysis Tools\\Rule Sets\MinimumRecommendedRules.ruleset" /subsystemversion:6.00 /target:library /warnaserror- /utf8output "C:\Users\MyUserid\AppData\Local\Temp\.NETFramework,Version=v4.5.SqlClrAttributes.cs"
Loading project references...
Loading project files...
Building the project model and resolving object interdependencies...
Validating the project model...
Writing model to C:\Users\MyUserid\source\repos\DELETEME3\db2\obj\Debug\Model.xml...
db2 -> C:\Users\MyUserid\source\repos\DELETEME3\db2\bin\Debug\db2.dll
db2 -> C:\Users\MyUserid\source\repos\DELETEME3\db2\bin\Debug\db2.dacpac
========== Build: 1 succeeded or up-to-date, 1 failed, 0 skipped ==========
A little grumpy...I'd like the import to "just work". But after Googling, I know to add a database reference to db2 in the db1 project.
I then edited the db1 synonym:
CREATE SYNONYM [d2].[t2] FOR [$(db2)].[tmp].[t2];
Trying again:
------ Build started: Project: db2, Configuration: Debug Any CPU ------
db2 -> C:\Users\MyUserid\source\repos\DELETEME3\db2\bin\Debug\db2.dll
db2 -> C:\Users\MyUserid\source\repos\DELETEME3\db2\bin\Debug\db2.dacpac
------ Build started: Project: db1, Configuration: Debug Any CPU ------
Loading project references...
Loading project files...
Building the project model and resolving object interdependencies...
Validating the project model...
C:\Users\MyUserid\source\repos\DELETEME3\db1\d2\Synonyms\t2.sql(1,30): Warning: SQL71562: Synonym: [d2].[t2] has an unresolved reference to object [$(db2)].[tmp].[t2].
C:\Users\MyUserid\source\repos\DELETEME3\db1\dbo\Views\v2.sql(3,16): Error: SQL71501: View: [dbo].[v2] has an unresolved reference to object [d2].[t2].
C:\Users\MyUserid\source\repos\DELETEME3\db1\dbo\Views\v2.sql(3,8): Error: SQL71501: View: [dbo].[v2] has an unresolved reference to object [d2].[t2].[ID].
C:\Users\MyUserid\source\repos\DELETEME3\db1\dbo\Views\v2.sql(3,8): Error: SQL71501: Computed Column: [dbo].[v2].[ID] has an unresolved reference to object [d2].[t2].[ID].
Done building project "db1.sqlproj" -- FAILED.
Build FAILED.
========== Build: 1 succeeded or up-to-date, 1 failed, 0 skipped ==========
Finally, I imported the db2 database, since the object the db1 synonym references hasn't been imported yet
------ Build started: Project: db2, Configuration: Debug Any CPU ------
Loading project references...
Loading project files...
Building the project model and resolving object interdependencies...
Validating the project model...
C:\Users\MyUserid\source\repos\DELETEME3\db2\d1\Synonyms\t1.sql(1,30): Warning: SQL71562: Synonym: [d1].[t1] has an unresolved reference to object [db1].[tmp].[t1].
C:\Users\MyUserid\source\repos\DELETEME3\db2\dbo\Views\v1.sql(3,16): Error: SQL71501: View: [dbo].[v1] has an unresolved reference to object [d1].[t1].
C:\Users\MyUserid\source\repos\DELETEME3\db2\dbo\Views\v1.sql(3,8): Error: SQL71501: View: [dbo].[v1] has an unresolved reference to object [d1].[t1].[ID].
C:\Users\MyUserid\source\repos\DELETEME3\db2\dbo\Views\v1.sql(3,8): Error: SQL71501: Computed Column: [dbo].[v1].[ID] has an unresolved reference to object [d1].[t1].[ID].
Done building project "db2.sqlproj" -- FAILED.
Build FAILED.
------ Build started: Project: db1, Configuration: Debug Any CPU ------
C:\Program Files (x86)\Microsoft Visual Studio\2017\SQL\MSBuild\15.0\Bin\Roslyn\csc.exe /noconfig /nowarn:1701,1702,2008 /nostdlib+ /errorreport:prompt /warn:4 /define:DEBUG;TRACE /errorendlocation /preferreduilang:en-US /highentropyva+ /reference:C:\Users\MyUserid\source\repos\DELETEME3\db2\bin\Debug\db2.dll /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.6.1\mscorlib.dll" /debug+ /debug:full /optimize- /out:obj\Debug\db1.dll /ruleset:"C:\Program Files (x86)\Microsoft Visual Studio\2017\SQL\Team Tools\Static Analysis Tools\\Rule Sets\MinimumRecommendedRules.ruleset" /subsystemversion:6.00 /target:library /warnaserror- /utf8output "C:\Users\MyUserid\AppData\Local\Temp\.NETFramework,Version=v4.6.1.SqlClrAttributes.cs"
Loading project references...
Loading project files...
Building the project model and resolving object interdependencies...
Validating the project model...
C:\Users\MyUserid\source\repos\DELETEME3\db1\d2\Synonyms\t2.sql(1,30): Warning: SQL71562: Synonym: [d2].[t2] has an unresolved reference to object [$(db2)].[tmp].[t2].
C:\Users\MyUserid\source\repos\DELETEME3\db1\dbo\Views\v2.sql(3,16): Error: SQL71501: View: [dbo].[v2] has an unresolved reference to object [d2].[t2].
C:\Users\MyUserid\source\repos\DELETEME3\db1\dbo\Views\v2.sql(3,8): Error: SQL71501: View: [dbo].[v2] has an unresolved reference to object [d2].[t2].[ID].
C:\Users\MyUserid\source\repos\DELETEME3\db1\dbo\Views\v2.sql(3,8): Error: SQL71501: Computed Column: [dbo].[v2].[ID] has an unresolved reference to object [d2].[t2].[ID].
Done building project "db1.sqlproj" -- FAILED.
Build FAILED.
========== Build: 0 succeeded or up-to-date, 2 failed, 0 skipped ==========
And if I try to add a reference to db1 in the db2 project, I get the circular reference error message. In fact, I get this error message even if I just have two empty SQL Server Database projects.
Nothing is intrinsically wrong with the database design. But, I can't use SQL Server Database projects to manage this database design.
Hopefully Microsoft will fix this software in a future release. I've added a bug report per link in my previous post, but crickets from Microsoft.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply