How can I get a stored procedure to call an outside database on the same SQL server?

Question

I am dealing with a web application that uses 2 different databases to store information. These databases have some database keys that refer to records in the other database. I would like to be able to write a stored procedure in SQL 2005 that can modify data in the current database as well as go out and modify some data in the other database. Is this possible? How?

Answer 1

You can fully qualify the table names (I'm assuming the databases are on the same db server)

e.g. from sproc in DB1:

UPDATE DB2.dbo.MyOtherTable
SET Field = 'SomeValue'
WHERE ID = 1

Answer 2

Since you are on the same SQL server, this a relatively straight forward task.

Everywhere you reference a table from the other database, you simple need to qualify it with that database name.

So, where you might have:

Update table1
Set col = 'val'
Where Id = @Id

You just have:

Update DatabaseName.dbo.table1
Set col = 'val'
Where Id = @Id

Note that the .dbo is the database schema your table exists within.

If you need to go to a different server, then you will need to set up a linked server.

Two additional things that you may find handy are:

Synonyms

Synonyms allow you to create aliases to the referenced table, using the code below:

CREATE SYNONYM [dbo].[TheOtherTable] FOR [OtherDataBase].[dbo].[OtherTable]

This allows you to simply use TheOtherTable in your procedures.

SQLCMD mode

As you promote your project from dev to testing to production, you may find that database names change in environments. This can quickly become a pain if you have multiple database references.

SQLCMD scripts allow you to specify variables that get replaces when the script runs. I've found this very handy in the past when dealing with procedures that reference tables in different databases.