Wednesday, October 31, 2012

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"
  • 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.
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!


  1. 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?

    1. 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.

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

    1. 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.

    2. are you change the "Build output path" to the Shared folder ?

    3. 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.

  3. 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

    1. 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.

  4. 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,

    1. 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. :)

    2. 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 

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

    1. 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.

    2. 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

    3. 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.

  6. 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 ??

    1. 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.

  7. 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?

    1. 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

  8. 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".

    1. 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.

  9. 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.

    1. 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.