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,
How can I call a remote Oracle 10g procedure from SQL Server 2000?
We tried the following sentences:
exec
[LINKSERVER].Chema.Package.Procedure param1, param2
We received the following error message:
[OLE/DB provider returned message: One or more errors occurred during processing of command.]
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure ‘XXXX’ on remote server ‘LINKSERVER’.
We also tried testing teh LINKSERVER with SELECT * FROM OpenQUERY (LINKSERVER, ‘SELECT * FROM TABLE’) and it worked fine. We know that we cannot use OPENQUERY for execute procedures.
Please, could you help us?
Thanks in advance
Regards,
manager
Hi,
in Oracle 10g one out parameter will be defined as SYS_REFCURSOR. when the parameters are passed to stored procedure, OUT parameter is not taken care.
i am also facing the same problem. as described by you. if you find the answer please post it to site.
Thanks
Regards,
Mohan
how can i call a SP through below way. And where i have to mention the parameters?
SELECT * FROM OPENQUERY
([DEVSQL],AdventureWorks.dbo.spAdmin_GetAllUsers)
SELECT * FROM OPENQUERY
([DEVSQL],AdventureWorks.dbo.spAdmin_GetAllUsers ‘parameter value’)
Hey madhivanan
I got an error in this syntax.
My statement is like this :
([DEVSQL],AdventureWorks.dbo.spAdmin_GetAllUsers ‘parameter value’)
it gives error like : “Incorrect syntax near ‘AdventureWorks’.”
Hi,
I want to create the Linked server for IBM Informix.
Could you provide the steps for the same.
Thanks in advance.
Using method #1, is there a way to keep the ‘remote server’ name variable ? It will really help lot of issues. I am using method #1 to insert record into Oracle from SQL Server using linked server. The Oracle table has a time stamp column and this is the only way I could actually do so. Issue is that Linked Server (remote server) could be variable. Any suggestions ??
Thanks in advance.
Here is the SQL syntax that I am working with:
declare @currentDateToUse datetime ;
set @currentDateToUse = convert ( datetime, getdate());
Exec (‘ insert into SchemaName.TableName( col1, col2 ) values (?,?)’, @var1, @currentDateToUse ) at OracleLinkedServer1
Hello
I want to call linked server stored procedure in trigger.
But it’ll be given me an error.
Error :
Msg 7391, The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “ServerName” was unable to begin a distributed transaction.
Thanks in advance
Hi,
I am getting the following error when I am trying to connect to a remote database via stored proc.
Login failed for user(null) Not a trusted SQL Connection.
I am trying to access a remote database.
I did the following settings on the machine:
1)I added the server which I am trying to coonect in the Linkedserver using sp_addlinkedserver
2)I checked the ‘Enforced Distriuted transactions’ in Connection tab is sqlserver properties.
3)I am using sql2000 and installed the service pack 4 on tht machine.
4)I have also set the allow inbound,outbound connections in the Component services in Administrative tools of Control panel.
Do I need to do something more?
I have one stored procedure in one database and its inserting values to three tables simultaneously. Two tables are in the same database and another one in different DB but in the same server.
When i was trying to insert into table of second database it was not inserting and i used OpenDatasource() for inserting that.
But what happens now is; i am missing some data as it takes more time for executing the stored procedure. This procedure have to execute 4 times in every second.
Please suggest me a solution.
Regards,
Shenu Lal
Hi ALL,
Can link server be setup between sql server 2005 and sql server 2008. The data will be fetched from sql server 2008 database. The link server is defined in sql server 2005. I get following error:
OLE DB provider “SQLNCLI” for linked server “xxxx” returned message “Login timeout expired”.
OLE DB provider “SQLNCLI” for linked server “xxxx” returned message “An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.”.
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].
Thanks a lot,
Amit
I’m a newbie and just starting to use linked servers. I created the linked servers and have no problem calling stored procedures on the linked servers. I need to be able to call the same stored procedure on all the linked servers setup (a little less than 100). I am using a vbscript to execute the stored procedure from one server. I need to be able to call the stored procedure when needed for the needed linked server. My question is…Do I need to create the stored procedure on each individual linked server database in order to call it from a ‘centralized’ program/server? Thanks
Hello, when I try that code, i get an error saying : invalid outside procedure.
Any ideas what I should do?
Thank You So Much, Its really good one …
Question: I have a main database and a sattelite database in a SQL 2008 linked configuration. I’m running the following query in my sattelite database and want to know where would the actual query execute?
select C1,C2,C3, A1,A2,A3, A4 from MainDatabase..dbo.Customers inner join Accounts on Customers.id = Accounts.CustomerId and Customers.lastName=’Smith’
Will data first be copied from the MainDatabase to my local database and then the Join/Search Will occurr? Or the Whole Query will be shipped to the Main Database for execution.
Hi,Pinal,
Using spAddLinkServer i can add remote machine and execute remote sql commands.
I have few questions:
1) when it adds the remote server , all the databases of the remote server are visible.Can’t it be only the required database?
2) In some cases the command allows to add the link server . while exploring the catalog it shows error: login fail for user sa. Error code is:18456
Guys!!
Is there any way to copy data of one or more tables from your local database to a remote database , without using LinkedServer Concept.
Use SSIS
I need to copy data of one or more table from a local SQL database to a hosted server database. Most of the data in the local database is copied to the live one, though optimisations are made to reduce the amount of actual data required to be sent.
What is the best way of sending this data from one database to the other?
Hi,
Pinal
Can i use Transactional statements while using linked server concept?
hi,
got a problem on how to confine server name into a local variable.
Example:
declare @Server varchar(100),
@Database varchar(100)
SET @Server = ‘server’
SET @Database = ‘Database’
Exec [@Server].[@Database].[dbo].[sp_Storeprocedure]
with example above, i’ve got error 7202
any idea how to handle this?
thanx
I have a problem where we are migrating from Sybase to SQL Server. I need to be able to invoke a stored procedure in Sybase from Sql Server. The issue is that these STPs have output paramteters. I have a link set up but can not figure out how I can get to the output parameters. I ended up basically building lot of code in the OPENQUERY but while it seems to run it also does not work
execute (‘use tord_ord
declare @priceed datetime,
@error_code int,
@error_message varchar(255),
@a char(10),
@b varchar(10),
@c varchar(10),
@d varchar(10),
@e int,
@f char(2),
@x varchar(300)
set @priceed = CAST(”2007-05-08 12:35:29.123” AS datetime)
set @a =”1234567890”
set @b = ”1234567890”
set @c = ”1234567890”
set @e = -1 /* should cause error with TN set below”
set @d = ”1234567890”
set @f = ”TN”
set @error_code = 101010
set @error_message = ”startng mmessage”
exec STP_SIP_OA_close @a,@b,@c,@d,@e,@f,@priceed,@error_code,@error_message
set @x = rtrim(cast(@error_code as char(20)))
select @error_message as error_message
select @error_code as error_code
select @a
select @x
select @f
select @a
‘) at WPOALINK
And though no errors were reported I do not think it really invoked the stored procedure the values of error_code and error_message stayed the same. One of the first things the STP does is set them to 0 and an empty string.
hi madhivanan ,is there a way to call remote procedure in hibernate.is it can be done.can you give some suggestions