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 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.
Yes with the help of Linked Server
can i edit view in data base
Yes
ALTER view your_view
.
.
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?
Make sure that Remote Procedure Call is started at the Server
On your Link Servers Properties you have to configure the Server options Tab and Set the RPC = True and RPC OUT = True.
Do we need admin rights for this?
Remote Procedure Call should be enabled
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.
You can use dynamic SQL
Make sure you read this
http://www.sommarskog.se/dynamic_sql.html
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
EXEC(‘backup database ‘+@sourcedb+’ to disk = ”’+@backup_file+””)
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