May 6, 2010 at 3:26 pm
I've searched the web and cannot find a clean example of what a cross-server/cross-database query should look like. Can someone help please?
May 6, 2010 at 11:23 pm
This was removed by the editor as SPAM
May 6, 2010 at 11:26 pm
While providing the information already requested, you may also want to lookup linked servers in BOL.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 7, 2010 at 2:41 pm
I'm only trying to SELECT data using SQL in MSSMS 2008.
May 7, 2010 at 3:21 pm
Linking Servers:
http://msdn.microsoft.com/en-us/library/ms188279.aspx
Once you have created a linked server object for the remote server, there are several options for returning a resulset and joining it with local tables.
For exampe, you can execute SQL statements that join tables between the two servers by using the 4 part naming convention like so:
select o.orderid, c.customername
from dbo.customers c
join serverb.northwind.dbo.orders o on o.customerid = c.customerid
where
o.orderdate > '2010/04/01';
The problem with 4 part named joins, is that if you attempt to join multiple remote tables, especially joining remote tables between themselves, or complex where clauses, it can be an extreme performance bottleneck. It depends on how SQL Server chooses to distribute the query processing, for example sometimes it might even pull an entire remote table scan across the wire to the local server. You can try to remember all the rules for what performs well and what doesn't, but I just try to avoid this technique and don't screw with it.
As an alternative to 4 part named tables, you can also use openquery() to pass through a complete sub-query to the remote server, complete with where clause and additional joins between remote tables, and then guarantee you get a single filtered table valued result back to the local server for joining with local tables. For example:
select o.orderid, c.customername
from dbo.customers c
join openquery(serverb,'select orderid, customerid from dbo.orders where orderdate > ''2010/04/01''') o on o.customerid = c.customerid;
If you are not joining tables between two servers, just selecting from joined remote tables, then just use openquery() and don't even fool with 4 part named objects. However, openquery() doesn't accept parameters. For example, you can't pass it @customerid, and the SQL string itself can't be a parameter, so it is severely limited in that way.
You can also use the EXEC .. AT SERVER statement. Like openquery(), you can pass through the entire query to the remote server, but it has an advantage in that it accepts runtime parameters using the ? token and substitution.
exec ('select * from dbo.orders where customerid = ?',@customerid) at server;
You can't join tables between servers this way, but you can insert the result into a local temporary table and then join that with local tables. For example:
insert into #MyTable (a,b,c)
exec ('select a, b, b from table where customerid = ?',@customerid) at server;
select a,b,c,d,e
from AnotherTable
join #MyTable on #MyTable.a = AntotherTable.a;
You'll have to experiement to see what works best in your situation.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply