locked
Execute a stored procedure of another server RRS feed

  • Question

  • Hi,

    I have to execute a stored procedure of another server. Can someone suggest me the best way to do that.

     

    Thanks,

    Ved

    Monday, April 16, 2007 2:28 PM

Answers

  • Just create Linked Server and use 4-part name of you stored procedure:

    Code Snippet

    EXEC <remote_server_name_with_instance>.<remote_database_name>.<remote_schema>.<remote_sp>

     

    Monday, April 16, 2007 2:51 PM
  • Try:

    Code Snippet

    exec [p-srv1].[msdb].[dbo].[sp_name]

     

    Wednesday, April 18, 2007 8:57 AM

All replies

  • Just create Linked Server and use 4-part name of you stored procedure:

    Code Snippet

    EXEC <remote_server_name_with_instance>.<remote_database_name>.<remote_schema>.<remote_sp>

     

    Monday, April 16, 2007 2:51 PM
  • As Konstantin indicated, the syntax is:  ServerName.DatabaseName.SchemaName.ProcedureName

     

    SchemaName is 'usually' [dbo] in SQL 2000.

     

    Whoever executes the remote procedure will have to have LOGIN permission to the remote server, be given permissions to access the remote database, and EXECUTE permission to the stored procedure.

    Monday, April 16, 2007 4:56 PM
  • Use Linked Server

     

     

    Code Snippet

    EXEC sp_addlinkedserver  @server = 'SERVER', @provider = 'SQLOLEDB.1', @srvproduct = '', @provstr = 'Privider=SQLOLEDB.1;Data Source=TargetServer;Initial Catalog=Database'

    go
    Exec sp_addlinkedsrvlogin @rmtsrvname = 'SERVER', @useself = true, @locallogin = null, @rmtuser = 'Userid', @rmtpassword = 'Password'

     


    On your source database you can use..

    Code Snippet

    Select * From OpenQuery(MyRemoteServer, 'Exec YourSP')

    --OR

    Exec MyRemoteServer.DatabaseName.dbo.YourSP

     

    Tuesday, April 17, 2007 8:18 AM
  • when i try to execute "exec p-srv1.msdb.dbo.sp_name" i am getting following error:

     

    Incorrect syntax near "-". (Actually my linked server name has "-")

     

    what i should do now.

     

    Thanks,

    Ved

    Wednesday, April 18, 2007 8:27 AM
  • Try:

    Code Snippet

    exec [p-srv1].[msdb].[dbo].[sp_name]

     

    Wednesday, April 18, 2007 8:57 AM
  • Thanks a lot
    Wednesday, April 18, 2007 11:32 AM
  • I have successfully executed stored procedure on my local server but when i try the same on remote server, i am getting following error:

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

     

    what should i do now.

     

    Thanks,

    Ved

     

    Wednesday, April 18, 2007 12:30 PM
  • Did you configure credentials for linked server?
    Wednesday, April 18, 2007 1:32 PM
  • yes, i have chosen "Be made using the login's current security context" for linked server
    Wednesday, April 18, 2007 2:14 PM
  • > "Be made using the login's current security context"

    As result you connect with user account your SQL Server.

    Try man remote login to your local login

     

    Wednesday, April 18, 2007 2:40 PM
  • Is your computer a member of a domain? OR a Workgroup?

     

    Are you attempting to make this connection through a web page?

    Wednesday, April 18, 2007 2:47 PM
  • I don't have sql server authentication login information. can it be done without it?

     

    Thanks,

    Ved

    Friday, April 20, 2007 5:45 AM
  • 1. You could run both SQL Server instances under one domain user account

    2. You could try create SQL Server creadential http://msdn2.microsoft.com/de-de/library/ms189522.aspx if you use SQL Server 2005

     

    Friday, April 20, 2007 10:25 AM