Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
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?

Answered 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
     
     
    Sign In to Vote
    0

    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.

    PSULionRP

     

All Replies

  • Friday, February 08, 2013 9:49 PM
     
     Answered Has Code
    Sign In to Vote
    1

    Hello PSULionRP,

    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.

     
  • Friday, February 08, 2013 9:50 PM
    Moderator
     
     
    Sign In to Vote
    0

    You can use OPENQUERY:

    http://www.sqlusa.com/bestpractices2005/selectfromsproc/

    Also SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices/ssis-wizard/

    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: SQL Server 2012 Pro - Programming, Design & Business Intelligence

     
  • Friday, February 08, 2013 9:53 PM
     
      Has Code
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    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, esquel@sommarskog.se