Printing Strings Longer Than 8000 Characters

  • I'm working on a script to generate DDL statements. I have a variable declared as a VARCHAR(MAX) which gets set using a combination of the STUFF function and XML. All of that works just fine. Knowing that SQL is going to limit that to 8,000 characters I have a series of substrings breaking that down into smaller segments and assigning multiple variables. The script will print out the values stored in those variables at the end which gives me the DDL needed. However, in cases where it is longer than 8,000 characters it does not provide a clean break and prevents from easily copying and pasting. Here is what I mean by that.

    create table dbo.TestTable
    (
    [column1] int NOT NULL, [column2] varchar(10), ...<characters 47-7996>... , [col
    umn450] decimal(8,2)
    )

    You can see it breaks up column450 into two separate lines which causes error when running as is. Is there a way to have SQL continue to print a different variable on the same line as another exceeding a total of 8,000?

  • Are you referring to the output generated by the SQL PRINT statement?

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • Phil - Yes, the SQL PRINT output. In particular printing out strings, or concatenating, strings longer than 8,000 characters.

  • My first suggestion would be to use SELECT rather than PRINT, since SELECT goes up to 65535 chars (I believe, if you have the options set that way in SSMS).

     

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Here is a chunk of code I've used in the past that may help you, where @SQL1 was previously declared as NVARCHAR(MAX).

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

    SET @SQL1 = REPLACE(REPLACE(@SQL1, CHAR(13) + CHAR(10), CHAR(10)), CHAR(13), CHAR(10));

    WHILE LEN(@SQL1) > 1
    BEGIN
    IF CHARINDEX(CHAR(10), @SQL1)
    BETWEEN 1 AND 4000
    BEGIN
    SET @CurrentEnd = CHARINDEX(CHAR(10), @SQL1) - 1;
    SET @offset = 2;
    END;
    ELSE
    BEGIN
    SET @CurrentEnd = 4000;
    SET @offset = 1;
    END;

    PRINT SUBSTRING(@SQL1, 1, @CurrentEnd);

    SET @SQL1 = SUBSTRING(@SQL1, @CurrentEnd + @offset, LEN(@SQL1));
    END;

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • Phil - that helps limit each printout to 4,000 characters but in my case the string is 11,698 so it breaks it into three separate lines. The script I'm creating loops through each object in a database so the goal is to be able to copy and paste without having to bring those multiple lines into the same line.

  • RonMexico wrote:

    Phil - that helps limit each printout to 4,000 characters but in my case the string is 11,698 so it breaks it into three separate lines. The script I'm creating loops through each object in a database so the goal is to be able to copy and paste without having to bring those multiple lines into the same line.

    Then PRINT is not going to work for you. As Scott suggested, use SELECT instead.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • May I ask why you are doing this? There are other tried & tested ways of generating DDL.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • When you say use select instead of print are you saying to toggle results to text and then use select @SQL1 which in my case has a string of 11,698 characters? If so, that only shows the first 256 characters.

     

    I'm using this as part of a custom script to loop through all tables in a database and exclude some fields, change some data types, etc based on a set of criteria.

  • You need to change the Options..., Query Results, SQL Server in your SSMS to display more chars.

    You don't need (nor likely want) to toggle to text.  A grid display should work, at least as long as you don't use -- for inline comments.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Changing it to select provided all of the characters. Now I need to figure out an easy way to copy those results since it loops over hundreds of tables all in their own grid.

  • You can write them to a table -- add an identity for ordering -- and then SELECT from that table, for example:

    SELECT sql_text AS [--sql_to_run]

    FROM #some_table_name

    ORDER BY $IDENTITY

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • I'm with Phil.  Why are you doing this?

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Michael L John wrote:

    I'm with Phil.  Why are you doing this?

    I can't speak for Ron but sometimes it's necessary to see, for example, the full monty for dynamic SQL.

    --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)
    Intro to Tally Tables and Functions

  • Ron,

    The following does the trick for me when I need to do such a thing.  Details are in the flower box.

    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.

    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
    ;
    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
    )
    ;

    --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)
    Intro to Tally Tables and Functions

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

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