SQL Server Performance

Linked server SP call from another SP

Discussion in 'SQL Server 2005 General Developer Questions' started by Sheetal, Oct 27, 2007.

  1. Sheetal New Member

    Hi Sir,
    I want to call one linked server stored procedure from another stored procedure.
    --BATCH FOR LINKED SERVER SETUP
    SET @IBatchNum = 2

    EXEC PR_Linked_Server
    @VServer = 'Linked_Amisys'
    ,@VSrvProduct = ''
    ,@VProvider = 'SQLOLEDB'
    ,@VDataSrc = 'DTCORPMXDV9'
    ,@VCatalog = 'pubs'
    ,@VCurrentUsr = 'CORPajohri'
    ,@VServerUsr = 'dba'
    ,@VServerPwd = 'dba'
    END TRY
    The linked server was not created When I executed the query. However, I was able to create the linked server when I executed this query(batch 2) alone.
    Is there any limitation of SP which I am not aware of?
    --Sheetal
  2. satya Moderator

  3. sudiptakr New Member

    the user should have access to this SP. please check because manually you have to give access to desire object
  4. deepu_v04 New Member

    USE MASTER
    GO
    EXEC master.dbo.sp_addlinkedserver
    @server = 'Linked_Amisys', --This is an ODBC connection that is setup on the machine.
    @srvproduct = '',
    @provider = 'SQLNCLI',
    @datasrc = 'Linked_Amisys',
    @catalog = 'database_name'
    go
    EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname=N'Linked_Amisys',
    @useself=N'False',
    @locallogin=NULL,
    @rmtuser=N'dba',
    @rmtpassword='dba'

    go
    then call the stored procedure using
    Method 1 : Remote Stored Procedure can be called as four part name:
    Syntax: EXEC [RemoteServer] .DatabaseName.DatabaseOwner.StoredProcedureName ‘Params’
    Example: EXEC [DEVSQL] .AdventureWorks.dbo.uspGetEmployeeManagers ‘42′

    Method 2 : Use Distributed Query functions (OpenQuery())
    Syntax: SELECT * FROM OPENQUERY([RemoteServer],DatabaseName.DatabaseOwner.StoredProcedureName)
    Example: SELECT * FROM OPENQUERY([DEVSQL],AdventureWorks.dbo.spAdmin_GetAllUsers)


Share This Page