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.
我试图在链接服务器上获取IDENT_CURRENT值。我在具有输出参数的远程服务器上创建了一个存储过程sp_current_identity。
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.
之后我创建了两个同义词:sp_current_identity和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:
我需要使用sp_executesql执行sp_current_identity(我正在创建一个自定义DataAtapter，通过LLBLGEN 3.1与同义词一起工作)。请看下面的例子:
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:
当在远程服务器上执行此代码时(其中sp_current_identity是本地存储过程)，它可以正常工作，但是当代码在本地服务器上执行时，它会导致异常。这是错误:
Procedure or function 'sp_current_identity' expects parameter '@strTableName', which was not supplied.
过程或函数'sp_current_identity'期望参数'@strTableName'，这是不提供的。
Thanks for your help!
谢谢你的帮助!
2 个解决方案
#1
4
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.
您是否考虑过运行EXEC remoteserver.database.dbo。sp_executesql动态SQL的;而不是尝试在本地执行动态SQL ?sp_current_identity过程必须存在于查询实际执行的位置，而不是查询的调用位置。
#2
0
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:
我发现我必须用两个步骤组装对远程服务器的动态调用。我试图获取数据库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 + ''''''
SET @SPSQL = '
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
#1
4
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.
您是否考虑过运行EXEC remoteserver.database.dbo。sp_executesql动态SQL的;而不是尝试在本地执行动态SQL ?sp_current_identity过程必须存在于查询实际执行的位置，而不是查询的调用位置。
#2
0
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:
我发现我必须用两个步骤组装对远程服务器的动态调用。我试图获取数据库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 + ''''''
SET @SPSQL = '
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