For example, if you reference AdventureWorksDW2008 from Adventureworks2008, you'll need to add that as a database reference. It isn't immediately obvious, but doing this will let you keep referencing your database in the same manner you do currently. You'll need to replace this reference in your code with the variable from this column. Make sure that server variable is set appropriately when you publish the database. 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. This should be easily accessible from all of your SQL Projects. You can find these files in your Visual Studio folder in a path similar to the following. Anonymous Hi Peter, with interest I read your article. Do you have any suggestions how to support multiple. 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. I've always just created one at the same level as all of the project folders. The reference then points to something like.. 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. 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. 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 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. 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. 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. 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. 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. Once that's done once you don't have to do too much else to keep it up. Same way it normally would, I imagine. I haven't found a good way to use 3-part naming for objects in the current database. 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?? 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. 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. 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. 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. For CI, we have build jobs set up in Jenkins. We can trigger a build quickly, then push that build out to multiple environments. I wrote a series of batch files for our devs to use to keep their local boxes up to date more easily. 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. 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. What are they trying to do. That can help track down the issues. Anonymous Hello Peter, I have followed you instructs and have come to an error when add the DB reference and selecting the dacpac file. 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. 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. Anonymous Hello Peter, Issue:- There is one Mart1. 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. If you're going to use VSO, you'll need some way to reference the dacpacs so that VSO can see them. 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. 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. 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. 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. There are quite a few options, but I'll admit this is not something I've personally done. You'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. I'd consider posting this on stackexchange to see if anyone has any other ideas. You might also consider two differently named dacpacs for your references - even if they're otherwise identical.
