Executing Remote Stored Procedure Within Sp_executesql

I'm trying to get IDENT_CURRENT value on the linked server. I've created a stored procedure sp_current_identity on the remote server that has output parameter. CREATE PROCEDURE

Solution 1:

Have you considered running EXEC remoteserver.database.dbo.sp_executesql 'dynamic SQL'; instead of trying to execute the dynamic SQL locally? The sp_current_identity procedure has to exist at the place where the query is actually executed, not where the query is called from.

Solution 2:

I found that I had to assemble my dynamic call to the remote server in two steps. I was trying to get the Database ID:

DECLARE@sql nvarchar(4000)
DECLARE@parmDefinition nvarchar(500)

SET@parmDefinition= N'@retvalOUTside int OUTPUT'SET@sql='SELECT TOP 1 @retvalOUT = database_id FROM ['+@ServerName+'].master.sys.databases WHERE name = '''''+@dbname+''''''DECLARE@SPSQL nvarchar(4000) ='
    DECLARE @DBID INT;
    DECLARE @parmDefinition nvarchar(500); 
    SET @parmDefinition = N''@retvalOUT int OUTPUT''; 
    DECLARE @SQLinside nvarchar(400) ='''+@sql+''';
    EXEC ['+@ServerName+'].master.dbo'+'.sp_executeSQL @SQLinside, @parmDefinition, @retvalOUT = @retvalOUTside OUTPUT'EXEC sp_executeSQL @SPSQL, @parmDefinition, @retvalOUTside=@DBID OUTPUT