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

  • I don’t want to hardcode the server and db details so please help

    Reply
  • Hi, I have a problem calling a stored procedure which is in a remote server, the message error is that I don’t have the remote server in sys.servers, but I query that table and it’s there.
    The original query that I implement was an insert to the remote server but when I query the remote table the data was truncate to 12 characters in one field.
    This is the query:
    insert [remote].database.dbo.table(field1,field2,field3,field4) values(‘00001’, ‘000000’, ‘HOYOS PAZ TERESITA’, ‘2015-09-29’)

    The field that is truncated is field3, its stored HOYOS PAZ TE, just that.
    When I run the insert statement directly in the remote server, it’s stored complete.
    So I have to use a stored procedure in remote server, but when I call it, it’s doesn’t works.

    I need help, I don’t know what else do.
    Tks.

    Reply
  • Hi, I have a problem to call Stored Procedure from Stored Procedure in Linked Server. The Error Msg 344, Level 16, State 1, Procedure r_bi_rawdata_bi, Line 203
    Remote function reference ‘[IP’s Server].bina.dbo.f_GetMaxLoanSeries’ is not allowed, and the column name ‘[IP’s server]’ could not be found or is ambiguous.
    I have configured Linked Server in both of server. Could you help me?
    Thanks a lot.

    Reply
  • Thank you for the great post! I am on SQL server 2016. I have used the following pattern successfully if I use sql statements (within quotes) and they run at the linked/remote server side and returns the results. Notice the “AT” keyword:

    EXEC (‘sql statements’, paramlist, … n) AT

    However it errors out on missing parameters if I replace the sql statements with a stored procedure name (within quotes) that I know to be residing on the linked server. What should be the workaround to make the storedprocedure execute on the remote server side?

    Reply
  • Mainly we use this SP_EXECUTE_REMOTE to connect to cross database/linked database right..?
    While using the SP_EXECUTE_REMOTE, if it encounter any error on the cross database doing DML operations suppose (Delete) it will not return back. it will stuck there and throws an error. (Error retrieving data from shard [DataSource=XXXXXXXXX Database=XXXXXX]. The underlying error message received was: ‘The DELETE statement conflicted with the REFERENCE constraint etc… The Statement has been terminated);

    How we need to capture that error message.??

    Please help.!!

    Reply

Leave a Reply

Menu