Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    70

    Lightbulb Unanswered: Stored Procedure Of Different Server

    hi,
    Can we invoke a stored procedure in a different server. Can anyone give me a step by step example

    Thanks
    Anand

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    you will need to make a linked server to the second server and then you will be able to access the stored proc as [servername].[dbname].[owner].[objectname].

    for information on how to make a linked server , consult SQL server books online

  3. #3
    Join Date
    Jul 2003
    Posts
    70
    Thanks for the response. But I am not able to add a server as a linked server. It says that the server has already been added as linked server. May be I feel if we add a server as remote server we cannot add it in linked server. I am not sure about this. Do u know about this?

    Thanks
    Anand

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    You are right. Yoiu will not be able to add a server both as a remote server and linked server.

    From the MS SQL Books online : --

    To disable a remote server setup

    Run the following code on the second server running Microsoft SQL Server.
    EXEC sp_remoteoption ServerName1,sa, sa, trusted, false
    EXEC sp_dropremotelogin ServerName1, sa, sa
    RECONFIGURE
    GO

    EXEC sp_configure 'remote access', 0
    EXEC sp_dropserver ServerName1
    EXEC sp_dropserver ServerName2
    RECONFIGURE
    GO

    Once you have done this you will be able to add the server as a linked server.
    Stop and restart the second SQL Server.


    Run the following code on the first SQL Server:
    EXEC sp_configure 'remote access', 0
    EXEC sp_dropserver ServerName2
    EXEC sp_dropserver ServerName1
    RECONFIGURE
    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •