Comment puis-je afficher le contenu complet d'une colonne de type texte ou varchar(MAX) dans SQL Server 2008 Management Studio?

Question

Dans cette base de données SQL Server 2008 en direct (version 10.0.1600), il y a une table Events, qui contient une colonne text nommée Details. (Oui, je réalise que cela devrait en réalité être une colonne varchar(MAX), mais la personne qui a mis en place cette base de données ne l'a pas fait de cette manière.)

Cette colonne contient de très grands journaux d'exceptions et de données JSON associées auxquels j'essaie d'accéder via SQL Server Management Studio, mais chaque fois que je copie les résultats de la grille dans un éditeur de texte, il les tronque à 43679 caractères.

J'ai lu à divers endroits sur Internet que vous pouvez définir votre Maximum de caractères récupérés pour les données XML dans Outils > Options > Résultats de requête > SQL Server > Résultats dans la grille sur Illimité, puis exécuter une requête comme celle-ci :

select Convert(xml, Details) from Events
where EventID = 13920

(Remarquez que les données dans la colonne ne sont pas du tout XML. Convertir la colonne en XML est simplement une solution de contournement que j'ai trouvée en faisant des recherches sur Google et que quelqu'un d'autre a utilisée pour contourner la limite que SSMS a pour récupérer des données à partir d'une colonne text ou varchar(MAX).)

Cependant, après avoir défini l'option ci-dessus, exécuté la requête, et cliqué sur le lien dans le résultat, j'obtiens toujours l'erreur suivante :

Impossible d'afficher le XML. L'erreur suivante s'est produite: La fin inattendue du fichier s'est produite. Ligne 5, position 220160.

Une solution consiste à augmenter le nombre de caractères récupérés depuis le serveur pour les données XML. Pour modifier ce paramètre, dans le menu Outils, cliquez sur Options.

Alors, une idée sur comment accéder à ces données? Est-ce que convertir la colonne en varchar(MAX) résoudrait mes problèmes?

Answer 1

SSMS permet uniquement des données illimitées pour les données XML. Ce n'est pas la valeur par défaut et doit être défini dans les options.

Une astuce qui pourrait fonctionner dans des circonstances assez limitées consiste simplement à nommer la colonne d'une manière spéciale comme ci-dessous afin qu'elle soit traitée comme des données XML.

DECLARE @S varchar(max) = 'A'

SET @S =  REPLICATE(@S,100000) + 'B' 

SELECT @S as [XML_F52E2B61-18A1-11d1-B105-00805F49916B]

Dans SSMS (au moins les versions 2012 à la version actuelle 18.3) cela affiche les résultats comme ci-dessous

En cliquant dessus, vous ouvrez les résultats complets dans le visualiseur XML. En faisant défiler vers la droite, le dernier caractère de B est préservé,

Cependant, cela présente certains problèmes significatifs. Ajouter des colonnes supplémentaires à la requête casse l'effet et des lignes supplémentaires se concatènent toutes avec la première. Enfin, si la chaîne contient des caractères tels que < l'ouverture du visualiseur XML échoue avec une erreur d'analyse.

Une manière plus robuste de faire cela qui évite les problèmes de SQL Server convertissant < en < etc ou échouant en raison de ces caractères est ci-dessous (crédit Adam Machanic ici).

DECLARE @S varchar(max)

SELECT @S = ''

SELECT @S = @S + '
' + OBJECT_DEFINITION(OBJECT_ID) FROM SYS.PROCEDURES

SELECT @S AS [processing-instruction(x)] FOR XML PATH('')

Answer 2

La solution la plus simple que j'ai trouvée est de sauvegarder la table et de visualiser le script. Pour ce faire

1. Cliquez avec le bouton droit sur votre base de données et choisissez Tâches > Générer des scripts...
2. Sur la page "Introduction", cliquez sur Suivant
3. Sur la page "Choisir les objets"
   1. Choisissez la Sélectionner des objets spécifiques de la base de données et sélectionnez votre table.
   2. Cliquez sur Suivant
4. Sur la page "Définir les options de script"
   1. Définissez le type de sortie sur Enregistrer les scripts dans un emplacement spécifique
   2. Sélectionnez Enregistrer dans un fichier et remplissez les options associées
   3. Cliquez sur le bouton Avancé
   4. Définissez Général > Types de données à scripter sur Données uniquement ou Schéma et données et cliquez sur OK
   5. Cliquez sur Suivant
5. Sur la page "Résumé", cliquez sur Suivant
6. Votre script SQL devrait être généré en fonction des options que vous avez définies dans 4.2. Ouvrez ce fichier et visualisez vos données.

Answer 3

Il semble que le XML ne soit pas bien formé. Si c'est le cas, vous ne pourrez pas le convertir en XML et par conséquent, vous êtes limité dans la quantité de texte que vous pouvez renvoyer dans Management Studio. Cependant, vous pourriez diviser le texte en plus petites parties comme ceci :

With Tally As
    (
        Select ROW_NUMBER() OVER ( ORDER BY s1.object_id ) - 1 As Num
        From sys.sysobjects As s1
            Cross Join sys.sysobjects As s2
    )
Select Substring(T1.textCol, T2.Num * 8000 + 1, 8000)
From Table As T1
    Cross Join Tally As T2
Where T2.Num <= Ceiling(Len(T1.textCol) / 8000)
Order By T2.Num

Vous devriez ensuite les combiner manuellement de nouveau.

MODIFIER

Il semble qu'il y ait certains caractères dans les données text que l'analyseur XML n'apprécie pas. Vous pourriez essayer de convertir ces valeurs en entités puis essayer l'astuce Convert(xml, data). Donc quelque chose comme :

Update Table
Set Data = Replace(Cast(Data As varchar(max)),'<','&lt;')

(J'ai dû caster en varchar(max) car la fonction replace ne fonctionnera pas sur les colonnes de type text. Il ne devrait y avoir aucune raison pour laquelle vous ne pourriez pas convertir ces colonnes de type text en varchar(max).)

Answer 4

Le type de données TEXT est obsolète et ne devrait plus être utilisé, il est pénible de sélectionner des données dans une colonne TEXT.

ntext, text, et image (Transact-SQL)

Les types de données ntext, text, et image seront supprimés dans une version future de Microsoft SQL Server. Évitez d'utiliser ces types de données dans de nouveaux développements, et prévoyez de modifier les applications qui les utilisent actuellement. Utilisez nvarchar(max), varchar(max), et varbinary(max) à la place.

Vous devez utiliser TEXTPTR (Transact-SQL) pour récupérer les données textuelles.

Voir également cet article sur La manipulation du type de données TEXT.

Answer 5

Vous êtes malchanceux, je pense. Le problème n'est pas un problème au niveau SQL comme semblent le suggérer toutes les autres réponses, mais simplement un problème d'interface utilisateur. Management Studio n'est pas censé être une interface d'accès aux données générale ou générique. Ce n'est pas là pour être votre interface, mais votre espace administratif, et il a de sérieuses limitations dans le traitement des données binaires et de grandes quantités de données texte - car les personnes l'utilisant dans le profil d'utilisation spécifié ne rencontreront pas ce problème.

Présenter de grandes données texte n'est tout simplement pas l'utilisation prévue.

Votre seule option serait une fonction en table qui prend en entrée le texte et le découpe en lignes pour chaque ligne, de sorte que Management Studio obtienne une liste de lignes, pas une seule ligne.