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)

SQL Scripts, SQL Stored Procedure
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

  • Query with parameters
    —————————-
    SELECT * FROM OPENQUERY([ServerInstance],’exec Test.dbo.USP_Process ”1234567890123456”’)

    Dynamically pass parameters
    =====================
    declare @sql varchar(1000),@param varchar(16)

    set @param=’1234567890123456′

    set @sql=”” +’exec Test.dbo.USP_Process ‘ + ””” +@param+ ””””

    EXEC (‘SELECT * FROM OPENQUERY([ServerInstance],’+@sql+’)’)

    Also,you can insert the results into a table.
    Hope this helps…

    Reply
  • Hi Expert

    i m learining to sql server2005 initialy . so i have one doute in store procedure how can i use the month and year in store procedure

    regrds by
    karthik

    Reply
  • Hello all. I am a newbee to Link Servers and this blog is coming pretty handy to get started, so thanks everyone for the info.

    Up to the moment we have been using one sql server containing all the DB of our proyects but now we are changing to linked servers. The problem is that we are going to have to change all the Stored Procedures of all our databases, which really is going to be a lot of work, so I am trying to find a way of doing this that doesn´t imply changing by hand every single SP, considering also that the location of a particular database may change from time to time and we´ll have to do the same operation more times.

    Any ideas would be nice,

    Thanks.

    Reply
  • when changing all the stored procedures I meant where before we had DB.Table we´ll obiously we´ll have to change it toDB.Table we´ll have to change it to Server.DB.Table

    Reply
  • PayDogg,
    If you have your code in T-SQL you should be able to use a search and replace utility.
    RJM

    Reply
  • hello,

    i am trying to call a mysql 5.1.26 procedure from sql server 2000 procedure.
    the server is linked properly
    when i do a select statement using openquery for just getting the remote data it works.
    but i am getting this error..
    my remote server name is TOMySql
    and the schema belongs to root.

    Could not execute procedure ‘myproc’ on remote server ‘TOMySql’.
    [OLE/DB provider returned message: [MySQL][ODBC 3.51 Driver][mysqld-5.1.29-rc-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘?=call “myschema”.”myproc”;1’ at line 1]

    the sql server 2000 procedure is

    alter proc mydistproc
    –@name varchar(50),
    –@address varchar(50)
    as
    begin
    exec TOMySql..myschema.myproc

    –EXEC TOMySql.root.myschema.myproc()
    –select * from OPENQUERY(TOMySql, ‘call myschema.myproc’)
    –exec(OPENQUERY(TOMySql, myschema.myproc))
    –insert into OPENQUERY(TOMySql, ‘SELECT name, address FROM myschema.temp’)
    –select @name, @address
    –ROWLOCK

    end

    ————————————————————————

    the my sql procedure is

    DELIMITER $$

    DROP PROCEDURE IF EXISTS `myproc` $$
    CREATE * from sys.tables

    I am using these code to retrieve all tables name.
    it works but “Name” column field not showing full table name like “person.address”.
    Plz help me how can i retrieve full name of table.

    Thanks

    Reply
    • Hi Nitesh

      My name is Ludwig and I’m getting exactly the same error from MS Server 2008 when calling a stored procedure (SP) on MySQL 5.2.34.

      I can’t seem to call the SP directly:
      EXEC ERA.fswd_eras.dbo.RunMySP;

      ERA is the linked server and like you I can also use the Openquery to perform selects, inserts and updates, but openquery does not support calling functions on MySQL.

      Have you managed to solve the problem as yet?

      Regards
      Ludwig

      Reply
      • Thanks to “SSC Journeyman”, I’ve got an answer:

        EXEC(‘CALL RunMySP’) AT ERA.

        And it works!

  • Hi all,

    I created a linked server using below syntax
    — Stored Procedure to Create Linked Server
    EXEC sp_addlinkedserver
    @server = ‘TEST1’,
    @srvproduct = ‘SQLServer OLEDB Provider’,
    @provider = ‘SQLOLEDB’,
    @datasrc = ‘192.168.20.45\ls’

    It is successful

    I am trying to execute stored procedure created in ”192.168.20.45\ls”

    EXEC TEST1.IELS_SIT_SUB_7Jan.dbo.Spu_Poc_ShowHearingDetails ‘null’,’null’

    but it is throwing below error

    Msg 7357, Level 16, State 2, Line 1
    Cannot process the object “exec IELS_SIT_SUB_7Jan.dbo.sp_test”. The OLE DB provider “SQLNCLI” for linked server “TEST1” indicates that either the object has no columns or the current user does not have permissions on that object.

    Please some one help me in this.

    Thanks in advance

    Reply
    • For executing a stored procedure that requires parameters and it’s on a linked server I suggest you try the AT version of exec … For example:

      exec (‘exec IELS_SIT_SUB_7Jan.dbo.Spu_Poc_ShowHearingDetails null, null’) AT TEST1

      Details in SQL Server BOL. Hope this helps. Regards Nik.

      Reply
  • How to pass dynamic parameters to stored procedure

    Reply
  • need help, i am using sql server 2005, i have to schedule a job in which i have to call a stored procedure that is made in Oracle 10g….is this possible? ho0w can we do this?

    Reply
  • Can I run a remote stored procedure in SQL job, both database servers are sql server 2005

    Reply
  • If the SQL Server service account does not have execute rights on remote stored procedure than create a Credential using the user who has rights, then on that credential create a Proxy and use that proxy in agent job step.

    Regards,
    Pinal Dave

    Reply
  • Hi.
    I am passing table value paramter from one server to another server but I am getting the following error.

    valued parameters are not allowed in remote calls between servers.

    I am using sql server 2008 so do you have any solution for this ??

    Reply
    • Imran Mohammed
      June 5, 2010 10:19 am

      @Shailesh,

      Is it possible to share SQL you are using. It would be helpful to resolve this issue.

      Can;t you create Type data type in remote server and use it locally.

      ~IM.

      Reply
  • I hope this thread may still be alive? I am adding to an application that uses SQl2005. I have used Dynamic SQL in a Stored Procedure to retrieve data. Because of the way the application is structured I need to select the result set into a View. I have tried many ways to do this – including OpenRowSet (needs Ad Hoc Queries on (which they are not) – and accessing the Stored Procedure via a Linked Server using OpenQuery – which returns errors

    SELECT * FROM OPENQUERY([SERVER-1].Test1DB.dbo.[SP_RFTA])

    gives error:
    Incorrect syntax near ‘.’.

    any thoughts – and thank you in advance,
    PJ

    Reply
  • Hi pinal,

    I am working with sql server 2005. My company db is very huge. so i am planning to divide the db with help of linked server concept. Previous 7 years datas put into separate machine and forcoming datas are put into separate machine.so the we can fetch the data very fast manner. i don’t know how to do.. plz guide me..

    Thanks,
    Vinoth

    Reply
  • I have a weird one…
    what if the column is named something like [name.name]?

    I get the following error:
    Msg 7357, Level 16, State 2, Line 1
    Cannot process the object “”MyDB”.”dbo”.”test””. The OLE DB provider “SQLNCLI” for linked server “MylinkServer” indicates that either the object has no columns or the current user does not have permissions on that object.

    Table name is TEST
    Column name is [Ship.Address1]

    I can get around this by creating a view and renaming my columns as Address1, but is there another solution?

    Reply
  • try with :-
    select ‘[‘+ column name + ‘]’

    it should work

    Reply
  • how can i call a stored proc?
    please send the answer to my Email.
    thanks.

    Reply
  • Hi Pinal Dave,
    My name is Metin Gümüş and I m from Turkiye. I m really proud of learning you and letting us know about your knowledge.

    Thanks Dave.

    Reply
  • can anyone help us to execute mysql stored procedure from mssql trigger. or please provide some useful link which describes how to do it.

    Regards
    V.S.Jain

    Reply
  • Hi,

    Please tell me how to access multiple databases in a instance using single linked server.

    Thanks in advance
    Venki

    Reply

Leave a Reply