Comment imprimer un message à partir de la fonction SQL CLR?

, Vous devriez juste être capable de faire:

SqlContext.Pipe.Send("hello world");

Si vous exécutez ceci dans un UDF CLR, {[1] } sera toujours null comme vous l'avez découvert. Sans un SqlPipe valide, Je ne crois pas que vous puissiez faire ce que vous voulez.

Si c'est uniquement à des fins de débogage, vous pouvez toujours ouvrir un fichier dans le code géré et y écrire votre sortie. Cela nécessite que votre assembly ait l'autorisation EXTERNAL_ACCESS, et cela nécessite que la base de données soit marquée comme digne de confiance. Pas nécessairement quelque chose que je ferais ou recommanderais.

Ahh je vois... Jsut pour clarifier: si vous avez un SqlFunction alors SqlContext.Pipe n'est pas disponible, cependant dans un SqlProcedure Il est et vous pouvez utiliser Send () pour écrire des messages.

Je n'ai toujours pas trouvé de moyen de générer des informations à partir D'une fonction SQL en dehors d'un message d'exception.

Vous pouvez essayer de mettre ces informations via la procédure stockée "xp_logevent". Vous pouvez définir vos informations de débogage "information", "avertissement" ou "erreur" lors de différents niveau. J'ai également essayé de mettre ces informations de débogage/erreur dans le journal des événements, mais cela nécessite un peu de configuration à la sécurité, que je doute de ne pas pouvoir utiliser en production.

Fonctions SQLCLR -- les fonctions définies par L'utilisateur scalaires (UDF), les fonctions à valeur de Table (TVFs), les agrégats définis par l'utilisateur (UDAs) et les méthodes des Types définis par L'utilisateur (UDTs) -- lors de l'utilisation de la connexion de contexte (c'est-à-dire ConnectionString = "Context Connection = true;"), sont liées par la plupart des mêmes restrictions que les fonctions T-SQL Cependant, vous avez quelques options.

Avant d'arriver à ces options, il devrait être indiqué que:

• Vous n'avez pas besoin de passer à l'utilisation d'une Procédure Stockée. Si vous voulez une fonction puis coller avec une fonction.

• Ajouter un paramètre "debug" et changer la sortie pour cela semble un peu extrême puisque les fonctions UDFs (T-SQL et SQLCLR) ne permettent pas de surcharger. Donc le paramètre debug sera toujours dans la signature. Si vous voulez déclencher le débogage, créez simplement une table temporaire appelée #debug (ou quelque chose comme ça) et testez via SELECT OBJECT_ID(N'tempdb..#debug'); en utilisant "Context Connection = true;" pour le ConnectionString (qui est rapide et peut être fait en mode sans échec et fait partie de la même session afin qu'il puisse voir la table temporaire). Obtenez le résultat de cela à partir de if (SqlCommand.ExecuteScalar() == DBNull.Value).

• Veuillez ne pas utiliser de variable globale (c'est-à-dire statique). c'est beaucoup plus compliqué que nécessaire, et nécessite (généralement) que l'assemblage soit réglé sur UNSAFE, ce qui devrait être évité si possible.

Donc, si vous pouvez au moins à l'assemblée de EXTERNAL_ACCESS, alors vous avez un quelques options. Et cela ne pas nécessite de définir la base de données sur TRUSTWORTHY ON. C'est un malentendu très commun (et malheureux). Vous avez juste besoin de signer l'assembly (ce qui est une bonne pratique de toute façon), puis de créer une clé asymétrique (dans [master]) à partir de la DLL, puis de créer un Login basé sur cette clé asymétrique, et enfin d'accorder le Login EXTERNAL ACCESS ASSEMBLY. Après avoir fait cela (une fois), vous pouvez effectuer l'une des opérations suivantes:

• Ecrire les messages dans un fichier en utilisant fichier.AppendAllText (chemin de chaîne, contenu de chaîne) . Bien sûr, si vous n'avez pas accès au système de fichiers, ce n'est pas aussi utile. S'il existe un lecteur partagé sur le réseau accessible, tant que le compte de service du service SQL Server a l'autorisation de créer et d'écrire des fichiers sur ce partage, cela fonctionnera. S'il y a un partage auquel le compte de service n'est pas autorisé, mais que votre compte de domaine / Active Directory le fait, vous pouvez l'envelopper File.AppendAllText appel:

  using (WindowsImpersonationContext _Impersonate = 
                        SqlContext.WindowsIdentity.Impersonate())
  {
     File.AppendAllText("path.txt", _DebugMessage);
      _Impersonate.Undo();
  }
  

• Connectez-vous à SQL Server et écrivez les messages dans une table. Il peut s'agir du serveur SQL actuel / local ou de tout autre serveur SQL. Vous pouvez créer une table dans [tempdb] afin qu'elle soit automatiquement nettoyée la prochaine fois que SQL Server est redémarré, mais sinon elle dure jusqu'à ce moment-là, ou jusqu'à ce que vous la laissiez tomber. Faire une connexion régulière / externe vous permet de faire des instructions DML. Ensuite, vous pouvez sélectionner dans la table que vous exécutez la fonction.

• Ecrire les messages dans une variable d'environnement. Les variables d'environnement ne sont pas exactement limitées en taille depuis Vista / Server 2008, bien qu'elles ne gèrent pas vraiment les retours à la ligne. Mais tout ensemble de variables à partir du code.net survivra également jusqu'à ce que le service SQL Server soit redémarré. Et vous pouvez ajouter un message en lisant la valeur actuelle et en concaténant le nouveau message à la fin. Quelque chose comme:

  {
    string _Current = System.Environment.GetEnvironmentVariable(_VariableName,
                                    EnvironmentVariableTarget.Process);
  
    System.Environment.SetEnvironmentVariable(
        _VariableName,
        _Current + _DebugMessage,
        EnvironmentVariableTarget.Process);
  }
  

, Il convient de noter que dans chacun de ces 3 dans certains cas, il est supposé que le test est effectué de manière mono-thread. Si la fonction s'exécute à partir de plusieurs sessions en même temps, vous avez besoin d'un moyen de séparer les messages. Dans ce cas, vous pouvez obtenir le courant "transaction_id" (toutes les requêtes, même sans BEGIN TRAN sont une transaction!) qui devrait être cohérent pour toute exécution particulière(à travers plusieurs utilisations dans la même fonction ainsi que si la fonction est appelée par chaque ligne sur plusieurs lignes). Vous pouvez utiliser cette valeur en tant que préfixe pour les messages si vous utilisez les méthodes de fichier ou de variable d'environnement, ou en tant que champ séparé si vous stockez dans une table. Vous pouvez obtenir la transaction en procédant comme suit:

int _TransactionID;

using (SqlConnection _Connection = new SqlConnection("Context Connection = true;"))
{
    using (SqlCommand _Command = _Connection.CreateCommand())
    {
        _Command.CommandText = @"
SELECT transaction_id
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;
";

        _Connection.Open();
        _TransactionID = (int)_Command.ExecuteScalar();
    }
}

Informations supplémentaires sur les fonctions T-SQL et SQLCLR

La liste suivante a été initialement tirée de la page MSDN pour créer des fonctions définies par L'utilisateur (moteur de base de données) , puis éditée par moi, comme indiqué, pour refléter les différences entre les fonctions T-SQL et Fonctions SQLCLR:

• les fonctions définies par l'utilisateur ne peuvent pas être utilisées pour effectuer des actions qui modifient l'état de la base de données.
• les fonctions définies par l'utilisateur ne peuvent pas contenir une clause OUTPUT INTO qui a une table comme cible.
• les fonctions définies par l'utilisateur ne peuvent pas renvoyer plusieurs ensembles de résultats. Utilisez une procédure stockée si vous devez renvoyer plusieurs ensembles de résultats.
• la gestion des erreurs est limitée dans une fonction définie par l'utilisateur. Un UDF ne prend pas en charge TRY ... CATCH, @ @ ERROR, ou RAISERROR. [Note: Ceci est en termes de T-SQL, soit natif ou soumis à partir d'une fonction SQLCLR. Vous pouvez utiliser try / catch / finally / jeter dans .NET code. ]
• les instructions SET ne sont pas autorisées dans une fonction définie par l'utilisateur.
• la clause FOR XML n'est pas autorisée
• fonctions définies par l'Utilisateur peuvent être imbriquées; ... Le niveau d'imbrication est incrémenté lorsque la fonction appelée démarre l'exécution et décrémenté lorsque la fonction appelée termine l'exécution. Défini par l'utilisateur les fonctions peuvent être imbriquées jusqu'à 32 niveaux.
• les instructions de Service Broker suivantes ne peuvent pas être incluses dans la définition D'une fonction définie par L'utilisateur Transact-SQL:
  • DÉMARRER LA CONVERSATION DE DIALOGUE
  • FIN DE LA CONVERSATION
  • OBTENIR UN GROUPE DE CONVERSATION
  • DÉPLACER LA CONVERSATION
  • recevoir
  • envoyer

Ce qui suit concerne à la fois les fonctions T-SQL et les fonctions SQLCLR:

• impossible D'utiliser PRINT
• ne peut pas appeler NEWID () [Eh bien, sauf si vous SELECT NEWID() depuis une vue. Mais dans le code. Net, vous pouvez utiliser Guid.NewGuid(). ]

Ce qui suit ne concerne que les fonctions T-SQL:

• les fonctions définies par l'utilisateur ne peuvent pas appeler une procédure stockée, mais peuvent appeler une procédure stockée étendue.
• les fonctions définies par l'utilisateur ne peuvent pas utiliser de tables dynamiques SQL ou temporaires. Les variables de Table sont autorisées.

En revanche, les fonctions SQLCLR peuvent:

• exécute les procédures stockées, tant qu'elles sont en lecture seule.
• faire usage de SQL dynamique (tout SQL soumis à partir de SQLCLR est ad hoc / dynamique par sa nature même).
• sélectionnez à partir de tables temporaires.