LongPrint

  • Comments posted to this topic are about the item LongPrint

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • Thanks for the LongPrint. Works... I use it for debugging. It is a tool needed in SQL Server for long print statements. :-):-)

  • Hello, Pete. Glad its helping you out.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • Thanks for the wonderful script.

    keep posting such good scripts..

    Neeraj Prasad Sharma
    Sql Server Tutorials

  • Hello Timothy,

    Question where did you get the figure 1073741822?

    SET @sSQL = SUBSTRING(@sSQL, @CurrentEnd+@Offset, 1073741822)

    The pain of Discipline is far better than the pain of Regret!

  • Thank you so much for this script!! I was going crazy trying to figure out why I was printing something that seemed to be getting truncated in the output.

    Much appreciated.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • I noticed it was a bit slow, and had a quick look.

    Here is a version that is faster, if you have a lot of spaces.

     

    --USE master
    GO
    BEGIN TRY
    DROP PROCEDURE dbo.LongPrint
    END TRY
    BEGIN CATCH
    END CATCH

    go
    CREATE --or alter
    PROCEDURE dbo.LongPrint
    @String NVARCHAR(MAX)

    AS

    /*
    Example:

    exec LongPrint @string =
    'This String
    Exists to test
    the system.'

    another method is
    SELECT CAST('<root><![CDATA[' + @MyLongString + ']]></root>' AS XML)
    from --https://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement

    */

    /*
    from: http://www.sqlservercentral.com/scripts/Print/63240/

    This procedure is designed to overcome the limitation
    in the SQL print command that causes it to truncate strings
    longer than 8000 characters (4000 for nvarchar).

    It will print the text passed to it in substrings smaller than 4000
    characters. If there are carriage returns (CRs) or new lines (NLs in the text),
    it will break up the substrings at the carriage returns and the
    printed version will exactly reflect the string passed.

    If there are insufficient line breaks in the text, it will
    print it out in blocks of 4000 characters with an extra carriage
    return at that point.

    If it is passed a null value, it will do virtually nothing.

    NOTE: This is substantially slower than a simple print, so should only be used
    when actually needed.
    */

    DECLARE
    @CurrentEnd BIGINT, /* track the length of the next substring */
    @offset tinyint /*tracks the amount of offset needed */


    set @string = replace( replace(@string, char(13) + char(10), char(10)) , char(13), char(10))

    WHILE LEN(@String) > 1
    BEGIN


    IF CHARINDEX(CHAR(10), @String, 3000) between 1 AND 4000
    BEGIN
    SET @CurrentEnd = CHARINDEX(char(10), @String, 3000) -1
    set @offset = 2
    END
    ELSE IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
    BEGIN
    SET @CurrentEnd = CHARINDEX(char(10), @String) -1
    set @offset = 2
    END
    ELSE
    BEGIN
    SET @CurrentEnd = 4000
    set @offset = 1
    END


    PRINT SUBSTRING(@String, 1, @CurrentEnd)

    set @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822)

    END /*End While loop*/
  • I used to display long strings in a similar matter and even wrote a similar article (didn't know Timothy had already written one back then).  A fellow by the name of Orlando Colamatteo showed me a trick and the following function is what I wrote from Orlando's great tip.  Details are in the comments and, yes, I use this in production.

     CREATE FUNCTION [dbo].[ShowLongString]
    /**********************************************************************************************************************
    Purpose:
    Display a string of more than 8000 characters. The string can be Dynamic SQL, XML, or just about anything else.

    Note that this function does use XML and, while normally quite faithful, there are characters (I've not taken the time
    to identify them because they're edge cases that I don't deal with) that it just won't handle. It will, however,
    handle most control-characters below ASCII 32.
    -----------------------------------------------------------------------------------------------------------------------
    Usage:
    --===== Example with Dynamic SQL
    DECLARE @SQL VARCHAR(MAX);
    SELECT @SQL = '
    SELECT somecolumnlist
    FROM some table with joins
    ;'
    ;
    SELECT LongString
    FROM dbo.ShowLongString(@SQL)
    ;
    --===== Example with a call to a table or view
    SELECT sm.Object_ID, Definition = ls.LongString
    FROM sys.SQL_Modules sm
    CROSS APPLY dbo.ShowLongString(sm.Definition) ls
    ;
    -----------------------------------------------------------------------------------------------------------------------
    Credits:
    1. I learned this trick from a post by Orlando Colamatteo at the following link. It has served me very well since
    then. Thanks, Orlando.
    https://www.sqlservercentral.com/Forums/FindPost1468782.aspx
    -----------------------------------------------------------------------------------------------------------------------
    Revision History:
    Rev 00 - 20 Sep 2013 - Jeff Moden - Initial creation and test.
    **********************************************************************************************************************/
    --===== Declare the I/O for this function
    (@pLongString VARCHAR(MAX))
    RETURNS TABLE WITH SCHEMABINDING AS
    RETURN
    SELECT LongString =
    (
    SELECT REPLACE(
    CAST(
    '--' + CHAR(10) + @pLongString + CHAR(10)
    AS VARCHAR(MAX))
    ,CHAR(0),'') --CHAR(0) (Null) cannot be converted to XML.
    AS [processing-instruction(LongString)]
    FOR XML PATH(''), TYPE
    )
    ;
    GO
    GRANT SELECT ON [dbo].[ShowLongString] TO [public] AS [dbo]
    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • hi Jeff,

    That is going to be useful, at least for me. But I could not get it to work on SQL Server 2008 R2 (not that it is relevant for much longer)

    Thank you,

    Henrik

  • Henrik Staun Poulsen wrote:

    hi Jeff, That is going to be useful, at least for me. But I could not get it to work on SQL Server 2008 R2 (not that it is relevant for much longer) Thank you, Henrik

    I don't have 2008 R2 to test against but, for me, it works just fine in 2008, 2012, and 2016.  I wonder if the forum code may have changed some of the code in the function (it's happened before).

    Also, when you say you couldn't get it to work in 2008 R2, I can't help there without you posting the code that you used that didn't work.  It would also be helpful if you were to post any errors returned if any are returned but, at least post the code where it's not working for you.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • SQL Server 2008 R2 is going out of support, and we're moving the last database to Azure SQL DB today, so it doesn't really matter, but here goes:

    Your function works when I run it on Azure SQL DB. The same code installs and run (and run and run) on SQL Server 2008 R2 with this @@version : Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) Aug 19 2014 12:21:34 Copyright (c) Microsoft Corporation Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    When running SP_Whoisactive, it shows that it is just burning CPU cycles. Your first example has not stopped yet, after 04:22.

     

     

  • Thanks for taking the time, Henrik.  A lot of people will continue to be "stuck" in 2008 R2 for one reason or another and so I wanted to know what's going on there.  I don't have that version to test on but it's good to know that it goes haywire on that version so that we can warn people if they ask.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • version - Microsoft SQL Server 2008 R2 (SP3-GDR) (KB4057113) - 10.50.6560.0 (X64) Dec 28 2017 15:03:48 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

    Jeff

    this worked well for me. the version i had been using cut the output into 4k lines, but always lost the first character of the next line. Really a pain to put it back together.

    declare @t varchar(max) = cast(replicate('1', 8000) as varchar(max)) + replicate('2', 8000) + replicate('3', 8000) + 'z'
    print len(@t)

    select @t
    print @t

    SELECT LongString
    FROM dbo.ShowLongString(@t)
  • Jeff

    declare @t varchar(max) = cast(replicate('1', 8000) as varchar(max)) + char(13) + char(10) + replicate('2', 8000) + char(13) + char(10) + replicate('3', 8000) + char(13) + char(10) +'z'

    Another nice feature of your function is that it respects CHAR(13) + CHAR(10), which does not usually happen in the SQL 2008 or SQL 2008 R2 SSMS result sets. The function that i mentioned that i had been using would force a CRLF at 4000 if the line was longer than 4k. This is what would lose the first character on the next line.

    I think you really ought to promote this function, especially since SSMS v18 will not have a debugger. This function will become a crucial part of my toolbox.

    Vote to add debugger back

    https://feedback.azure.com/forums/908035-sql-server/suggestions/35881492-put-debugger-back-into-ssms-18#comments

  • Jeff Moden's function ShowLongString is just what I was looking for.  I use it as a better alternative for sp_helptext to quickly view the code of a procedure or function.

    Adding the following line of code to a query shortcut will make it even faster:

    EXEC sys.sp_executesql N'SELECT ls.longstring FROM sys.sql_modules sm CROSS APPLY dbo.ShowLongString (sm.[definition]) ls WHERE OBJECT_NAME(sm.[object_id]) = @O', N'@O SYSNAME', @O =

    Selecting the name of a procedure (double click or CTRL + W), pressing the assigned query shortcut, click on XML result.

     


    Dutch Anti-RBAR League

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply