L'exécution d'une procédure stockée distante dans sp_executesql

Je vais essayer d'obtenir IDENT_CURRENT de la valeur sur le serveur lié. J'ai créé une procédure stockée sp_current_identity sur le serveur distant qui a le paramètre de sortie. 

CREATE  PROCEDURE [dbo].[sp_current_identity] ( @strTableName nvarchar(255), @intRowId int OUTPUT )
AS
BEGIN
select IDENT_CURRENT(@strTableName)
END

Après que j'ai créé deux synonymes:sp_current_identity et sometable.

J'ai besoin pour exécuter sp_current_identity sp_executesql (je suis de la création d'une coutume DataAtapter de travailler avec des synonymes via LLBLGEN 3.1). Veuillez voir l'exemple suivant:

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

Il fonctionne très bien lorsque ce code est exécuté sur le serveur distant (où sp_current_identity est une procédure stockée locale), mais il provoque une exception lorsque le code est exécuté sur le serveur local.
Voici le message d'erreur:

Procédure ou la fonction 'sp_current_identity' attend le paramètre '@strTableName", qui n'a pas été fourni.

Merci pour votre aide!

  • Votre code est incorrect car le contrôle de la IDENT_CURRENT après l'insertion est incorrect. En vertu de l', même légère, de la simultanéité de votre script de démarrage de retour de la dernière identité inséré par l'autre session(s) et votre application ne peut pas fonctionner correctement ensuite. Utilisation INSERT INTO ... OUTPUT inserted.idcolumn à la place.
  • Merci pour la réponse rapide. Il semble que je ne peux pas utiliser "INSERT INTO ... SORTIE inséré.idcolumn" parce que "sometable" est un synonyme de la table distante.
  • Toujours pas sûr de savoir pourquoi vous ne pouvez pas créer la procédure stockée à l'autre extrémité, que d'effectuer l'insertion et de sortie SCOPE_IDENTITY()?
InformationsquelleAutor Alex | 2011-08-01
  1. 4

    Avez-vous pensé à l'exécution de EXEC remoteserver.database.dbo.sp_executesql 'dynamic SQL'; au lieu d'essayer d'exécuter le SQL dynamique localement? Le sp_current_identity procédure doit exister à l'endroit où la requête est exécutée, pas en où la requête est appelée à partir d'.

    • Malheureusement, aucune. Je ne suis pas en mesure d'utiliser à distance le nom du serveur dans SQL. Je suis à l'aide de synonymes pour travailler à distance avec des tables et procédures stockées.
    • Mais c'est uniquement parce que vous êtes à l'aide de synonymes? Est-ce comme disant que je ne peut pas acheter une voiture qui prend de l'essence sans plomb parce que j'ai un peut de diesel que je ne veux pas aller à déchets?
    • Merci pour votre aide, Aaron. Je suis limité dans l'écriture sql, en raison du système actuel de conception. J'ai juste pensé que peut-être il est possible d'exécuter la procédure stockée distante qui a un paramètre de sortie via sp_executesql. Ce qui devrait résoudre mon problème.
    • demande doit travailler avec des environnements différents, de sorte que, les serveurs distants sont encapsulés par des synonymes
    • Donc, l'ensemble de vos environnements différents, et chaque base de données sur chaque serveur, vont également avoir une procédure stockée appelée sp_current_identity qui prend exactement les mêmes paramètres et se comporte exactement de la même façon?
    • qui est correct. Maintenant, je suis en train d'étudier la création d'une table dans la base de données locale qui va stocker un nom de serveur distant et nom de base de données. Dans ce cas, il est possible de modifier la chaîne de connexion au moment de l'exécution et de travailler avec le serveur distant directement.
    • J'ai créé un tableau mentionné ci-dessus et mon problème a été résolu. Merci pour l'aide!

    InformationsquelleAutor Aaron Bertrand
  2. 0

    J'ai trouvé que j'avais monter mon appel dynamique pour le serveur distant en deux étapes. J'ai essayé d'obtenir l'ID de Base de données:

    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
    InformationsquelleAutor RelativitySQL