Cross Database Queries

[RFH Reseller: InspireUK]

Is it possible to do a cross database query. Both db are on the same server. If it is can you give me an example.

Thanks.

Josh

[RFH Reseller: davidbending]

Yes you can. For example if you had Orders and Products tables in different databases (not sure why you'd do this) and both belonged to the dbo schema, you could do a query like this:

select * from [db1].[dbo].[Orders] o, [db2].[dbo].[Products]
where o.productId = p.ProductId

Basically you just need to fully qualify the names of your SQL objects.

A word of warning though -it's difficult to maintain referential integrity across databases so only do this if you have a good reason to.

[Staff: Warren Ashcroft]

...that isn't possible on our servers though as each database has its own logins.

[RFH Reseller: askjim]

I guess you could manually grant access to both databases for a particular user.

Alternatively in SQL 2005 there is something called linked servers - unfortunately you are unlikely to have the rights to setup a linked server though and whether you could use it to access two databases on the same server as it is normally for two databases on seperate servers.

I am not sure why you would want to query data accross two databases - I would have thought it would be best to copy the data in so it could be queried.

[Tanzy]

Take a look at

http://msdn.microsoft.com/en-us/library/ms190312.aspx

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
     'SELECT GroupName, Name, DepartmentID
      FROM AdventureWorks.HumanResources.Department
      ORDER BY GroupName, Name') AS a

However, I would NEVER recommend cross database queries if you can avoid it.

[Staff: Warren Ashcroft]

Take a look at

http://msdn.microsoft.com/en-us/library/ms190312.aspx

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
     'SELECT GroupName, Name, DepartmentID
      FROM AdventureWorks.HumanResources.Department
      ORDER BY GroupName, Name') AS a

However, I would NEVER recommend cross database queries if you can avoid it.

...interesting, didn't know you could specify a connection string within a cross-db query like that.

[RFH Reseller: balexandre]

you can always perform the 2 queries separated, populate a DataTable inside a DataSet and then use the DataSet to group, order by, filter, etc ...

DataSet ds = new DataSet();
ds.Table[0] = getDataTableFromQuery( query1 );
ds.Table[1] = getDataTableFromQuery( query2 );
DataColumn parentColumn = ds.Tables[0].Columns["CustID"];
DataColumn childColumn = ds.Tables[1].Columns["CustID"];

DataRelation relCustOrder;
relCustOrder = new DataRelation("CustomersOrders", parentColumn, childColumn);
ds.Relations.Add(relCustOrder);

etc ...