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

sql-server, stored-procedures

Solution

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

Problem

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?

Original source