calling an remote stored proc from a stored proc

Question

calling an remote stored proc from a stored proc

valli

SSC Veteran

Points: 202
More actions
December 2, 2002 at 8:13 am

#79494

Hi,
I am new to TSQL and was wondering if someone can tell me how to exec a stored proc on a remote server from within a stored proc.
I need to know how I can pass the login & password for the remote server and then issue the exec statement on the stored proc.
Any ideas??
Thanks
VR

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

dm Ten Centuries Points: 1135 More actions · Answer 1

Set up the remote server as a linked server on your local server. SQL Server Books Online (help files) will show you how to do this.

Then use the following code:

exec ServerName.DatabaseName.OwnerName.StoredProcedureName

to call the remote sp. ServerName is the name of the remote server.

Diane

Mkumari Hall of Fame Points: 3498 More actions · Answer 2

Valli,try to create the link servers first or else you cannot execute the remote stored procedures.

Example:

EXEC servername.databasename.dbo.storedproc.name

you can execute this from within the stored procedure or individually.

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 3

If a linked server is not an option for you, consider the OPENROWSET function.it will allow you to do what you're asking but will be much slower. Each query will have to establish its own connection where the linked server connects once.