Just Learned - Execute Remote Server's Stored Procedures for SQL Server

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

Execute Remote Server's Stored Procedures for SQL Server

Jun 21 2011 3:18AM by Paresh Prajapati

Sometime on requirement based we need to call the stored procedures remotly to get output in local server and database.

This is the how can we execute it,

-- [RemoteServername] is the link server name 
EXEC [RemoteServername].[RemoteDatabaseName].[dbo].[RemoteStoreProcedureName]
GO

if link server is not done with RPC setting to TRUE, above call raised error like,

Msg 7411, Level 16, State 1, Line 1
Server 'ServerName' is not configured for RPC.

You can change it from linked server properties or using below code,

EXEC SP_SERVEROPTION @server='ServerName', @optname='rpc', @optvalue='true'
EXEC SP_SERVEROPTION @server='ServerName', @optname='rpc out', @optvalue='true' 
GO

4 Comments

One of our client using same statergy, DataBase(Just table data) in seperate server(SQLSEVR 2000), SPs& functions in Separate server (SQLSEVR 2005), Execution in different server(SQLSEVR 2005) for BI purposes.

commented on Jun 21 2011 12:12PM

Robert Dennyson

11 · 14% · 4419

hmmm..that sounds quite strange (to me). I guess it must be a small database or the performance could be horrible!

commented on Jun 21 2011 12:21PM

Jacob Sebastian

1 · 100% · 32004

Yes ,obviously slow... But 1st DB contains millions records, and its tables are auto generated, records coming from machine. Second server DB SPs are developed to serve different clients. third one from Client's server.

commented on Jun 21 2011 12:33PM

Robert Dennyson

11 · 14% · 4419

May be alternative option using OPENQUERY which pass-through query on the specified linked server [RemoteServername] is better than directly remote stored procedure call.

SELECT * FROM OPENQUERY([RemoteServername], EXEC [RemoteDatabaseName].[dbo].[RemoteStoreProcedureName])

commented on May 8 2012 2:30AM

Paresh Prajapati

6 · 22% · 7054

Your Comment

Sign Up or Login to post a comment.

"Execute Remote Server's Stored Procedures for SQL Server" rated 5 out of 5 by 12 readers +Paresh Prajapati