Partager via

Programmation Moteur de base de données procédures stockées étendues

  • Article

S'applique à : SQL Server

Important

Cette fonctionnalité sera supprimée dans une version future de SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité. Utilisez plutôt l’intégration clR.

Fonctionnement des procédures stockées étendues

Le processus de fonctionnement d'une procédure stockée étendue est le suivant :

  1. Lorsqu’un client exécute une procédure stockée étendue, la requête est transmise au format TDS (Simple Object Access Protocol) de l’application cliente vers SQL Server.

  2. SQL Server recherche la DLL associée à la procédure stockée étendue et charge la DLL si elle n’est pas déjà chargée.

  3. SQL Server appelle la procédure stockée étendue demandée (implémentée en tant que fonction à l’intérieur de la DLL).

  4. La procédure stockée étendue transmet les jeux de résultats et retourne les paramètres au serveur via l'API de la procédure stockée étendue.

Par le passé, les services ODS (Open Data Services) permettaient d'écrire des applications serveur, telles que des passerelles à des environnements de base de données non-SQL Server. SQL Server ne prend pas en charge les parties obsolètes de l’API Open Data Services. La seule partie de l’API Open Data Services d’origine toujours prise en charge par SQL Server est les fonctions de procédure stockée étendue. L’API a donc été renommée en API de procédure stockée étendue.

Avec l’émergence de requêtes distribuées et l’intégration du CLR, la nécessité pour les applications d’API de procédure stockée étendue a été largement remplacée.

Si vous disposez d’applications de passerelle existantes, vous ne pouvez pas utiliser les opends60.dll applications fournies avec SQL Server pour exécuter les applications. Les applications de passerelle ne sont plus prises en charge.

Procédures stockées étendues et intégration clR

L’intégration clR offre une alternative plus robuste à l’écriture d’une logique côté serveur qui était difficile à exprimer ou impossible à écrire dans Transact-SQL. Dans les versions antérieures de SQL Server, les procédures stockées étendues (XPs) fournissaient le seul mécanisme disponible pour les développeurs d’applications de base de données à écrire ce code.

Avec l’intégration du CLR, la logique utilisée pour être écrite sous la forme de procédures stockées est souvent mieux exprimée en tant que fonctions table, ce qui permet aux résultats construits par la fonction d’être interrogés dans les instructions en SELECT les incorporant dans la FROM clause.

Pour plus d’informations, consultez la vue d’ensemble de l’intégration clR.

Caractéristiques d’exécution des procédures stockées étendues

L'exécution d'une procédure stockée étendue présente les caractéristiques suivantes :

  • La fonction de procédure stockée étendue est exécutée dans le contexte de sécurité de SQL Server.

  • La fonction de procédure stockée étendue s’exécute dans l’espace de processus de SQL Server.

  • Le thread associé à l'exécution de la procédure stockée étendue est le même que celui utilisé pour la connexion cliente.

Important

Avant d’ajouter des procédures stockées étendues au serveur et d’accorder des autorisations d’exécution à d’autres utilisateurs, l’administrateur système doit examiner soigneusement chaque procédure stockée étendue pour s’assurer qu’elle ne contient pas de code dangereux ou malveillant.

Une fois la DLL de procédure stockée étendue chargée, la DLL reste chargée dans l’espace d’adressage du serveur jusqu’à ce que SQL Server soit arrêté ou que l’administrateur décharge explicitement la DLL à l’aide DBCC <DLL_name> (FREE)de .

La procédure stockée étendue peut être exécutée à partir de Transact-SQL en tant que procédure stockée à l’aide de l’instruction EXECUTE :

EXECUTE @retval = xp_extendedProcName @param1, @param2 OUTPUT;

Paramètres

@ retval

Valeur de retour.

@ param1

Paramètre d'entrée.

@ param2

Paramètre d’entrée/sortie.

Attention

Les procédures stockées étendues offrent des améliorations des performances et étendent la fonctionnalité SQL Server. Toutefois, étant donné que la DLL de procédure stockée étendue et SQL Server partagent le même espace d’adressage, une procédure problématique peut affecter le fonctionnement de SQL Server. Bien que les exceptions levées par la DLL de procédure stockée étendue soient gérées par SQL Server, il est possible d’endommager les zones de données SQL Server. Par précaution de sécurité, seuls les administrateurs système SQL Server peuvent ajouter des procédures stockées étendues à SQL Server. Ces procédures doivent être testées entièrement avant d'être installées.

Envoyer des jeux de résultats au serveur avec l’API de procédure stockée étendue

Lors de l’envoi d’un jeu de résultats à SQL Server, la procédure stockée étendue doit appeler l’API appropriée comme suit :

  • La srv_sendmsg fonction peut être appelée dans n’importe quel ordre avant ou après toutes les lignes (le cas échéant) sont avec srv_sendrow. Tous les messages doivent être envoyés au client avant l’envoi de l’état d’achèvement avec srv_senddone.

  • La srv_sendrow fonction est appelée une fois pour chaque ligne envoyée au client. Toutes les lignes doivent être envoyées au client avant que les messages, les valeurs d’état ou les états d’achèvement soient envoyés avec srv_sendmsg, l’argument srv_status de srv_pfield, ou srv_senddone.

  • L’envoi d’une ligne dont toutes les colonnes ne sont pas définies entraîne srv_describe la levée d’un message d’erreur informationnel par l’application et le retour FAIL au client. Dans ce cas, la ligne n’est pas envoyée.

Créer des procédures stockées étendues

Une procédure stockée étendue est une fonction C/C++ avec un prototype :

SRVRETCODE xp_extendedProcName ( SRVPROC *) ;

L’utilisation du préfixe xp_ est facultative. Les noms de procédures stockées étendues respectent la casse lorsqu’ils sont référencés dans les instructions Transact-SQL, quelle que soit la page de codes/l’ordre de tri installé sur le serveur. Lorsque vous générez une DLL :

  • Si un point d’entrée est nécessaire, écrivez une DllMain fonction.

    Cette fonction est facultative. Si vous ne le fournissez pas dans le code source, le compilateur lie sa propre version, ce qui ne fait que retourner TRUE. Si vous fournissez une DllMain fonction, le système d’exploitation appelle cette fonction lorsqu’un thread ou un processus s’attache ou se détache de la DLL.

  • Toutes les fonctions appelées hors de la DLL (toutes les procédures stockées étendues Efunctions) doivent être exportées.

    Vous pouvez exporter une fonction en répertoriant son nom dans la EXPORTS section d’un .def fichier, ou vous pouvez préfixer le nom de la fonction dans le code source avec __declspec(dllexport), une extension du compilateur Microsoft (__declspec() commence par deux traits de soulignement).

Ces fichiers sont requis pour la création d'une DLL de procédure stockée étendue.

Fichier Description
srv.h Fichier d'en-tête de l'API de procédure stockée étendue
opends60.lib Importer la bibliothèque pour opends60.dll

Pour créer une DLL de procédure stockée étendue, créez un projet de type bibliothèque de liens dynamiques. Pour plus d'informations sur la création d'une DLL, consultez la documentation relative à l'environnement de développement.

Toutes les DLL de procédure stockée étendue doivent implémenter et exporter la fonction suivante :

__declspec(dllexport) ULONG __GetXpVersion()
{
   return ODS_VERSION;
}

__declspec(dllexport) est une extension de compilateur spécifique à Microsoft. Si votre compilateur ne prend pas en charge cette directive, vous devez exporter cette fonction dans votre DEF fichier sous la EXPORTS section.

Lorsque SQL Server est démarré avec l’indicateur -T260 de trace ou si un utilisateur disposant de privilèges d’administrateur système s’exécute DBCC TRACEON (260), et si la DLL de procédure stockée étendue ne prend pas en charge __GetXpVersion(), le message d’avertissement suivant est imprimé dans le journal des erreurs (__GetXpVersion() commence par deux traits de soulignement).

Error 8131: Extended stored procedure DLL '%' does not export __GetXpVersion().

Si la DLL de procédure stockée étendue exporte __GetXpVersion(), mais que la version retournée par la fonction est inférieure à la version requise par le serveur, un message d’avertissement indiquant la version retournée par la fonction et la version attendue par le serveur est imprimée dans le journal des erreurs. Si vous recevez ce message, vous retournez une valeur incorrecte à partir de __GetXpVersion(), ou vous compilez avec une version antérieure de srv.h.

Remarque

SetErrorMode, une fonction Win32 ne doit pas être appelée dans les procédures stockées étendues.

Les procédures stockées étendues de longue durée doivent appeler srv_got_attention régulièrement, afin que la procédure puisse se terminer si la connexion est tuée ou si le lot est abandonné.

Pour déboguer une DLL de procédure stockée étendue, copiez-la dans le répertoire SQL Server \Binn . Pour spécifier l’exécutable de la session de débogage, entrez le chemin d’accès et le nom de fichier du fichier exécutable SQL Server (par exemple). C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn\sqlservr.exe Pour plus d’informations sur sqlservr les arguments, consultez l’application sqlservr.

Ajouter une procédure stockée étendue à SQL Server

Une DLL qui contient des fonctions de procédure stockée étendue agit comme une extension à SQL Server. Pour installer la DLL, copiez le fichier dans un répertoire, par exemple celui qui contient les fichiers DLL SQL Server standard (C:\Program Files\Microsoft SQL Server\MSSQL16.0.<x>\MSSQL\Binn par défaut).

Une fois la DLL de procédure stockée étendue copiée sur le serveur, un administrateur système SQL Server doit s’inscrire à SQL Server chaque fonction de procédure stockée étendue dans la DLL. Cette opération est effectuée à l’aide de la sp_addextendedproc procédure stockée système.

Important

L’administrateur système doit examiner minutieusement une procédure stockée étendue pour s’assurer qu’elle ne contient pas de code dangereux ou malveillant avant de l’ajouter au serveur et d’accorder des autorisations d’exécution à d’autres utilisateurs. Validez toutes les entrées utilisateur. Ne concatènez pas l’entrée utilisateur avant de la valider. N'exécutez jamais une commande élaborée à partir d'une entrée utilisateur non validée.

Le premier paramètre de sp_addextendedproc spécifie le nom de la fonction, et le deuxième paramètre spécifie le nom de la DLL dans laquelle réside cette fonction. Vous devez spécifier le chemin d’accès complet de la DLL.

Remarque

Les DLL existantes qui n’ont pas été inscrites avec un chemin d’accès complet ne fonctionnent pas après la mise à niveau vers SQL Server 2005 (9.x) ou une version ultérieure. Pour corriger le problème, utilisez sp_dropextendedproc cette option pour annuler l’inscription de la DLL, puis réinscrivez-la en sp_addextendedproc, spécifiant le chemin d’accès complet.

Le nom de la fonction spécifié dans sp_addextendedproc doit être exactement identique (y compris la casse) au nom de la fonction dans la DLL. Par exemple, cette commande inscrit une fonction xp_hello, située dans une dll nommée xp_hello.dll, en tant que procédure stockée étendue SQL Server :

sp_addextendedproc 'xp_hello', 'c:\Program Files\Microsoft SQL Server\MSSQL13.0.MSSQLSERVER\MSSQL\Binn\xp_hello.dll';

Si le nom de la fonction spécifiée dans sp_addextendedproc ne correspond pas exactement au nom de la fonction dans la DLL, le nouveau nom est inscrit dans SQL Server, mais le nom n’est pas utilisable. Par exemple, bien qu’il xp_Hello soit inscrit en tant que procédure stockée étendue SQL Server située dans xp_hello.dll, SQL Server ne peut pas trouver la fonction dans la DLL si vous utilisez xp_Hello pour appeler la fonction ultérieurement.

-- Register the function (xp_hello) with an initial upper case
sp_addextendedproc 'xp_Hello', 'c:\xp_hello.dll';

-- Use the newly registered name to call the function
DECLARE @txt VARCHAR(33);
EXEC xp_Hello @txt OUTPUT;

Voici le message d’erreur :

Server: Msg 17750, Level 16, State 1, Procedure xp_Hello, Line 1
Could not load the DLL xp_hello.dll, or one of the DLLs it references. Reason: 127(The specified procedure could not be found.).

Si le nom de la fonction spécifiée correspond sp_addextendedproc exactement au nom de la fonction dans la DLL et que le classement de l’instance SQL Server ne respecte pas la casse, l’utilisateur peut appeler la procédure stockée étendue à l’aide de n’importe quelle combinaison de lettres minuscules et majuscules du nom.

-- Register the function (xp_hello)
sp_addextendedproc 'xp_hello', 'c:\xp_hello.dll';

-- The following example succeeds in calling xp_hello
DECLARE @txt VARCHAR(33);
EXEC xp_Hello @txt OUTPUT;

DECLARE @txt VARCHAR(33);
EXEC xp_HelLO @txt OUTPUT;

DECLARE @txt VARCHAR(33);
EXEC xp_HELLO @txt OUTPUT;

Lorsque le classement de l’instance SQL Server respecte la casse, SQL Server ne peut pas appeler la procédure stockée étendue si la procédure est appelée avec un cas différent. Cela est vrai même s’il a été inscrit avec exactement le même nom et le même classement que la fonction dans la DLL.

-- Register the function (xp_hello)
sp_addextendedproc 'xp_hello', 'c:\xp_hello.dll';

-- The following example results in an error
DECLARE @txt VARCHAR(33);
EXEC xp_HELLO @txt OUTPUT;

Voici le message d’erreur :

Server: Msg 2812, Level 16, State 62, Line 1

Vous n’avez pas besoin d’arrêter et de redémarrer SQL Server.

Interroger les procédures stockées étendues installées dans SQL Server

Un utilisateur authentifié SQL Server peut afficher les procédures stockées étendues actuellement définies et le nom de la DLL à laquelle chacun appartient en exécutant la sp_helpextendedproc procédure système. Par exemple, l’exemple suivant retourne la DLL à laquelle xp_hello appartient :

sp_helpextendedproc 'xp_hello';

Si sp_helpextendedproc elle est exécutée sans spécifier de procédure stockée étendue, toutes les procédures stockées étendues et leurs DLL sont affichées.

Supprimer une procédure stockée étendue de SQL Server

Pour supprimer chaque fonction de procédure stockée étendue dans une DLL de procédure stockée étendue définie par l’utilisateur, un administrateur système SQL Server doit exécuter la sp_dropextendedproc procédure stockée système, en spécifiant le nom de la fonction et le nom de la DLL dans laquelle réside cette fonction. Par exemple, cette commande supprime la fonction xp_hello, située dans une DLL nommée xp_hello.dll, à partir de SQL Server :

sp_dropextendedproc 'xp_hello';

sp_dropextendedproc ne supprime pas les procédures stockées étendues du système. Au lieu de cela, l’administrateur système doit refuser EXECUTE l’autorisation sur la procédure stockée étendue au rôle public .

Décharger une DLL de procédure stockée étendue

SQL Server charge une DLL de procédure stockée étendue dès qu’un appel est effectué à l’une des fonctions de la DLL. La DLL reste chargée jusqu’à ce que le serveur soit arrêté ou jusqu’à ce que l’administrateur système utilise l’instruction DBCC pour la décharger. Par exemple, cette commande décharge le xp_hello.dll, ce qui permet à l’administrateur système de copier une version plus récente de ce fichier dans le répertoire sans arrêter le serveur :

DBCC xp_hello(FREE);

Contenu connexe