Jak wyświetlić pełną zawartość kolumny Text lub varchar (MAX) w programie SQL Server 2008 Management Studio?


98

W tej działającej bazie danych SQL Server 2008 (kompilacja 10.0.1600) znajduje się Eventstabela, która zawiera textkolumnę o nazwie Details. (Tak, zdaję sobie sprawę, że to właściwie powinna być varchar(MAX)kolumna, ale ktokolwiek ustawił tę bazę danych, nie zrobił tego w ten sposób).

Ta kolumna zawiera bardzo duże dzienniki wyjątków i powiązanych danych JSON, do których próbuję uzyskać dostęp za pośrednictwem programu SQL Server Management Studio, ale za każdym razem, gdy kopiuję wyniki z siatki do edytora tekstu, obcina je do 43679 znaków.

Czytałem w różnych miejscach w Internecie, że możesz ustawić maksymalną liczbę znaków pobieranych dla danych XML Tools > Options > Query Results > SQL Server > Results To Gridna nieograniczoną, a następnie wykonać zapytanie takie jak to:

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

(Zwróć uwagę, że kolumna danych nie jest w ogóle XML. CONVERTPrzekazywanie kolumny do formatu XML to tylko obejście, które znalazłem w Google, a ktoś inny użył go do obejścia limitu, jaki SSMS ma przy pobieraniu danych z kolumny textlub varchar(MAX)).

Jednak po ustawieniu powyższej opcji, uruchomieniu zapytania i kliknięciu w link w wyniku nadal otrzymuję następujący błąd:

Nie można wyświetlić XML. Wystąpił następujący błąd: Wystąpił nieoczekiwany koniec pliku. Wiersz 5, pozycja 220160.

Jednym z rozwiązań jest zwiększenie liczby znaków pobieranych z serwera dla danych XML. Aby zmienić to ustawienie, w menu Narzędzia kliknij polecenie Opcje.

Więc masz jakiś pomysł, jak uzyskać dostęp do tych danych? Czy konwersja kolumny, aby varchar(MAX)naprawić moje nieszczęścia?

Odpowiedzi:


99

SSMS zezwala tylko na nieograniczoną ilość danych dla danych XML. Nie jest to ustawienie domyślne i należy je ustawić w opcjach.

wprowadź opis obrazu tutaj

Jedną sztuczką, która może działać w dość ograniczonych okolicznościach, jest po prostu nazwanie kolumny w specjalny sposób, jak poniżej, aby była traktowana jako dane XML.

DECLARE @S varchar(max) = 'A'

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

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

W programie SSMS (co najmniej wersje od 2012 do bieżącej 18,3) wyświetla wyniki jak poniżej

wprowadź opis obrazu tutaj

Kliknięcie go otwiera pełne wyniki w przeglądarce XML. Przewijanie w prawo pokazuje, że ostatni znak B jest zachowany,

Jednak powoduje to pewne istotne problemy. Dodanie dodatkowych kolumn do zapytania przerywa efekt i wszystkie dodatkowe wiersze są łączone z pierwszym. Wreszcie, jeśli ciąg zawiera znaki, takie jak <otwarcie przeglądarki XML kończy się niepowodzeniem z błędem analizy.

Bardziej niezawodny sposób na to, aby uniknąć problemów z konwersją SQL Server <do &lt;etc lub niepowodzeniem z powodu tych znaków jest poniżej ( autor: Adam Machanic tutaj ).

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('')

2
Dodanie CDATA do instrukcji Convert zadziałało! Wielkie dzięki. :)
adamjford

Dodawanie CDATAmoże działać, ale jeśli dane zawierają znaki sterujące, musisz wykonać operację zamiany. W moim przypadku używałem separatora jednostek, kodu ASCII 31, w moich danych. Ponieważ używałem tylko tej jednej postaci w wielu miejscach, REPLACE(details, char(31), '&x1f;')wystarczył prosty . Gdybym miał nieznane znaki lub dużą liczbę różnych znaków do zastąpienia, być może musiałbym znaleźć inne rozwiązanie.
Zarepheth

@Zarepheth - To CDATAbyła moja pierwsza sugestia. Późniejsza z nich AS [processing-instruction(x)]unika tego.
Martin Smith

2
@MartinSmith doskonała odpowiedź! Ciekawe, ile się nauczyłem z Twoich odpowiedzi! +1
Kin Shah,

54

Udało mi się to uruchomić ...

SELECT CAST('<![CDATA[' + LargeTextColumn + ']]>' AS XML) FROM TableName;

28

Jedynym sposobem obejścia problemu jest kliknięcie prawym przyciskiem myszy zestawu wyników i wybranie opcji „Zapisz wyniki jako ...”. Spowoduje to wyeksportowanie go do pliku CSV z całą zawartością kolumny. Nie idealne, ale dla mnie działało wystarczająco dobrze.

Obejście problemu


11

Czy wypróbowałeś to proste rozwiązanie? Tylko 2 kliknięcia!

W oknie zapytania

  1. ustaw opcje zapytania na „Wyniki do siatki”, uruchom zapytanie
  2. Kliknij prawym przyciskiem myszy kartę wyników w rogu siatki, zapisz wyniki jako dowolne pliki

Otrzymasz cały tekst, który chcesz zobaczyć w pliku !!! Widzę 130 556 znaków dla mojego wyniku pola varchar (MAX)

Wyniki w pliku


1
Prosto - łatwo - i zawsze zapominany, dopóki nie będziesz go potrzebować :)
Dimi Takis

5

Najprostszym rozwiązaniem, jakie znalazłem, jest utworzenie kopii zapasowej tabeli i wyświetlenie skryptu. Aby to zrobić

  1. Kliknij prawym przyciskiem myszy bazę danych i wybierz Tasks>Generate Scripts...
  2. Kliknij stronę „Wprowadzenie” Next
  3. Strona „Wybierz obiekty”
    1. Wybierz Select specific database objectsi wybierz swój stół.
    2. Kliknij Next
  4. Strona „Ustaw opcje skryptów”
    1. Ustaw typ wyjścia na Save scripts to a specific location
    2. Wybierz Save to filei wypełnij powiązane opcje
    3. Kliknij Advancedprzycisk
    4. Ustaw General> Types of data to scriptna Data onlylub Schema and Datai kliknij OK
    5. Kliknij Next
  5. „Strona podsumowania” kliknij dalej
  6. Twój skrypt sql powinien zostać wygenerowany na podstawie opcji ustawionych w 4.2. Otwórz ten plik i wyświetl swoje dane.

3

Typ danych TEKST jest stary i nie powinien być już używany, uciążliwe jest wybieranie danych z kolumny TEKST.

ntext, tekst i obraz (Transact-SQL)

Typy danych ntext, text i image zostaną usunięte w przyszłej wersji Microsoft SQL Server. Unikaj używania tych typów danych w nowych pracach programistycznych i planuj modyfikowanie aplikacji, które obecnie z nich korzystają. Zamiast tego użyj nvarchar (max), varchar (max) i varbinary (max).

musisz użyć TEXTPTR (Transact-SQL), aby pobrać dane tekstowe.

Zobacz także ten artykuł dotyczący obsługi typu danych tekstowych .


Czy konwersja kolumny, aby varchar(MAX)uniemożliwić SSMS obcięcie danych z niej?
adamjford

Najpierw użyłbym SSMS do wygenerowania skryptu, aby przekonwertować kolumnę TEKST na varchar (maks.). Stamtąd możesz ocenić, czy chcesz dalej przekonwertować go na kolumnę XML. Jeśli chodzi o limity wyświetlania SSMS, wyniki w siatce można ustawić tak, aby wyświetlały do ​​65 535 znaków danych innych niż XML na kolumnę i do nieograniczonej ilości danych xml (fyi, kolumny tabeli typu XML mogą przechowywać tylko 2 GB). Podczas gdy wyniki do tekstu są ograniczone do 8192 znaków na kolumnę. Aby ustawić te domyślne maksymalne limity, po prostu przejdź do (w SSMS) menu NARZĘDZIA, następnie OPCJE ... następnie WYNIKI ZAPYTANIA, następnie SERWER SQL, a następnie WYNIKI DO SIATKI lub WYNIKI DO TEKSTU.
KM.

3

Wygląda na to, że Xml może nie być dobrze uformowany. W takim przypadku nie będziesz w stanie przesłać go jako Xml, a biorąc pod uwagę to, jesteś ograniczony co do tego, ile tekstu możesz zwrócić w Management Studio. Możesz jednak podzielić tekst na mniejsze fragmenty, na przykład:

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

Następnie trzeba by je ponownie połączyć ręcznie.

EDYTOWAĆ

Wygląda na to, że w textdanych są znaki , których parser Xml nie lubi. Możesz spróbować przekonwertować te wartości na encje, a następnie spróbować Convert(xml, data)sztuczki. Więc coś takiego:

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

(Musiałem rzutować na varchar (max), ponieważ funkcja zamiany nie będzie działać na textkolumnach. Nie powinno być żadnego powodu, dla którego nie można by przekonwertować tych textkolumn na varchar(max).)


Och, przypuszczam, że powinienem wspomnieć, że kolumna w ogóle nie jest XML. Konwersja kolumny do XML to tylko obejście, które znalazłem w Google, że ktoś inny użył do obejścia limitu, jaki SSMS ma przy pobieraniu danych z kolumny textlub varchar(MAX).
adamjford

1
EDYTUJ powyższy kod; Z Tally As (wybierz ROW_NUMBER () OVER (ORDER BY s1.id) - 1 As Num From sys.sysobjects As s1 Cross Join sys.sysobjects As s2) Wybierz podłańcuch (T1.YourTextCol, T2.Num * 8000 + 1, 8000) Z Twojej tabeli jako T1 Łączenie krzyżowe Tally jako T2 Gdzie T2.Num <= Ceiling (dataalength (T1.YourTextCol) / 8000) Order By T2.Num
Ian Quigley

Użycie TSQLz @IanQuigley na podstawie tej odpowiedzi działało dobrze dla mnie. Zasadniczo wziąłem wyniki (skończyło się na 11 rekordach) i po prostu wkleiłem je razem w Notatniku. Działał doskonale. Musisz zapisać ten skrypt. Miałem szalenie długi plik XML zawierający nieprawidłowe znaki.
atconway

1

Wolę ten prosty hack XML, który sprawia, że ​​kolumny w SSMS są klikalne na podstawie komórki po komórce. Dzięki tej metodzie możesz szybko przeglądać dane w widoku tabelarycznym SSMS i klikać poszczególne komórki, aby zobaczyć pełną wartość, gdy są interesujące. Jest to identyczne z techniką OP, z wyjątkiem tego, że pozwala uniknąć błędów XML.

SELECT
     e.EventID
    ,CAST(REPLACE(REPLACE(e.Details, '&', '&amp;'), '<', '&lt;') AS XML) Details
FROM Events e
WHERE 1=1
AND e.EventID BETWEEN 13920 AND 13930
;

Zauważ, że niektóre znaki są nieprawidłowe w XML 1.0, ale poprawne w NVARCHAR/ VARCHAR. Na przykład znak NUL (inny niż NULLjako wartość pola). Rzutowanie zakończy się niepowodzeniem dla ciągów z takimi osadzonymi wartościami.
binki

1

Począwszy od SSMS 18.2, możesz teraz wyświetlać do 2 milionów znaków w wynikach siatki. Źródło

Zezwalaj na wyświetlanie większej ilości danych (wynik na tekst) i przechowywanie ich w komórkach (wynik na siatkę). SSMS pozwala teraz na maksymalnie 2 mln znaków dla obu.

Sprawdziłem to za pomocą poniższego kodu.

DECLARE @S varchar(max) = 'A'

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

SELECT @S as a

1
Maksymalna liczba znaków to w rzeczywistości 2097152 ( w SSMS w wersji 15.0.18333.0 ).
stomia

0

Myślę, że nie masz szczęścia. Problem nie jest problemem na poziomie SQL, na którym zdają się koncentrować wszystkie inne odpowiedzi, ale po prostu jednym z interfejsów użytkownika. Management Studio nie jest przeznaczony do ogólnego przeznaczenia / ogólnego interfejsu dostępu do danych. To nie jest twój interfejs, ale twój obszar administracyjny i ma poważne ograniczenia w obsłudze danych binarnych i dużych danych testowych - ponieważ osoby używające go w określonym profilu użytkowania nie napotkają tego problemu.

Przedstawianie dużych danych tekstowych po prostu nie jest planowanym użyciem.

Jedynym wyborem byłaby funkcja wartościowana w tabeli, która pobiera dane wejściowe i wycina je w wierszach dla każdego wiersza, tak aby Management Studio otrzymało listę wierszy, a nie jeden wiersz.


Wydaje mi się, że ta odpowiedź jest w najlepszym razie domysłem, czy masz jakieś źródła „planowanego użycia”? Jeśli jest to „studio zarządzania” SQL, powinno przynajmniej być w stanie pokazać mi zawartość moich baz danych. Absolutnie okropny interfejs użytkownika.
cholerny

Ta odpowiedź powinna zostać usunięta, ponieważ nie ma już zastosowania
Caius Jard
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.