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
the user should have access to this SP. please check because manually you have to give access to desire object
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)