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!

Your code is incorrect because checking for `IDENT_CURRENT` after insert is incorrect. Under even mild concurrency your script will start returning the last identity inserted by other session(s) and your application cannot possibly work correctly then. Use `INSERT INTO ... OUTPUT inserted.idcolumn` instead. — Remus Rusanu, Aug 01 '11 at 21:00
Thanks for the quick reply. It seems that I cannot use "INSERT INTO ... OUTPUT inserted.idcolumn" because 'sometable' is a synonym to the remote table. — Alex, Aug 01 '11 at 21:55
Still not sure why you can't create the stored procedure on the other end, have that perform the insert and output SCOPE_IDENTITY()? — Aaron Bertrand, Aug 01 '11 at 22:02

score 7 · Answer 1 · answered Aug 01 '11 at 20:52

7

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.

answered Aug 01 '11 at 20:52

Aaron Bertrand

272,866
37
466
490

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. – Alex Aug 01 '11 at 22:00
2

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? – Aaron Bertrand Aug 01 '11 at 22:03
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 Aug 01 '11 at 22:49
application should work with different environments, so, remote servers are encapsulated via synonyms – Alex Aug 01 '11 at 22:58
1

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? – Aaron Bertrand Aug 01 '11 at 23:09
1

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 Aug 02 '11 at 06:27
1

I've created a table mentioned above and my issue has been resolved. Thanks for help! – Alex Aug 02 '11 at 08:49

score 1 · Answer 2 · edited Dec 22 '21 at 20:30

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

Executing remote stored procedure within sp_executesql

2 Answers2

Linked