How can I visualize the value of nvarchar(max), with max>65535, from SQL Server database?

Question

Possible Duplicate:
SQL Server Management Studio: Increase number of characters displayed in result set
Update: Note: that discussion contains INCORRECT answer marked as answer.

SSMS from SQL Server 2008 R2, permits to visualize maximum of 8192 characters in "Results to text" mode and 65535 in "Results to grid" mode. "Results to file" are also truncated.

How can I see the selected value of bigger size fast and cheap?

Update:
I saw previous discussion and the best answer is to create my own front-end app is not really an answer.
I am not planning to compete with DBMS client tools vendors.
I just need to see the value fast, dirty or cheap, be it tools or not tools.

I just cannot believe that in order to see a single value I should create client applications and there is no trick or way around.

Answer 1

Why don't you just return the dataset as XML and there are no size limitations? You can do this using the cast( COLUMN_NAME as XML ) to do this.

Answer 2

Quick and dirty, I like that. Of course it can be done from inside Management Studio, you just have to be little creative. The idea is simple - can't display the whole string? Chop it up and display more rows. Here is a function that takes a varchar input and outputs table with chunks of specified size. You can then CROSS APLLY select from from original table with this function and get what you need.

Function:

create function Splitter( @string varchar(max), @pieceSize int ) 
returns @t table( S varchar(8000) )
as
begin
    if ( @string is null or len(@string) = 0 )
        return

    set @pieceSize = isnull(@pieceSize, 1000)       
    if (@pieceSize < 0 or @pieceSize > 8000)
        set @pieceSize = 8000

    declare @i int = 0
    declare @len int = len(@string)

    while ( @i < @len / @pieceSize  )
    begin
        insert into @t(S) values(substring( @string, @i * @pieceSize + 1, @pieceSize))
        set @i = @i + 1
    end

    if (@len % @pieceSize) != 0
    begin
        if (@len / @pieceSize) = 0
            set @i = 1

        insert into @t(S) values(substring( @string, (@i - 1) * @pieceSize + 1, @len % @pieceSize ))
    end 
    return      
end

Usage example:

select t.ID, t.Col1, t.Col2, t.Col3, pieces.S
from dbo.MyTable as t
cross apply dbo.Splitter( t.MybigStringCol, 1000 ) as pieces

Answer 3

That is the problem, I attack in sqlise a PowerShell module of the SQLPSX codeplex project (sorry I'm only allowed to use on hyperlink please google for it). PowerShell ISE is the Integrated Scripting Environment which is part of PowerShell V2. SQLPSX is a collection of PowerShell modules targeting management and querying MS-SQLserver (and minimal support for ORACLE too). The normal output-pane of ISE has some bad truncation/wrapping behaviour, but it is possible to send out-put to an editor pane. When use a query that fetches a single row of a one column wide resultset and use either 'inline' or 'isetab' as output format, you get the complete varchar(max), text, CLOB (yes this works for ORACLE too) value. If you query a single row with such columns, the result depends on embedded linefeeds, a width of 10000 chars / line is current set. But that is in a script language and you can modify it by yourself.

If you prefer a pure T-SQL solution, you can look a the source of my project Extreme T-SQL Script http://etsql.codeplex.com/. With the scripts print_string_in_lines.sql and sp_gentextupdate.sql you have the tools to generate update scripts to set fields to the current content. SQL-SERVER 2008 is required, as I internally use varchar(max).

BTW I don't have access to SQL Server 2008 R2. I though the limit is still about 4000 characters per text column.

I hope that helps Bernd

Answer 4

Select
    CASE WHEN T.TheSegment = 1 Then Cast(T.SomeID as varchar(50)) 
         Else '' 
    End as The_ID
    , T.ChoppedField
From  (
   Select SomeID
       , 1 as TheSegment
       , substring(SomeBigField, 1, 8000) as ChoppedField
    from sometable
    Union All
    Select SomeID
       , 2
       , substring(SomeBigfield, 8001, 16000)
    from sometable
) as t
order by t.SomeID, t.TheSegment;

Rinse and repeat if necessary on the unions or feel free to get recursive; not sure how much more than 16000 characters you feel like reading. About as cheap as it gets.

Many times these large fields contain formating characters, so the suggestions for creating your own form and using some type of richtext control are valid.

Answer 5

You can see it by viewing it in your front-end application. SSMS is not designed to be a general data viewer.