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 (http://blog.SQLAuthority.com)
pinaldave
I would love to have you cover these in detail. This is very timely for me.
Hi,
Can I execute a remote stored procedure which is sql 2000 from a sql 2005 procedure.
Leo Thomas.
can i edit view in data base
The servers are set up as linked servers, server A is sql 2005 and the remote server B is sql 2000.
The procedure has to be run on B, it gets values from a procedure running on A and returns a result to the proc on A.
I used the four part naming convention.
When i execute the following
EXEC @result = Dino.Prod.[dbo].[Proc_MCC_Payroll] @rows, @rows_retran;
I get an error Server ‘Dino’ is not configured for RPC.
both servers run on windows 2003 server enterprise edition SP2
How do I fix this?
Do we need admin rights for this?
Is there a way to execute the call to the remote stored procedure dynamically where the remote stored procedure accespts 2 input parameter and one output parameter?
Remote Stored procedure Header
Create Procedure usp_BillBroadcast (
@intFileFormat int,
@intFileType int,
@intBroadcast int out)
Since I cannot hardcode the link server name in my code, is there any way that I may be able to call the above stored procedure? Can you please cover it in detail?
Your help is greatly appreciated.
About the error “is not configured for RPC”. We have fix it, chaning ( on local server), linked server configuration to remote server setting “outbound RPC” as True.
We use:
EXECUTE (‘ select …etc… ‘ ) at
To Pinal Dave: Tanks, This blog is very interesting. I’m learning a lot.
Sorry, Problems with puntuaction shadown command line on my post. This is complete:
About the error “is not configured for RPC”: We have fix it, chaning ( on local server), linked server configuration to remote server setting “outbound RPC” as True.
We use:
EXECUTE (’ select …etc… ‘ ) at remoteserver
To Pinal Dave: Tanks, This blog is very interesting. I’m learning a lot.
Have you ever come across a situation in SQL 2005 where a query in the form of a stored proc, comes across as a llinked server call and gets hung, often causing it to be a lead block?
I have a few sprocs that are called frequently, and every so often they become hung, blocking other processes requiring the spid to be killed manually…Because we are a 24/7 shop we have gone as far as setting up jobs that look for these spids and KILL them when they meet the criteria that we have identified.
Any ideas what could be causing this? Anyone else experience this in their org? Your thoughts & experiences are very much appreciated!
C~
To follow up on RAM’s question, if you are using 2005, setup a linked server and a synonym. The synonym will allow you to “hard code” the server name and database instance name once. The synonym will act as an alias to the remote database. If you change the synonym to point somewhere else, any code referencing the synonym requires no changes. I only wish 2000 had synonym…
I am thankful for sites like this that really try to help. I have been struggling to call a stored proc from a linked server when the calling server is SQL 2005 and the linked server is SQL 2000. After a lot of reading and experimenting I found the solution, which I have laid out in simple steps below. Good Luck!
– Add a linked server
EXEC sp_addlinkedserver
@server=’BOB’,
@srvproduct=”,
@provider=’SQLOLEDB’,
@datasrc=’SERVER’,
@catalog = ‘Northwind’
– get a list of linked servers
EXEC sp_linkedservers
– add linked server login (this user and password has already been created on linked server)
EXEC sp_addlinkedsrvlogin ‘BOB’, ‘false’, NULL, ‘userlogin’, ‘password’
EXEC sp_serveroption @server=’BOB’, @optname = ‘rpc’, @optvalue=’true’
EXEC sp_serveroption @server=’BOB’, @optname=’rpc out’, @optvalue=’true’
–see all linked servers and status
EXEC sp_helpserver
– perform query against linked server
SELECT * FROM OPENQUERY (BOB, ‘SELECT * FROM customers’)
– execute stored proc on Linked Server
declare @CustomerID char(50)
EXEC BOB.Northwind.dbo.CustOrderHist ‘ALFKI’
– drop linked server logins (must be done prior to droping linked server)
EXEC sp_droplinkedsrvlogin ‘BOB’, NULL
– drop linked server
EXEC sp_dropserver ‘BOB’
I would like to run the following on a server that is linked in.
backup database @sourcedb to disk = @backup_file
Can you help with the syntax. When I specify
[remote server]…backup datebase [remotedb] to disk = [file]
Thanks
Tony,
You can’t use a linked server to address the database you want to backup.
You can use ” sp_executesql ” to execute an SQL string on the remote server
EXEC .master.dbo.sp_executesql N’BACKUP DATABASE pubs TO DISK = ”c:\pubs.bak”’
Thanks chema! You helped me solve a big problem I was having!
Hi, problem is re Linked Servers and rpc calls.
Using SQL Server 7 (A) rpc to SQL Server 2005 (B).
A is setup with a linked server B and can replicate to B – NO problems. (The replication was setup with sa connection)
I have a linked server setup with a standard user and appropriate access rights. Below 1 query works the other fails. Any ideas? It used to work until I had to recreate everything due to firewall failure.
Query fails
1. select * from SERVERNAME.dbname.dbo.tablename
> Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider ‘SQLOLEDB’ reported an error. The provider did not give any information about the error.
Query Suceeds
2. select * from openquery(SERVERNAME,’select * from tablename’)
I have also created said same linked server on another SQL server 7 (C) db and both queries work NO PROBLEMS. It’s hard to crack the problem. Any ideas?
For extra info. using Patrick example above. All works for OpenQuery but same error when executing
select * from SERVERNAME.dbname.dbo.tablename
Sorry fellas you took too long. ;-)
to solve the problem go the registry
(HKLM\Software\Microsoft\Mssqlserver\providers\SQLOLEDB ) and make sure all
except “AllowInProcess” have a value of 0.
Dave
I have an intresting situation whereby a stored procedure called via a 2005 linked server connection to an SQL 2000 database fails to complete but does not raise an error. The execution of the Stored procedure is aborted and the calling SQL procedure continues from the next statement.
If I run the stored procedure in management studio in completes correctly.
If I run the SQL job that calls the stored procedure it aborts without error as previously mentioned.
Finally this behaviour is unpredictable in that this problem has only started to happen recently. No changes have been made to any of the stored procedures
ewan, sounds like user access rights to me. Is the SQL job ran as “sa” or another user?
Since this thread is the closest I’ve found to my problem I was hoping someone may be able to help me with the folllowing issue:
I have a stored procedure on a SQL 2005 server exec’ing a stored procedure on a SQL 2000 server through a linked server. I receive the following error on this exec:
The attempt by the provider to pass remote stored procedure parameters to remote server ‘TEMP_TOPTAGGING’ failed. Verify that the number of parameters, the order, and the values passed are correct.
This exact same scenario works correctly going from SQL 2005 to SQL 2005…just not from SQL 2005 to 2000.
I have verified that the parameters, orders and values are correct.
Has anyone had a similar issue?
Thanks,
Bill
As soon as I ask the question I figure out what’s causing the problem.
We were using the 2005 varchar(max) variable to a text variable parameter on 2000. Changed it to varchar(8000) and it works correctly.
do we require system admin rigths to create stored procedures in sql 2005.. please answer my question
I am trying to run the following stored procedure using link server. My other non parameter SP works fine with this following ways but the parameter SP gives me the following error:
DECLARE @vTechnicianName Varchar(25)
DECLARE @cmd Varchar(8000)
SET @vTechnicianName = ‘AMY GAMBLE’
SET @CMD = ‘AGY_REC_RPT.AA_Test.dbo.AA_Legal_Agents_Report ‘+””+@vTechnicianName+””
Print @cmd
EXEC @CMD
Error:
AGY_REC_RPT.AA_Test.dbo.AA_Legal_Agents_Report ‘AMY GAMBLE’
OLE DB provider “MSDASQL” for linked server “AGY_REC_RPT” returned message “[Microsoft][SQL Native Client][SQL Server]Could not find stored procedure ‘AA_Test.dbo.AA_Legal_Agents_Report ‘AMY GAMBLE”.”.
Msg 7212, Level 17, State 1, Line 8
Could not execute procedure ‘AA_Legal_Agents_Report ‘AMY GAMBLE” on remote server ‘AGY_REC_RPT’.
Is there a limit to the number of linked servers that can be attached to a single SQL 2005 Server?
Please let me know what i have to do in the case of linked server is not created…I have the 100 server..i want to create the job on 100 server which will call to the 1 server.so it does n’t make sense to create the linked server for all 100 servers..
@Manish,
In SQL Server 2000 and 2005, we have something called Master and Target Server.
Right click on SQL Server Agent Properties -> Make this Server Master or Target.
Make a Master Server and then also define what will be target servers for this master server.
Use of this is, if you have a job on Master server and you want this to run in 100 different target servers, you can, you just need to create this job only on master Server.
I don’t know what type of job you are running what script does it contain, so I can’t really tell you if this has to be a linked server for in order to work.
Please explore more about this Master Server and Target Server, its a very good concept.
After you sucessfully implement this, please share here how did you do this, it will be helpful for all of us.
Regards,
IM.
Hi, I have a Sybase linked server on SQL 2005, I need execute a store procedures, when the sp has no parameters all work fine, but with parameters bring up the next error:
Mens. 7357, Nivel 16, Estado 2, Línea 1
Cannot process the object “exec finmes..sp_testsql ‘a’”. The OLE DB provider “MSDASQL” for linked server “SPC” indicates that either the object has no columns or the current user does not have permissions on that object.
So, this is the query:
SELECT * FROM OPENQUERY(SLS, ‘exec database..sp_testsql ”a”’)
Thanks a lot.
Hello, I have a Sybase linked server toward SQL 2005 and I need execute a stored procedure with parameters. If I try the sp without paramaters all works fine, but when I add the parameters bring me up the next error:
Mens. 7357, Nivel 16, Estado 2, Línea 1
Cannot process the object “exec finmes..sp_testsql ‘a’”. The OLE DB provider “MSDASQL” for linked server “SPC” indicates that either the object has no columns or the current user does not have permissions on that object.
This is my sql, executed in SQL:
SELECT * FROM OPENQUERY(SPC, ‘exec sybasedb..sp_testsql ”a”’)
This is my sp created in Sybase:
create procedure sp_testsql @test char(1)
as
select @test + ‘I AM IN SYBASE’
Thanks a lot for help.
how can i call a stored procedure (which receives parameters and returns a value) from another stored proedure
thanks
Luis Alberto,
I am in a similar situation and I get the same error message when i run a insert query using OPENQUERY.
Were you able to solve your problem. I would really appreciate it if you can post it here.
Thanks
[...] Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server. (Read More Here) [...]
Luis,
I’ve a problem with a table which is getting wrong data and i need to create a trigger to track it, but I want to know which stored procedures is sending bad data and I tried with @@PROCID and it returns the trigger ID. How can I get the ID of the stored procedure filling the table?
Thanks
Y
I created a utility database (UTIL) on the same server as my test database (TEST). The production database (PROD) is on another server.
I had hoped that I could put procedures, functions, etc. on the UTIL server and run them remotely from TEST and PROD.
However, it looks like the procedure looks at its own database and not on the one where the call originated.
Example:
Table1 resides on DEV
Table2 resides on UTIL
Proc1Sp resides on UTIL and has the following command:
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name
I connect to DEV and execute the proc on UTIL as follows:
UTIL.dbo.Proc1Sp Table1 – It cannot find Table1.
UTIL.dbo.Proc1Sp Table2 – I get a resultset.
Is my thinking wrong? Does the procedure only look at the database where it resides? If I have many databases and want to build a collection of utilities, do I have to copy them to all the databases or can I put them all in one place?
Thanks… mpv
Thank you Patrick for your great response on 4th April 2008. I was having terrible trouble trying to get a linked server to work on SQL 2005 from SQL 2000. I was creating it in Enterprise Manager to no avail but when I created it as you said in your steps it works perfectly! Thanks again!
Hello Pinal,
You sight is wonderful. I’ve found so much information here. I have a question for you.
Our company has 13 SQL Server Databases. Currently each database has a duplicate copies of all of our Stored Procedures (SP). I would like to centralize these SP into one database then perhaps add the database name to the arguments to get data from the other database. This central database would not contain the tables just the SP while the other database would contain the data but not the SP.
Example:
CentralDB.SP1(‘TestDB’, …)
{…
select * from Table … table does not exist in this database
}
called from
TestDB.SP1(…) … table exists in this database
Hi Pinal,
Some One told me that “The new table created with the SELECT INTO clause in linked servers does not support 4 part names.
But I am not sure, Do we have this thing documented any where ?
As in BOl they have just mentioned that SELECT INTO is supported in linked servers as well.
Thank You,
Mithalesh
Hiiii
I want to execute one SP available at Remote Server.
when i am executing it as you told:
Execute [192.168.1.82\SQL2005].[PCFileParser].[dbo].[usp_RemoteCalling_Test]
It is giving Error:
Msg 7202, Level 11, State 2, Line 1
Could not find server ‘192.168.1.82\SQL2005′ in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
Please solve my problem as soon as possible.
My client don’t want to use linked Server, so can’t use OPENQUERY clause.
Hi Pinal,
I am looking for a T-SQL script that automatically script out all the linked server. is it possible in SQL server. Please guide me.
Regards
Priyaa
hi how i call Stored Procedure in asp.net
YRAIS,
You may want to have each stored-procedure store a static value or some sort of identifier into one of the fields of the table. That way that field in the table holds the source of the data. When you run a query you see where the data came from.
RJM
Hi,
I Have the same problem reported by Luis Alberto, calling stored procedures with parameters.
Please If you have some notice post it.
Thanks.
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
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 DEFINER=`root`@`%` PROCEDURE `myproc`()
BEGIN
select ‘hi’ as greetings;
END $$
DELIMITER ;
———————————————————————-
Kindly help
i dont know where i am going wrong..
Regards
Nitesh