April 7, 2020 at 1:28 pm
Hi, I need to include a very simple schema in all of my database projects. It will have 3-4 tables to log some special events. I want to include it on my current projects, but keep a single definition.
Is there a simple way to obtain that? I know I can reference a datpac or another project , but I do not want the schema to be considered "outside" each of the databases, I want it to be part of them.
April 7, 2020 at 1:36 pm
Can you not just include a script to create the tables? Alternatively, you could put the tables in the model database, so long as you don't create any other databases on the instance.
John
April 7, 2020 at 1:48 pm
I can't think of a way of doing this which allows the schema objects to be both part of every database project and maintainable from a single place.
I don't think that John's suggested ideas work very well, from a source-control point of view.
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
April 7, 2020 at 2:34 pm
We will have several database on the same instance, all of them should have the same set of tables, and there is code that references them (triggers on different schemas that insert into those "common" tables).
I can add them with a shared post deployment script, but the triggers in the project would fail since they won't be able to "see" the tables...
I have though of storing the tables in a separate database, common to all databases on the instance (and referenced on each database project), but the problem is that if any database needs to be moved to another instance/server then the audit data it generated will remain on the old common database that is the reason i want to have those tables as a schema on each database and not a shared database.
They are pretty simple objects, 3-4 tables with a PK, no FK, and a couple of indexes. but I have 20+ database where I need to add them, and I do not want to repeat the definition on the 20 of them.
April 7, 2020 at 2:52 pm
I can add them with a shared post deployment script, but the triggers in the project would fail since they won't be able to "see" the tables...
Not ideal, but could you also put the triggers in the PD script?
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
April 7, 2020 at 3:06 pm
No, they are more than 2000 triggers (1000+ tables, triggers for insert and delete that record some specific details when some specific conditions are met...)
April 7, 2020 at 3:27 pm
you can try the following.
create a folder where you put the create scripts for those tables
on the projects where you need those tables ensure you have the folders setup correctly for the schema where the tables will reside
then on VS go to that folder within each project, right click->Add Existing Item -> select on the bottom right dropdown - Add As Link
that should do it
April 7, 2020 at 3:37 pm
you can try the following.
create a folder where you put the create scripts for those tables
on the projects where you need those tables ensure you have the folders setup correctly for the schema where the tables will reside
then on VS go to that folder within each project, right click->Add Existing Item -> select on the bottom right dropdown - Add As Link
that should do it
If this works, it's almost perfect for your scenario; awesome! Never knew that was a possibility, and I've been using SSDT database projects for 10 years.
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
April 7, 2020 at 4:35 pm
frederico_fonseca wrote:you can try the following.
create a folder where you put the create scripts for those tables
on the projects where you need those tables ensure you have the folders setup correctly for the schema where the tables will reside
then on VS go to that folder within each project, right click->Add Existing Item -> select on the bottom right dropdown - Add As Link
that should do it
If this works, it's almost perfect for your scenario; awesome! Never knew that was a possibility, and I've been using SSDT database projects for 10 years.
it works great - schema compare with real db thinks the objects are there, code build includes them on scripts as if the file was on same folder as the others.
but it not a well know functionality.
Now if MS could fix bloody circular project references it would be great.
April 7, 2020 at 5:11 pm
Seems a good idea. I did not knew about this, but seems to be the behavior I need.
The only problem I see is that the stored path must be relative since this can be deloyed from different PCs, and will be stored on GIT. I will do some testing and let you all know!
April 7, 2020 at 5:21 pm
Now if MS could fix bloody circular project references it would be great.
Can you provide a practical example where circular (as defined by MS in this case) references are an example of good database design?
I've certainly had a good deal of trouble getting legacy DBs imported into DB projects, as a result of circular references, but in these cases the best solution was to re-architect the DBs because those references were bad design.
Apologies: I don't want to hijack the thread, but this is an interesting topic.
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
April 7, 2020 at 11:42 pm
Phil - it was combination of 3 db's for a migration project (insurance company).
main issue with MS design is that circular references should only be considered at object level, not project level, specially taking in consideration that in many cases the objects only need to be resolved at execution time.
April 8, 2020 at 1:51 pm
Thanks for taking the time to write that out.
While I'm sure it would have been helpful in this case to permit 'circular' references, I'm still not convinced that this is a sound design.
I'm happy to have one-way links (eg, to a Utilities database), but two-way links, where each DB depends on the other in some way, just feels wrong to me. I've always been able to avoid this, or engineer my way out of it. My ETL processes always head upstream only.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply