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!
Hi Peter, with interest I read your article.
ReplyDeletequote: "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?
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.
DeleteHow make a shared folder ? dacpac is inside debug folder.. and could be changed any time.
ReplyDeleteI'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.
DeleteThat "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.
are you change the "Build output path" to the Shared folder ?
DeleteNo, 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.
DeleteHi Peter,
DeleteJust 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?
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.
DeletePeter, 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.
ReplyDeleteThanks, Scott
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.
DeleteHi Peter,
ReplyDeletemany 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,
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. :)
DeleteThank 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
DeleteCheers,
Chris
I am using DBName.DBO.TableName in SSDT. It throws an error "sql71561 has an unresolved reference to object" Any Solution for this? Thanks.
ReplyDeleteI'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.
DeleteI 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
DeleteSame 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.
DeleteHi Peter,
ReplyDeleteLets 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 ??
What we do in these cases is we create a "Shared Schemas" type folder so our structure is something like:
DeleteDB 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.
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?
ReplyDeleteAll 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.
DeleteThat 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/
Hello Peter,
ReplyDeleteI 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".
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.
DeleteWhen 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.
Hello Peter,
ReplyDeleteI 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.
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.
DeleteMost 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.
Hello Peter,
ReplyDeleteCan 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.
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.
DeleteHello Peter,
ReplyDeleteIssue:-
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,
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.
DeleteYou could look at including the necessary dacpacs in your project and referencing those directly. VSO should be able to see them at that point.
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?
ReplyDeleteI 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.
DeleteIt'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.
How can i reference a legacy databse that has no dacpac that is used in some stored procedures?
ReplyDeleteYou'll need to create a dacpac for that database - either create a local DB and get a dacpac from that or capture one from the existing using sqlpackage. I have a post on using the sqlpackage command line for the databases that don't extract into a project well or that don't extract through the wizard.
DeleteWhat about external references to two databases on different servers but having the same name?
ReplyDeleteSSDT gives a "not unique" error with yellow triangles over both dacpac references and again, wont build
Good question. I'd consider posting this on stackexchange to see if anyone has any other ideas. Sounds like a case of "Different Server, Different Database" for the reference - maybe using parameters for the names. You might also consider two differently named dacpacs for your references - even if they're otherwise identical.
DeleteI want to use SSDT to manage the custom DB objects I have deployed to a commercial application's DB.
ReplyDeleteI've extracted a DACPAC of the app DB (without my customizations) and referenced it with a database location of "same database".
I can successfully build my project, and a very small (~15kB) DACPAC drops out. However, when I attempt to deploy it, SqlPackage.exe generates a script to sync up the *entire* target DB (using my objects plus the extracted DACPAC), rather than just updating my custom objects. I can't seem to find a way to restrict it to just the objects that I am in control of.
I could try to set up our deployment to grab a fresh DACPAC so that it doesn't try to mess with users, role memberships, permissions, etc, but that would cause "model already contains an object with the given name" errors. It also keeps generating "drop index" and "drop constraint" statements, even though my publish.xml file has both "DropConstraintsNotInSource" and "DropIndexesNotInSource" set to "False".
Any ideas?
Have you tried forcing those options in the sqlpackage command line? It sounds like the publish profile isn't being picked up or something. You should also be able to force options to ignore users/role members/permissions/etc.
DeleteI see why it was dropping indexes & constraints...My code interacts with a very small number of objects in the App DB, so I rarely update the DACPAC reference. Apparently there was a schema change (in one of the thousands of tables that my code doesn't care about). SqlPackage was dropping constraints & indexes so it could do a table conversion, reverting that table back to its state in my stale DACPAC reference:(
DeleteI also tried changing from "same database" to "same server, different database", and then updating to 3 part references in all of my objects: [$(AppDBName)].SchemaName.ObjectName.
The project built successfully, but when deploying, SqlPackage again tried to sync up the entire DB, based on my DACPAC reference.
I'm beginning to think this use case is not something that is supported. I suppose the "same database" functionality is for cases where you've got some common/utility code in a separate database project.
I was hoping there was some kind of a "limit deployment operations to the objects in this project" setting...
Hmm...looks like it behaves the way I want if I uncheck the "Include composite objects" box in the Advanced Publish Settings.
DeleteIn hind sight, I should have known that.
But thank you for the prompt feedback!
Hello Peter,
ReplyDeleteNot sure if you still respond to this, and thank you for taking the time to write up this article. I'm running into an annoying problem with a database reference to another server I cannot seem to solve.
I've received a DACPAC file of a large database (over 5000 tables, DACPAC size if 7+ mb). I can add the file as a database reference successfully, setting both a database and server variable. However, the Visual Studio project file isn't updated and when I close the solution and re-open it, the database reference is gone.
When I try the exact same steps with another DACPAC from a much smaller database everything behaves as expected. Is there perhaps a limit on the DACPAC size? Or am I missing something else?
I really hope you can help me out as the success of our project depends on being able to reference this other database.
Thanks.
I still check this, though copied everything over to my main blog page (except the older comments, sadly). The file size for the dacpac shouldn't matter, though that could give you fits in overall performance. I can't say for sure why the dbproj file isn't getting updated appropriately. As a last resort, you could close your project, open that *.dbproj file in a text editor, and manually add the lines for the dacpac, but I haven't seen an issue with that elsewhere.
DeleteWhat I might recommend is perhaps creating a smaller version of that dacpac. It's unlikely you're referencing every object in that database so perhaps creating a sub-set of that database only containing the objects you need to reference might help? There are ways to do this with a text editor, but you're likely better off creating a project with just the objects you need and building a fresh dacpac. I think I have an article about customizing your master.dacpac reference here that might give you some ideas if you're going to tailor that file, but that's going to be a _lot_ of text to go through.
So - I might try adding that smaller dacpac first, save the project/solution, close it, and try editing it to point to the large file. Then re-open. If that doesn't work, I might try making a subset of needed tables if that's possible (or perhaps culling out stuff like stored procs that you don't reference or similar).
Hi Peter, thanks so much for the immediate response (and apologies for the double submission of my questions. Had some network issues).
DeleteI may have found another workaround. I added the large DACPAC reference, and then added the small test DACPAC file to see how the XML lines were added to the project file, in the hope I could modify it for my large DACPAC. To my surprise once the small one was registered, both the small and the large DACPAC showed up in the project file. I have now removed the small database reference and corresponding SQLCMD variables, and my big database reference is still there.
Let's hope it's a glitch and that from now on everything works as expected.
Thanks again for the help!
Glad it's working now. I still suggest seeing if there's a way to trim that file down to make publish/build actions more pleasant. Those large files can make the publish actions take some time, though still faster than the prior "VS DB Project" builds. :)
Delete