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
Query with parameters
—————————-
SELECT * FROM OPENQUERY([ServerInstance],’exec Test.dbo.USP_Process ”1234567890123456”’)
Dynamically pass parameters
=====================
declare @sql varchar(1000),@param varchar(16)
set @param=’1234567890123456′
set @sql=”” +’exec Test.dbo.USP_Process ‘ + ””” +@param+ ””””
EXEC (‘SELECT * FROM OPENQUERY([ServerInstance],’+@sql+’)’)
Also,you can insert the results into a table.
Hope this helps…
Hi Expert
i m learining to sql server2005 initialy . so i have one doute in store procedure how can i use the month and year in store procedure
regrds by
karthik
select month(getdate())
select year(getdate())
Hello all. I am a newbee to Link Servers and this blog is coming pretty handy to get started, so thanks everyone for the info.
Up to the moment we have been using one sql server containing all the DB of our proyects but now we are changing to linked servers. The problem is that we are going to have to change all the Stored Procedures of all our databases, which really is going to be a lot of work, so I am trying to find a way of doing this that doesn´t imply changing by hand every single SP, considering also that the location of a particular database may change from time to time and we´ll have to do the same operation more times.
Any ideas would be nice,
Thanks.
when changing all the stored procedures I meant where before we had DB.Table we´ll obiously we´ll have to change it toDB.Table we´ll have to change it to Server.DB.Table
PayDogg,
If you have your code in T-SQL you should be able to use a search and replace utility.
RJM
hello,
i am trying to call a mysql 5.1.26 procedure from sql server 2000 procedure.
the server is linked properly
when i do a select statement using openquery for just getting the remote data it works.
but i am getting this error..
my remote server name is TOMySql
and the schema belongs to root.
Could not execute procedure ‘myproc’ on remote server ‘TOMySql’.
[OLE/DB provider returned message: [MySQL][ODBC 3.51 Driver][mysqld-5.1.29-rc-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘?=call “myschema”.”myproc”;1’ at line 1]
the sql server 2000 procedure is
alter proc mydistproc
–@name varchar(50),
–@address varchar(50)
as
begin
exec TOMySql..myschema.myproc
–EXEC TOMySql.root.myschema.myproc()
–select * from OPENQUERY(TOMySql, ‘call myschema.myproc’)
–exec(OPENQUERY(TOMySql, myschema.myproc))
–insert into OPENQUERY(TOMySql, ‘SELECT name, address FROM myschema.temp’)
–select @name, @address
–ROWLOCK
end
————————————————————————
the my sql procedure is
DELIMITER $$
DROP PROCEDURE IF EXISTS `myproc` $$
CREATE * from sys.tables
I am using these code to retrieve all tables name.
it works but “Name” column field not showing full table name like “person.address”.
Plz help me how can i retrieve full name of table.
Thanks
Hi Nitesh
My name is Ludwig and I’m getting exactly the same error from MS Server 2008 when calling a stored procedure (SP) on MySQL 5.2.34.
I can’t seem to call the SP directly:
EXEC ERA.fswd_eras.dbo.RunMySP;
ERA is the linked server and like you I can also use the Openquery to perform selects, inserts and updates, but openquery does not support calling functions on MySQL.
Have you managed to solve the problem as yet?
Regards
Ludwig
Thanks to “SSC Journeyman”, I’ve got an answer:
EXEC(‘CALL RunMySP’) AT ERA.
And it works!
Hi all,
I created a linked server using below syntax
— Stored Procedure to Create Linked Server
EXEC sp_addlinkedserver
@server = ‘TEST1’,
@srvproduct = ‘SQLServer OLEDB Provider’,
@provider = ‘SQLOLEDB’,
@datasrc = ‘192.168.20.45\ls’
It is successful
I am trying to execute stored procedure created in ”192.168.20.45\ls”
EXEC TEST1.IELS_SIT_SUB_7Jan.dbo.Spu_Poc_ShowHearingDetails ‘null’,’null’
but it is throwing below error
Msg 7357, Level 16, State 2, Line 1
Cannot process the object “exec IELS_SIT_SUB_7Jan.dbo.sp_test”. The OLE DB provider “SQLNCLI” for linked server “TEST1” indicates that either the object has no columns or the current user does not have permissions on that object.
Please some one help me in this.
Thanks in advance
For executing a stored procedure that requires parameters and it’s on a linked server I suggest you try the AT version of exec … For example:
exec (‘exec IELS_SIT_SUB_7Jan.dbo.Spu_Poc_ShowHearingDetails null, null’) AT TEST1
Details in SQL Server BOL. Hope this helps. Regards Nik.
How to pass dynamic parameters to stored procedure
Using a dynamic SQL
Can you give us some sample values?
need help, i am using sql server 2005, i have to schedule a job in which i have to call a stored procedure that is made in Oracle 10g….is this possible? ho0w can we do this?
Can I run a remote stored procedure in SQL job, both database servers are sql server 2005
If the SQL Server service account does not have execute rights on remote stored procedure than create a Credential using the user who has rights, then on that credential create a Proxy and use that proxy in agent job step.
Regards,
Pinal Dave
Hi.
I am passing table value paramter from one server to another server but I am getting the following error.
valued parameters are not allowed in remote calls between servers.
I am using sql server 2008 so do you have any solution for this ??
@Shailesh,
Is it possible to share SQL you are using. It would be helpful to resolve this issue.
Can;t you create Type data type in remote server and use it locally.
~IM.
I hope this thread may still be alive? I am adding to an application that uses SQl2005. I have used Dynamic SQL in a Stored Procedure to retrieve data. Because of the way the application is structured I need to select the result set into a View. I have tried many ways to do this – including OpenRowSet (needs Ad Hoc Queries on (which they are not) – and accessing the Stored Procedure via a Linked Server using OpenQuery – which returns errors
SELECT * FROM OPENQUERY([SERVER-1].Test1DB.dbo.[SP_RFTA])
gives error:
Incorrect syntax near ‘.’.
any thoughts – and thank you in advance,
PJ
Hi pinal,
I am working with sql server 2005. My company db is very huge. so i am planning to divide the db with help of linked server concept. Previous 7 years datas put into separate machine and forcoming datas are put into separate machine.so the we can fetch the data very fast manner. i don’t know how to do.. plz guide me..
Thanks,
Vinoth
I have a weird one…
what if the column is named something like [name.name]?
I get the following error:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object “”MyDB”.”dbo”.”test””. The OLE DB provider “SQLNCLI” for linked server “MylinkServer” indicates that either the object has no columns or the current user does not have permissions on that object.
Table name is TEST
Column name is [Ship.Address1]
I can get around this by creating a view and renaming my columns as Address1, but is there another solution?
try with :-
select ‘[‘+ column name + ‘]’
it should work
how can i call a stored proc?
please send the answer to my Email.
thanks.
EXEC your_procedure
Hi Pinal Dave,
My name is Metin Gümüş and I m from Turkiye. I m really proud of learning you and letting us know about your knowledge.
Thanks Dave.
can anyone help us to execute mysql stored procedure from mssql trigger. or please provide some useful link which describes how to do it.
Regards
V.S.Jain
You need to set up mysql as a linked server from where you can access mysql procedure
Hi,
Please tell me how to access multiple databases in a instance using single linked server.
Thanks in advance
Venki