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!

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

Comments

[Alex]

Unfortunately, no. I'm not able to use remote server name in the SQL. I'm using synonyms to work with remote tables and stored procedures.

[Adir D]

But this is solely because you're using synonyms? Is this like saying I can't buy a car that takes unleaded gasoline because I have a can of diesel that I don't want to go to waste?

[Alex]

Thanks for your help, Aaron. I am limited in writing sql, because of current system design. I just thought that maybe there is some way to execute remote stored procedure that has an output parameter via sp_executesql. That should solve my issue.

[Alex]

application should work with different environments, so, remote servers are encapsulated via synonyms

[Adir D]

So all of your different environments, and each database on each server, are all going to have a stored procedure called sp_current_identity that takes the exact same parameters and behaves in the exact same way?

[Alex]

that is correct. Now I'm considering creating a table in the local database that will store a remote server name and database name. In this case it's possible to change connection string at runtime and work with remote server directly.

[Alex]

I've created a table mentioned above and my issue has been resolved. Thanks for help!