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
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.
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
Can i create Store Procedure or Alter SP in linked Server? Please , can i get an example/
hi; I am trying to execute a local stored procedure on multiple linked servers.
how can i do it?
thanks;
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
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
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
Correction. I’need to drop the user from a specific database on a specific server.
When i Call Procedure through Agent Job for Remote Data using Linked Server, It fails
Can Any body have solution
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
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
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]
Hi Dave,
Is it possible to Call a Stored Procedure in other server, without configuring a linked server?
Please help.
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
Can we use table valued parameters when we execute procedure on remote server using linked servers?
hi
I need to use convert an openquery but selecting multiple fields in a table because I have a linked server to oracle sql
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;
@Renu – what is the provider you are using?
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.
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??
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
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 :)
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.
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]