Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored Procedure At The Remote Server Expand / Collapse
Author
Message
Posted Friday, February 18, 2011 4:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 01, 2012 12:43 AM
Points: 63, Visits: 110
I have two server
1. The Host Server (HS1) - SQL Server Instance
2. The Target Server (TS1) - SQL Server Instance.

Need to fetch data from a TS1. But can't create any objects inside any objects under TS1.

There are two options:
1. Create a dynamic SQL and execute over a connection to the TS1 server every time. Don't want this option as the SQL Server optimizations can't be utilized.

2. Write a stored procedure under HS1. The stored procedure should have queries which will fetch data from the TS1 over a linked server.

I will like to go with the second option but how can I make sure that the statements / stored procedure gets executed on the TS1 server box so that after the execution is over the results are only returned to the host server and thus the resources of the target server are used (high configuration server) and the transport over the network is kept at the minimum.

If you need more calrity please let me know.

Awaiting a reply or some pointers for a detailed go through.

Regards
Post #1066257
Posted Tuesday, February 22, 2011 1:30 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 25, 2013 7:43 AM
Points: 1,384, Visits: 644
not 100 percent sure it what you are asking

but yes you can do exec ts1.dbo.stored_procedure on a linked server



Post #1067883
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse