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. There are two simple ways to execute a remote stored procedure.
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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
133 Comments. Leave new
I don’t want to hardcode the server and db details so please help
A synonym would likely be your best bet.
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.
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.
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?
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.!!