Cross-Database Queries

a11smiles

In production, I have a database with a number of stored procs that reference other databases.  

When I try to create a new VS project and establish a baseline, those sprocs won't get created because the other databases don't exist locally.  I get the "Invalid Object Name" error.

How do i create a baseline without having to create these databases (which include a huge data warehouse) on my localdb instance?

Kendra_Little

Hello,

Thanks for your question.

For stored procedures, they should all import/baseline successfully, but they will print a warning. This is because SQL Server has a feature called "deferred name resolution" for stored procedures: since stored procedures can create a table and then reference it in the same procedure, they don't require that objects exist when the stored procedure is created. Instead, name resolution in deferred until the first time the stored procedure is executed.

Views and some functions are a different story, however. If a view references a table, SQL Server requires validation that the dependency exists, and if it can't find it (in the current database or another), it will give that error.

Apologies if this comes off as pedantic! I know it may sound that way.

I mention it only because it may mean that the scope of your issue is smaller than it might seem at first. Although the scope really will depend on how many views you have which have the cross-db references.

To get going, there are a few approaches you can take. I'm putting the simplest first. The final two options are more work but are great longer term.

1) If it turns out to be just a couple of views/functions and it's not important for you to have them in version control,  you may simply filter them out and proceed without them.

2) You can create a schema-only copy of the dependent databases on your localdb instance -- potentially  using SQL Compare to generate the script and deploy it. No data need be in dependent databases, SQL Server only needs to validate the schema.

3) You can restore databases or use clones, as described in this article. For the large data warehouse, you'd want something like our SQL Clone tool, an on-demand snapshot using SAN technology, or similar other data virtualization solution to make it fast -- OR if you have a scaled down copy of the DW which is used for development  purposes, that could work, too. While this approach may seem like overkill, the reason it's wonderful in the long run is that you can immediately identify if indexes exist in the dependent database to support your queries, and if you've got a regular process to refresh the clones of dependent databases then you find out if there have been schema changes in them which impact you (even if that that depdendent database is not in version control or is managed by a team which is totally different than yours).

4) If you are also going to be adding the dependent databases into version control in the same solution, this article describe a "stubbing" approach to solving it.

Hope this info helps!

Kendra

Edited to fix my weird formatting problem, then edited a second time to fix a bad link