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)
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.!!