SQL SERVER – Executing Remote Stored Procedure – Calling Stored Procedure on Linked Server

I was going through comments on various posts to see if I have missed to answer any comments. I realized that there are quite a few times I have answered question which discuss about how to call stored procedure or query on linked server or another server. This is very detailed topic, I will keep it very simple.

I am making assumptions that remote server is already set up as linked server with proper permissions in application and network is arranged.

Method 1 : Remote Stored Procedure can be called as four part name:
Syntax:

EXEC [RemoteServer] .DatabaseName.DatabaseOwner.StoredProcedureName

‘Params’
Example:

EXEC [DEVSQL] .AdventureWorks.dbo.uspGetEmployeeManagers ’42’

Method 2 : Use Distributed Query functions (OpenQuery())
Syntax: SELECT *

FROM OPENQUERY([RemoteServer],DatabaseName.DatabaseOwner.StoredProcedureName)

Example: SELECT *

FROM OPENQUERY([DEVSQL],AdventureWorks.dbo.spAdmin_GetAllUsers)

Let me know if you want me write about above two methods in detail.

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – 2005 – Open SSMS From Command Prompt – sqlwb.exe Example
Next Post
SQLAuthority News – Latest Interesting Downloads and Articles

Related Posts

133 Comments. Leave new

  • Hi Pinal,
    I want in detail about Linked Servers in sql server 2008. I want to connect Oracle 10g in SQL Server 2008. Pls help me in this issue.

    Reply
  • do you guys know how to execute a sotred procedure (in a linked server, Sybase this time) which needs an output parameter with openquery o any other option? I´m trying this:
    sp in sybase:
    CREATE PROCEDURE dbo.usp_genera_saldo (@anio smallint, @periodo varchar(1), @cuenta int, @cve_con int, @importe_pagar decimal(30,2), @fecha_limpago datetime, @clave_carrera int, @opcion smallint, @salida int output )…….. end

    calling it in sql server :
    execute (‘Execute usp_genera_saldo (?,?,?,?,?,?,?,?,?)’ ,2012,’0′,171394,4,100,’2012/06/22′,904000,3,) AT SYBASE1
    or
    SELECT * FROM OPENQUERY(SYBASE1,'{EXECUTE usp_genera_saldo({ 2012, ”0”, 171394, 4,100,”2012/06/22”,904000,3,{? , ReturnVal})’)
    or
    declare @r int
    EXECUTE ( ‘begin usp_genera_saldo (?,?,?,?,?,?,?,?,?); end;’, 2012, ‘0’, 171394, 4,100,’2012/06/22′,904000,3, @r output) AT SYBASE1;
    nothing works

    Reply
  • Can i create Store Procedure or Alter SP in linked Server? Please , can i get an example/

    Reply
  • Orestes Arniella
    November 12, 2012 9:46 pm

    hi; I am trying to execute a local stored procedure on multiple linked servers.
    how can i do it?

    thanks;

    Reply
    • You need to add the server which has that procedure as linked server in other servers and using four part name, you can execute the procedure

      Reply
  • Hi all,

    I have a stored proc on server A, have linking from server B to server A,
    wanted to view server A text using sp_helptext as don’t have access to serverA.
    Ho to to do this..
    Select * from opequery(serverA, ‘sp_helptext spname’)
    not working throwing error…invalid object name #comments( this is an temp table whcih is created in sp_helptext procedure )

    Any idea.?
    Thanks,
    RK

    Reply
  • Hi all,
    I have 2 SQL 2012 servers that are linked. One contains a table of user, server, database and dates. I’m trying to create a process that will find expiring user by date and then drop them from the specific server and table (leaving the SQL login intact). I searched for days and it appears that this can’t happen. Can anyone make any suggestions?

    CED

    Reply
  • When i Call Procedure through Agent Job for Remote Data using Linked Server, It fails
    Can Any body have solution

    Reply
  • hi
    I can’t execute stored procedure in sybase from sql server using Linked server.

    It throws below error if i use OpenQuery

    Cannot process the object “EXECUTE PROCEDURE”. The OLE DB provider “TEST ” for linked server “LNKSERVER” indicates that either the object has no columns or the current user does not have permissions on that object.

    When I use Four Part Name it throws below error

    OLE DB provider “TEST ” for linked server “LNKSERVER” returned message “Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.”.

    Could you please help me?

    Thanks in advance
    Jaya

    Reply
  • I am trying to Exectue sybase stored procedure using linked server in Sql server 2008 R2 but throws errors:

    Below are the methods i am using

    EXECUTE [LinkedServer].[DB].[dbo].[StoredProcedure] 1, ‘F’, NULL, NULL, NULL, NULL

    OR

    declare @Parameter1 NUMERIC(38) declare @Parameter2 NUMERIC(6) declare @Parameter3 VARCHAR(250) EXECUTE [LinkedServer].[DB].[dbo].[StoredProcedure] 1, ‘F’, NULL, NULL, NULL, NULL, @Parameter1 Output, @Parameter2 Output, @Parameter3 Output

    OR

    EXECUTE (‘EXECUTE [DB].[dbo].[StoredProcedure] ?,?,?,?,?,? ‘, 1, ‘F’, Null , NULL, NULL, NULL ) AT [LinkedServer]

    Below is the error :

    The attempt by the provider to pass remote stored procedure parameters to remote server ‘[LinkedServer]’ failed. Verify that the number of parameters, the order, and the values passed are correct.

    I have set the RPC out values to true in Linked server properties. But I am not sure if I am missing any other properties.

    Could someone please help me

    Many thanks in advance

    S

    Reply
    • This is solution
      Declare @Parameter1 NUMERIC(4)
      Declare @Parameter2 VARCHAR(1)
      Declare @Parameter3 DATETIME
      Declare @Parameter4 NUMERIC(8)
      Declare @Parameter5 VARCHAR(12)
      Declare @Parameter6 VARCHAR(28)
      Declare @Parameter7 NUMERIC(38)
      Declare @Parameter8 NUMERIC(6)
      Declare @Parameter9 VARCHAR(250)

      select @Parameter1 = 1
      select @Parameter2 = ‘A’
      select @Parameter3 = NULL
      select @Parameter4 = NULL
      select @Parameter5 = NULL
      select @Parameter6 = NULL
      select @Parameter7 = NULL
      select @Parameter8 = NULL
      select @Parameter9 = NULL

      EXEC (‘EXEC [DB].[dbo].[StoredProcedure] ?,?,?,?,?,?,? ,? ,? ‘,@Parameter1,@Parameter2,@Parameter3,@Parameter4,@Parameter5,
      @Parameter6,@Parameter7 Output,@Parameter8 Output,@Parameter9 output) AT [LINKSERVER]

      Reply
  • Hi Dave,

    Is it possible to Call a Stored Procedure in other server, without configuring a linked server?

    Please help.

    Reply
  • Punit Singhi (@punitsinghi)
    April 25, 2014 8:41 pm

    Hi Pinal,

    I am facing a issue regarding linked server. I have a SP which uses synonyms and fetch data using dynamic SQL from linked server. SP has lot of joins to the linked server tables but it doesn’t have any join with local tables. When I run the SP for some of the columns I am getting incorrect data(getting null value) but when i run the query directly(query which exec command will execute) I see correct data. I am unable to find any ans for the same.

    Any help is appreciated.

    Thanks,
    Punit Singhi

    Reply
  • Can we use table valued parameters when we execute procedure on remote server using linked servers?

    Reply
  • hi
    I need to use convert an openquery but selecting multiple fields in a table because I have a linked server to oracle sql

    Reply
  • Hi,
    This was helpful though I am facing a unique problem I have 2 SQL servers on which i have configured linked server to DB2(REMOTEABC), I am trying to run a stored procedure and it works fine on one server while on other server it gives an error.”Msg 7213, Level 16, State 1, Line 1
    The attempt by the provider to pass remote stored procedure parameters to remote server ‘REMOTEABC’ failed. Verify that the number of parameters, the order, and the values passed are correct.”

    I am able to run other stored procedures on the same server while researching more i figured i am not able to run stored procedures which accepts more than 2 parameters, while on the other server everything works fine. I am quite puzzled.

    Stored procedure I am running :
    exec (‘call SERVER.SPNAME(?,?,?)’,’PARAM1′,’PARAM2′,’PARAM3′) AT REMOTEABC;

    Reply
  • LinkedServerNewbie
    May 22, 2015 8:51 pm

    I am newbie to linked server concept, I am looking for help to resolve my issue. Please suggest.

    My requirement:
    In DB2 data base I have a stored procedure and from SQL 2008 R2 using Linked server I need to be able to execute stored procedure. I am getting the error while I am trying to execute.

    Created SP as below with db2admin user:
    CREATE OR REPLACE PROCEDURE SP_MyStoredProcedure
    LANGUAGE SQL
    SPECIFIC SP_MyStoredProcedure
    — EXTERNAL ACTION
    BEGIN
    …….
    END

    select * from [MyDb2LinkedServer].[db2DB].[db2admin].TLS_MyDb2Table and I get the results, which means my linked server DB2 connectivity is fine.

    Then while executing as below SP got the error

    exec [MyDb2LinkedServer].[db2DB].[db2admin].SP_MyStoredProcedure

    Error:
    OLE DB provider “DB2OLEDB” for linked server “MyDb2LinkedServer” returned message “Routine “*rocedure”?SQL150518145704050?…erver”.”SP_MyStoredProcedure”?*?4″ (specific name “”) is implemented with code in library or path “”, function “” which cannot be accessed. Reason code: “”. SQLSTATE: 42724, SQLCODE: -444″.
    Msg 7212, Level 17, State 1, Line 1
    Could not execute procedure ‘SP_MyStoredProcedure’ on remote server ‘MyDb2LinkedServer’.

    Here in the error
    specific name “”
    library or path “”
    function “”
    Reason code: “”

    all are empty, no much information is available.

    Need help on this and how this can be resolved? Any help is appreciated.

    Note:
    “rpc”, “rpc out” both options are true.

    Reply
  • Hi… is there any relation between linked server & Setting the Transaction Isolation level serializable?? Means, will it create any problem if we use both in single query??

    Reply
  • I hope you can help me, I’m running a DTSX package that I had to remap to a backup copy of the database. I don’t understand why this error occurs:

    Microsoft (R) SQL Server Execute Package Utility
    Version 11.0.2100.60 for 64-bit
    Copyright (C) Microsoft Corporation. All rights reserved.

    Started: 7:15:05 AM
    Error: 2015-06-26 07:15:17.72
    Code: 0xC002F210
    Source: Create Reporting Tables Execute SQL Task
    Description: Executing the query ”
    DECLARE @return_value int

    EXEC @return_value = […” failed with the following error: “Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
    End Error

    Reply
  • Hi,

    Looking for a query to which will give all the objects in my SQL 2005 Database which use a Linked Server. Currently using 2 different queries with no good results.
    Thanks for the help :)

    Reply
  • Hi there,

    Is there a way to deploy the same SQL job to all linked servers.

    It is just a job to do an rebuild of all indexes but would like to know if this can be done as all our servers are setup as linked servers and would be much easier just to run the script once for all the servers?

    Can someone please help me with this?

    Kind Regards.

    Reply
  • Hi All,
    Is there a way to parameterize server,database and schema in a stored procedure which uses linked server during select as I am to run the SP with different environments. I use 2012 version
    select max(applicationnumber) from [server].[db].[schema].[table]

    Reply

Leave a Reply