Professional Web Applications Themes

Executing sp of another Server - Microsoft SQL / MS SQL Server

Hello all, Can we execute stored procedure residing in another server. If yes how? I tried this way: Exec Server1.Database.DBO.ProcName But it gave me this error: Could not connect to server 'Server1' because 'sa' is not defined as a remote login at the server. Can anyone help me to find a solution. Thanks Satish...

  1. #1

    Default Executing sp of another Server

    Hello all,
    Can we execute stored procedure residing in another
    server. If yes how?
    I tried this way:
    Exec Server1.Database.DBO.ProcName

    But it gave me this error:

    Could not connect to server 'Server1' because 'sa' is not
    defined as a remote login at the server.

    Can anyone help me to find a solution.

    Thanks
    Satish


    Satish Guest

  2. #2

    Default Re: Executing sp of another Server

    You need to configure "linked servers" to do this. Refer to SQL Server BOL
    for more information on how to setup linked servers.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Satish" <com> wrote in message
    news:0ae501c355c1$58158400$gbl... 


    SriSamp Guest

  3. #3

    Default Re: Executing sp of another Server

    Hi Sri,
    I have done the prerequisites (like linked server,
    adding remote login)but still I get the same error when I
    try to execute a SP of another server.

    Steps:
    sp_addlinkedserver 'ServerName'
    sp_addremoteloging 'ServerName', 'loginName'

    Can you tell me whether any other steps are required.

    Thanks
    Satish

     
    to SQL Server BOL [/ref]
    not 
    >
    >
    >.
    >[/ref]
    Satish Guest

  4. #4

    Default Re: Executing sp of another Server

    Satish,

    I think you are using the wrong sp_ for the security mapping:
    From BOL:
    "sp_addremotelogin: Adds a new remote login ID on the local server, allowing
    remote servers to connect and execute remote procedure calls."

    Check out sp_addlinkedserverlogin:
    From BOL:
    "sp_addlinkedsrvlogin Creates or updates a mapping between logins on the
    local instance of Microsoft® SQL ServerT and remote logins on the linked
    server."

    Another issue is how you execute the stored procedure:
    Do you try to execute the stored procedure with OPENQUERY:
    SELECT * FROM OPENQUERY(<linked_server_name', 'EXEC db_name.dbo.myproc')
    or by using a 4 part name:
    EXEC linked_server_name.db_name.owner.myproc ?

    In order to be able to execute stored procedures on the linked server using
    a 4 part name,
    you need to set the server option 'RPC Out' in the linked server properties
    / server options tab in EM
    or use:
    EXEC sp_serveroption 'linked_server_name', 'rpc out', 'true'

    HTH

    "Satish" <com> wrote in message
    news:3c7c01c355d2$21208ba0$gbl... 
    > because you are 
    > user "sp_addremoteloging 'ServerName', 'loginName" . 
    > executes successfully. [/ref]
    > I [/ref]
    > Refer [/ref]
    > another 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Amy Guest

Similar Threads

  1. Executing EXE
    By Yoshitha in forum ASP.NET Security
    Replies: 5
    Last Post: July 6th, 07:24 PM
  2. Server.Execute call not executing
    By Brian Burgess in forum ASP
    Replies: 7
    Last Post: September 9th, 07:27 AM
  3. executing .exe on NT from remote Unix server
    By Matt Garrish in forum PERL Miscellaneous
    Replies: 1
    Last Post: August 19th, 08:58 PM
  4. problem while executing php with thttpd server
    By Amarnath R in forum PHP Development
    Replies: 1
    Last Post: August 7th, 12:22 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139