4

In a SQL Server 2012 database, I have a log table where I log SQL queries executed by a VB.NET application.

The field is defined as nvarchar(MAX)

In Microsoft SQL Server Management Studio, when I go to the table and do "Edit All Rows", on one line I see spaces in the end of the text ScreenShot 1

But when I enter my cursor in the line to edit it (not touching anything, before I even start editing), the spaces disappear (along with the trailing quote)  Screenshot 2

And when I go to New Query and execute the same query

SELECT LOGID, LogDate, SqlLog 
FROM ezber_SQL_LOG 
WHERE (LOGID = 1604))

I get the result without the spaces and the trailing quote:

Screenshot 3

So my guess is that there are special characters in the data (line breaks, tabs or something like that), but how can I find out for sure and know what they are?

  • If you're only looking at a single row, try a query window with results to text instead of results to grid. – Aaron Bertrand Dec 19 '13 at 19:14
  • Results to text was giving me the same result as Results to Grid – JF Menard Dec 20 '13 at 15:17
2

Have a look at the answer in following post: (the one with pictures).

https://stackoverflow.com/questions/11897950/how-do-you-view-all-text-from-an-ntext-or-nvarcharmax-in-ssms/11900246#11900246

You need "copy current cell 1:1", or you can use notepad++ as visualizer:

  • 1
    I tried it and it fact I was able to find what I was looking for by using "open cell contents in external text editor (notepad++ or notepad) using "Cell visualizers" feature". Notepad++ shows "NUL" characters where the text cuts in the results grid/text – JF Menard Dec 20 '13 at 15:19
  • Yepp ! You can even use hex editor (or Hex plugin for notepad++) to see all data as HEX. Happy that it helped ! Read full features list and I am sure this will be not the only feature you will like ;) – Andrei Rantsevich Dec 20 '13 at 16:09
3

I found viewing the data as binary was the way to go for me:

SELECT @String as MyString, CAST(@String as varbinary(max)) MyString_In_Hexidecimal;

I found an ASCII table then helped decipher what was in the string.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.