How do I call a SQL Stored Procedure from a different server and process the result set back in my original SQL Stored Procedure in a different server?
Friday, February 08, 2013 9:32 PM
I have a Stored Procedure that exists within Server A that needs to execute and return its result set to Server B that will be doing the call. How can I do this in SQL Server?
Thanks in advance for your help.
Friday, February 08, 2013 9:49 PM
Yo can setup your Server A as linked server on Server B and then execute your stored procedure using the below code as an example.
-- Setup the linked server. EXEC sp_addlinkedserver 'ServerA', 'SQL Server' GO -- Execute the SELECT statement. EXECUTE ('EXECUTE Databasename.dbo.sp @parameter = ?',value) AT ServerA; GO
Hima, To help the community better identify relevant thread, please don't forget to mark as answer or vote as helpful if and answer helps.
- Marked As Answer by Iric WenModerator Monday, February 18, 2013 7:09 AM
Friday, February 08, 2013 9:50 PMModerator
You can use OPENQUERY:
Also SSIS Import/Export Wizard:
Kalman Toth Database & OLAP Architect
Paperback / Kindle: SQL Server 2012 Pro - Programming, Design & Business Intelligence
Friday, February 08, 2013 9:53 PM
Or, if you have a linked server set up, just output the results of the proc to a temp table within the calling proc, and do whatever with it:
Create Procedure CallingProc (<paramlist>) As exec ProcOnOtherServer into #TempTable --do things with #TempTable
Friday, February 08, 2013 11:30 PM
As others have said, you can set up a linked server and use INSERT-EXEC. This may work smoothly, or it can be very hard to work, depending on your environment. Since INSERT-EXEC defines a transaction you get a distributed transaction and they can be painful. In SQL 2008 there is an option to sp_serveroption to decline the distributed transaction for the linked server.
An alternative is write a CLR stored procedure to make the call, and add enlist=false in the connection string.
Erland Sommarskog, SQL Server MVP, email@example.com