SSDT: External Database References

If your database references other databases, you’ll need a *.dacpac file for each external database referenced in your code. For example, if you reference AdventureWorksDW2008 from Adventureworks2008, you’ll need to add that as a database reference.
 
To add a Database Reference to your project

  • Right-click the “References” folder in your project or choose the “Project” menu and select the option to “Add Database Reference”

clip_image001

  • Browse for your dacpac file
  • Choose the appropriate database location. (Same, Different DB / Same Server, or Different DB / Different Server)
  • If your database name doesn’t change, clear out the “Database Variable” column. It isn’t immediately obvious, but doing this will let you keep referencing your database in the same manner you do currently.
    • If this database name can vary, populate the Database Variable field. You’ll need to replace this reference in your code with the variable from this column.
    • If you’re referencing a database on a linked server, this would probably be better served with a static database name and a “Server Variable”. Make sure that server variable is set appropriately when you publish the database.
    • This should be addressed with a future release of SSDT.
  • You may choose to suppress errors if there are unresolved references in the project. This is useful if your dacpac file could be out of date. You can still code against an object you know to exist, but isn’t in the reference file yet.
  • If you use Linked Servers, you’ll probably want to use the Server Variable to ensure that you can release your code in different environments.

clip_image002

 
Some Best Practices and Lessons Learned

  • Make a shared folder to store your dacpac files in a common place. This should be easily accessible from all of your SQL Projects.
    • If you use C:\DatabaseProjects as your base for all SQL Server projects, create a folder in there to store your shared dacpac files.
  • Because all paths are relative to the project, you may need to copy the master/msdb dacpac files that ship with SSDT into your shared folder. You can find these files in your Visual Studio folder in a path similar to the following.

Drive:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\VersionNumber\SqlSchemas

    • If you support multiple SQL Server editions, you may want to rename master/msdb files with their user-friendly version number. E.g., the master.dacpac file in your “90” folder could be renamed to master2005.dacpac
    • This should be addressed in a future release of SSDT and could already be fixed.

  • If you use variables, don’t forget to replace any TSQL with the variable in the form of [$(VariableName)].
    • Square braces may not be necessary, but in most cases, they’ll work as expected for DB or Server names.
  • If you need to reference a dacpac that is already in use, you’ll need to create a copy of it. This comes up more often if you’re referencing a replicated database for which you already have a production dacpac.
  • Choose good variable names for the Server variable name.
  • Don’t forget to set the variables in the Project and/or Publication properties!
Comments
  1. Anonymous

    Hi Peter, with interest I read your article.

    quote: “If you support multiple SQL Server editions, you may want to rename master/msdb files with their user-friendly version number. E.g., the master.dacpac file in your “90” folder could be renamed to master2005.dacpac
    This should be addressed in a future release of SSDT and could already be fixed.”

    We did so, but this raises new problems. Do you have any suggestions how to support multiple .dacpac files for multiple target server editions at the same time?

  2. Peter Schott

    I'd suggest using the lowest common denominator for your master and msdb databases. That way you know that whatever you code, your project will work for that version. If you're developing the same database to take advantage of different SQL Server versions, that's a different concern. I'm not sure about the best way to handle that.

  3. Anonymous

    How make a shared folder ? dacpac is inside debug folder.. and could be changed any time.

  4. Peter Schott

    I've always just created one at the same level as all of the project folders. So if I have folders/SLNs for DB1, DB2, DB3, I'd put the “Shared” folder at that same level. The reference then points to something like ..\..\Shared\DB1.dacpac.

    That “Shared” folder can then be checked in to source control and you don't have to worry about conflicting or non-existent local debug/bin folders.

  5. Anonymous

    are you change the “Build output path” to the Shared folder ?

  6. Peter Schott

    No, I copy the files over there manually as needed. Most of the changes that we make do not affect the cross-database dependencies. As we have multiple stories going on at multiple times, I was trying to minimize the possible disruptions due to anything in that shared folder changing too often. You could likely tweak the build output path or create some other process to copy the dacpac to that shared folder on a regular basis, though.

  7. Scott Councill

    Peter, thanks for the blog, this was helpful. I am trying to build a dacpac in 2008 R2 and I have several stored procedures that reference linked servers. Those SPs are being eliminated due to invalid references. Is there a way to get around this? I want to add the dacpac files to my source control and then use them as a reference for my database projects.

    Thanks, Scott

  8. Peter Schott

    It's definitely possible. You will need to add the database reference to your linked server database as a database on another server. If that server name can change, you'll also want to make it a variable. When you reference that in your stored proc, it will either need to be a variable or the actual name of the server. That should be the “Different Database, Different Server” option to add the linked server database reference. Only populate the “Server Variable” field if that could be a different server name (in different environments or for different customers) to make the coding a bit easier. You'll want that server name or variable value to match the linked server name on your target server. Once that's set properly, it should work without any issues.

  9. Anonymous

    Hi Peter,
    many thanks for your SSDT blog, really helped me understand the concepts.
    I've been trying to find some information on adding an external reference to a linked server that is linked to an excel data source.
    my stored procedure may look something like
    select *
    from openquery ([Excel_Linked_Server_Object],
    'select * from [WorksheetName$A4:BA]'

    Of course this leads to an unresolved reference in my sql project.. but I have not been able to find any information on generating a .dacpac for a pass-through query to an OLEDB data source.
    have you come across this issue?

    Thank you,

  10. Peter Schott

    I haven't come across that in any of the work I've done, but you should be able to script these in Post-Deploy scripts if all else fails. The syntax checking is less strict for those scripts and you could tweak the settings per environment with some variable checking. In my experience, you can't add a linked server to anything outside of another SQL Server inside the default SSDT project format. It might be possible to enhance that, but until then using a post-deploy script is probably your best bet. As always, make sure you check for existence of your servers, objects, etc. before trying to create them and handle the DROP/ALTER/CREATE statements appropriately. 🙂

  11. Anonymous

    Thank you, I’ve since had a few chances to play with post-deploy scripts and they are pretty useful, I used them to script and schedule some jobs following the deployment. With the excel linked server issue, this is a practice I’ve inherited from the original setup and I’ve since questioned the need to maintain that… For the time being, I just excluded all my linked server calls from the build to suppress the errors but I’m looking into replacing the excel link with a managed SSIS package which I assume I should be able to make part of my SQL project so this is what I will be reading through next 
    Cheers,
    Chris

  12. Yogesh Shinde

    I am using DBName.DBO.TableName in SSDT. It throws an error “sql71561 has an unresolved reference to object” Any Solution for this? Thanks.

  13. Peter Schott

    I've been removing the DBName. portion of the object name in the code. It will work within the current database and any outside references need some sort of DB Reference. I've tried a couple of other ways to handle this, but it seems that if I get the 3-part naming working for the current DB, other things have broken for me. It's easy enough to search within the current project, *.sql files only, and replace “DBName.dbo.” with just “dbo.”. Once that's done once you don't have to do too much else to keep it up.

  14. JaY

    Hi Peter,
    Lets say I created a Database Project using Visual Studio and I gave reference for Databases in project using DAC packages. After couple weeks may be some table structure or DB architecture will get change!! Then how these changes gonna affect that Database Project I created !! It will fail right ??

  15. Peter Schott

    What we do in these cases is we create a “Shared Schemas” type folder so our structure is something like:
    DB Projects
    – DBProject1
    – DBProject2
    – SharedSchemas

    Anything that needs to be referenced across multiple databases goes in that “Shared” folder. Obviously if we add something to one DB Project that the other needs, we'll need to update the dacpac file there. We just take the output of a build from that updated project and copy it over the one in the shared folder. The update is then done and the other DBs now reference the changed dacpac.

  16. shaunt

    Great article. Do you use the localdb option for debugging with these cross database builds? I've had trouble trying to figure out how to get that working. Particularly because I don't want to have all my databases in a single solution. We are trying to get all this running with continuous integration as well. Send like having a lot of two wsy dependencies gets tricky. Any recommendations?

  17. Peter Schott

    All I can say is the way we do it. I publish the changes to my local SQL Server (Developer Edition) and check things against that. Our devs do that as well. From there we go to shared environments, QA, Staging, and ultimately Production. Each of those is identical as far as having the same service packs and databases. The logins may differ slightly and which version/branch of the database may also differ, but the cross-dependencies and such are all the same.

    That said, we use separate solutions – one for each DB we maintain. I'll admit this was mostly because trying to get one solution for all databases working with the older VS DB Projects was painful. However, the DB References in a shared folder helped quite a bit. We build/replace those as we make major schema mods and need to make sure the other DBs see those changes.

    For CI, we have build jobs set up in Jenkins. We can trigger a build quickly, then push that build out to multiple environments. Usually this is a branch for our Dev/QA environments and the mainline build for production. I wrote a series of batch files for our devs to use to keep their local boxes up to date more easily.

    I'll also recommend Jamie Thomson's blog – he discusses DB/SQL Projects quite a bit along his journey. You can find him at http://sqlblog.com/blogs/jamie_thomson/

  18. Anonymous

    I have 1 more question. What if 2 DB(A and B, A has database reference to B) have tables with same names, for example CityTable? How VS understand which one i wanna use when i write DBO.CityTable – from A db or B db? Sorry for my poor english

  19. Peter Schott

    Same way it normally would, I imagine. For the “same” database, you'd reference just “dbo.CityTable”. For the external DB, you'd use “OtherDB.dbo.CityTable”. I haven't found a good way to use 3-part naming for objects in the current database. I generally get errors/warnings when I try to do that so just reference everything in the current database with just schema.object notation.

  20. Anonymous

    Hello Peter,

    I have a query in regards to removing the DBName. portion of the object name in the code” to get rid of an error :- “View has an unresolved reference to object”.

    As a part of Importing the entire DB into Visual Studio 2013, I see an error in regards to Stored Procedure but then in the code no were it is mentioned the DB.Name. Its basically a Job related proc and the error I see is “Procedure has an unresolved reference to object”.

  21. Peter Schott

    Without seeing the project, it's hard to say exactly what could be missing. Unresolved Reference errors typically mean that exactly what they say – something the proc expects to find is missing. If this is a job-related stored proc, it could be a reference to the master or msdb databases. You can add those pretty easily within SSDT as I think they're built-in, though be careful if you have custom objects in your master database.

    When you look at the line(s) referenced in your errors and warnings for the views/procs, what do you see on those lines? What are they trying to do. That can help track down the issues.

  22. Anonymous

    Hello Peter,

    I have followed you instructs and have come to an error when add the DB reference and selecting the dacpac file.
    Error is:

    Project '' contains invalid database reference: 'c:\…'. You must update the reference so that it identifies a database project whose version of SQL Server matches the version on which your referencing database project is based.

    Any help with be greatly appreciated.

  23. Peter Schott

    Sounds like you may have the version of your project set to one version of SQL Server (e.g., SQL 2012) but the dacpac you're referencing is set to another (e.g., SQL 2008). You'll either need to change the version in the current project or tweak the dacpac files to be the same version. If you're referencing the system files, you might need to delete and re-import those. SSDT ships with several versions of the master/msdb dacpacs but if you change your DB version for the project I don't know if it's smart enough to catch that and adjust accordingly.

    Most often – you need to adjust the version of your reference project – edit its properties and rebuild to get a new dacpac with the appropriate version or unzip, edit the metadata, and re-compress/re-add.

  24. Anonymous

    Hello Peter,
    Can we edit the path given as reference?
    I tried it but the path is disabled.
    I wanted to give a new path as the path is not accessible due to server issues.

  25. Peter Schott

    I've found that you either need to edit the sqlproj file when it's offline to point to the new path or remove and re-add the dacpac used for the reference.

  26. Anonymous

    Hello Peter,

    Issue:-
    There is one Mart1.sln file which has database reference to Mart.dacpac file which is placed at below location
    \\server\Reference\Mart.dacpac
    I could try build in by visual studio ,it got succeded.
    but when I try build in VSO it throws the below error
    C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v14.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets (550, 5)File “\\server\Reference\Mart.dacpac” does not exist

    Is it something like VSO cannot have access to the server?
    or is there any path/folder to keep the dacpac files in VSO?
    Or kindly suggest a solution

    I also tried changing the dacpac file location path to my local ,but the same error occurred.

    Thanks,

  27. Peter Schott

    Pretty sure that's lack of access to the server. You'd want a common location that could be accessed. I think our reference was something like “..\..\shared\Mart.dacpac” but whatever you use it needs to be something that can be found no matter what you use. If you're going to use VSO, you'll need some way to reference the dacpacs so that VSO can see them. Using “\\server\share\filename.dacpac” won't work because VSO won't be able to see your local servers.

    You could look at including the necessary dacpacs in your project and referencing those directly. VSO should be able to see them at that point.

  28. Gertman

    Hi Peter,
    Just wondered how you prefer setting up Your environment With solutions/Projects for databases: Do you usually have one solution for all Your DBs (one solution/many Projects), or would you recommend one solution per DB-Project?

  29. Peter Schott

    It depends. 🙂 For me, I used one project/solution per database. Others have used one solution for all databases. It really will depend on your configuration. I liked working with different DBs, if only because I wasn't trying to build and check in everything when I only had one or two databases change. I normally have quite a few DBs that are more static so keeping them separate in both solutions and source control has made more sense.

  30. Bredolab

    Hi Peter. What you suggest for distributed partitioned views with SSDT? Is is manageable? How to create clean references to external instances of such DB without compilations errors and with ability to publish project later when u update schema with other distributed objects?

  31. Peter Schott

    I haven't had to use distributed partitioned views myself, but I would imagine it's doable. You'd really want all of your environments to mimic that setup in some way or you could have issues. I think you'd set up the external DB references (Different database, different server), use variables to populate the appropriate DB/Server names, and go from there. If the table structure doesn't change that often, I'd use the one that's in a shared location and just update that as needed.

    It's possible that Ed Elliott at https://the.agilesql.club/ might have some other tricks. You could also look at composite projects where the distributed view is taken out and put into sub-projects where it's either local or distributed, depending on what's needed. You could also build/alter it in a post-deploy script. There are quite a few options, but I'll admit this is not something I've personally done.

Leave a Reply

Your email address will not be published. Required fields are marked *