Executing remote stored procedure within sp_executesql

Question

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 [dbo].[sp_current_identity] ( @strTableName nvarchar(255), @intRowId int OUTPUT )
AS
BEGIN
select IDENT_CURRENT(@strTableName)
END

After that I have created two synonyms:sp_current_identity and sometable.

I need to execute sp_current_identity using sp_executesql (I'm creating a custom DataAtapter to work with synonyms via LLBLGEN 3.1). Please see the following example:

declare @p4 int
set @p4=NULL
exec sp_executesql N'SET XACT_ABORT ON; INSERT INTO [db].[dbo].[sometable] ([FieldName], [TableName], [UserField]) VALUES (@p1, @p3, @p4) ;
exec dbo.sp_current_identity @p5, @p2 
;SET XACT_ABORT OFF',N'@p1 varchar(50),@p2 int output,@p3 varchar(50),@p4 varchar(50), @p5 varchar(200)',
@p1='test24',@p2=@p4 output,@p3='test24',@p4='test5',@p5='sometable'
select @p4

It works fine when this code is executed on the remote server (where sp_current_identity is local stored procedure), but it causes an exception when the code is executed on the local server. Here is the error:

Procedure or function 'sp_current_identity' expects parameter '@strTableName', which was not supplied.

Thanks for your help!

Answer 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.

Answer 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