Connecting to multiple database

Hi, i'm a novice in all this database transactions stuff so please bear with me if my input is incorrect or out dated. I'm running into the problem of needing to compare or manipulate data that is in 2 tables in 2 separate database. Is there any way i can set up the connection string such that i can access data/tables from both database at the same time in 1 sql command (similar to accessing 2 tables of the same database in 1 sql command)?

Is this currently possible? If not, is it because the execution of sql command must be tied to only one connection string at each instance? Would be great if the wonderful folks at MICROSOFT can look into this and provide us with an upgrade to cater for such a need...

Regards

Oceandude

If the database are within the same server you can several methods, all not doing it in one command (as this not possible) but within transactions:

In a SQL Batch

BEGIN TRANSACTION

UPDATE SomeOwner.SOMETable SET SomeCol = 1

UPDATE SomeDatabase.SomeOwner.SOMETable SET SomeCol = 1

COMMIT

You can use a transaction scope to elevate the transaction to a distributed (crossdatabase transaction), but that depends on the coding language you are using the Transaction scope would be a sample for .NET.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||You can also create a synonym (if your server is SQL 2005) in one database for a table in the other.
See CREATE SYNONYM.|||

What Is your goal? You could open multiple SQL Commands or you could do any of the above suggestions, or your could define a stored process on the server that does the bulk of the work. There are many options depending on what you really want to do here.