Post reply

calling an remote stored proc from a stored proc

  • 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

  • December 2, 2002 at 12:08 pm

    #442823

    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

  • December 2, 2002 at 12:13 pm

    #442825

    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.

  • December 3, 2002 at 9:55 pm

    #443016

    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.

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

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