| Author |
Topic |
|
katarina07
Starting Member
31 Posts |
 Posted - 2008-06-10 : 14:01:00
|
| Hi,I would like to know what are the possibilities if I want to:- run a query on the tables of the server S1- load a result set to the server S2On the server S2, I have a linked server for S1, but it takes much more time to execute the query on S2 via linked server than on S1 . I would prefer something like the code would be executed on S1 and (only) the result set would be transferred to S2.I already have seen a code somewhere in which a procedure on S2 called another procedure on S1 to execute a code, and (only) the result was transferred to S2.The problem is I dont have an example of such a code, and also that I dont have rights to create procedures on S1.If somebody has experience on this, or knows some good links/sources about this, it would be a great help.ThanksKatarina |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-10 : 14:03:49
|
| You could use OPENROWSET function for this.Refer link for example usage & syntaxhttp://msdn.microsoft.com/en-us/library/ms190312.aspx |
 |
|
|
katarina07
Starting Member
31 Posts |
Posted - 2008-06-10 : 14:19:08
|
| I dont think that OPENROWSET would help me.my problem is not the access - I can do it via linked server, but the speed.I need to execute a query on S1 - I will have a result in a temporary table on S1, and I want to get this result to S2.From a temporary table on S1 to a table on S2. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-10 : 14:20:54
|
quote:
Originally posted by katarina07
I dont think that OPENROWSET would help me.my problem is not the access - I can do it via linked server, but the speed.I need to execute a query on S1 - I will have a result in a temporary table on S1, and I want to get this result to S2.From a temporary table on S1 to a table on S2.
That you can even do it with SSIS package. |
|
|
|
katarina07
Starting Member
31 Posts |
Posted - 2008-06-10 : 14:24:12
|
| I want to do it in a code.i also think its not possible to reference tempdb in SSIS package. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
katarina07
Starting Member
31 Posts |
Posted - 2008-06-10 : 14:44:40
|
| My previous answer was already drastically simplified: get data from a temporary table on server S1 to a table on server S2. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
katarina07
Starting Member
31 Posts |
Posted - 2008-06-10 : 14:54:03
|
| ok, I will try it with OPENQUERY and take a look at the speed. Thanks. |
|
|
|
afaa
Starting Member
16 Posts |
Posted - 2008-06-10 : 19:32:20
|
| How about a view on S1 |
|
|
|
CTDBA
Starting Member
9 Posts |
Posted - 2008-06-11 : 09:20:15
|
| What does the query look like on the remote server, S1? If it is a "SELECT a, b from c where...." can you write a stored procedure to do the same thing? If you are returning a lot of records, the SP will have a lot less network overhead. |
|
|
|
|
How to get a result set fron one server to another