Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tennis_123
Starting Member
7 Posts |
 Posted - 2011-12-12 : 11:51:53
|
| we have some old sps which use linked server to pull data from old systems, the sp query statements use lots of joins with local and other remote tables (same link server) and other local tables. and of course, the performance is very very slow.i try to use openquery () to pull the data from linked server first then join them with local data locally, but I have problem when I try to use join statement to join remote tables . something like: SELECT * FROM OPENQUERY(LINK_server1, 'SELECT t1.id, t2.name from table_1 t1 left outer join table_2 t2 on t1.id=t2.id') --(table_1 and table_2 are in the same remote server)GOerror --"Deferred prepare could not be completed.".I want to use openquery() to pull all the required data from linked server with less connection. to my understanding, with multiple joins, every row of return will require a new connection. is it ture? thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-13 : 00:59:02
|
| nope. it will opne a single connection and execute query with join against it. if tables are fairly large you can try pulling table details individually to local temporary tables first after applying required filters and then joining the data from temporary tables.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
tennis_123
Starting Member
7 Posts |
Posted - 2011-12-13 : 11:58:48
|
| have any one use openquery to pull data from more than one remote tables? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-13 : 12:06:08
|
| Duplicate question to: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=169079 |
|
|
|
|
|
|
linked server & OpenQuery Issue.