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




13 comments:

  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?

    ReplyDelete
    Replies
    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.

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

    ReplyDelete
    Replies
    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.

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

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

      Delete
  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

    ReplyDelete
    Replies
    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.

      Delete
  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,

    ReplyDelete
    Replies
    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. :)

      Delete
    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 
      Cheers,
      Chris

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

    ReplyDelete
    Replies
    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.

      Delete