February 18, 2011 at 4:30 am
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
February 22, 2011 at 1:30 pm
not 100 percent sure it what you are asking
but yes you can do exec ts1.dbo.stored_procedure on a linked server
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply