How to Print Varchar(Max) Using Print Statement

How to print VARCHAR(MAX) using Print Statement?

You could do a WHILE loop based on the count on your script length divided by 8000.

EG:

DECLARE @Counter INT
SET @Counter = 0
DECLARE @TotalPrints INT
SET @TotalPrints = (LEN(@script) / 8000) + 1
WHILE @Counter < @TotalPrints 
BEGIN
    -- Do your printing...
    SET @Counter = @Counter + 1
END

How to print a variable of type NVARCHAR(MAX) while looping over a CURSOR

As per https://docs.microsoft.com/en-us/sql/t-sql/language-elements/print-transact-sql:

Remarks

A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated. The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000).

This being the case you'll want to perform mulltiple Print actions, each one dealing with the next valid Chunk size, depending on if your're dealing with Standard or Unicode data types.

e.g. something like this untested code:

Set @p = 1
Print SubStr(@tsql, @p, 4000);
While @p < Len(@tSql)
Begin
    Set @p = @p + 4000;
    If @p < Len(@tSql)
        Print SubStr(@tsql, @p, 4000);
End

how to use print statement when you have more than 8000 characters

Why do you want to print ?

Why you don't do a select with the script, put it in xml if you want to see everything.

Select Convert(xml, @script)

EDIT :

between you don't need to do +1

PRINT SUBSTRING(@script,@pos,7500)

How many characters can we get in a SELECT or a PRINT statement?

Try

PRINT CAST(@QUERY AS NTEXT)

ntext , text, and image data types has been removed in a latest version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them.

You could do a WHILE loop based on the count on your script length divided by 8000.

EG:

DECLARE @Counter INT
SET @Counter = 0
DECLARE @TotalPrints INT
SET @TotalPrints = (LEN(@QUERY) / 8000) + 1
WHILE @Counter < @TotalPrints 
BEGIN
    -- Do your printing...
    SET @Counter = @Counter + 1
END

FIDDLE DEMO

Why is a Variable declared as NVARCHAR(MAX) dropping chunks of the string?

This is working fine for me:

DECLARE @sql nvarchar(max) = 
    REPLICATE(CONVERT(nvarchar(max), N'a'), 4000)
  + REPLICATE(CONVERT(nvarchar(max), N'b'), 4000)
  + REPLICATE(CONVERT(nvarchar(max), N'c'), 4000)
  + REPLICATE(CONVERT(nvarchar(max), N'd'), 4000)
  + REPLICATE(CONVERT(nvarchar(max), N'e'), 4000)
  + REPLICATE(CONVERT(nvarchar(max), N'f'), 4000)
  + REPLICATE(CONVERT(nvarchar(max), N'g'), 4000)
  + REPLICATE(CONVERT(nvarchar(max), N'h'), 4000)
  + REPLICATE(CONVERT(nvarchar(max), N'i'), 4000);


PRINT LEN(@sql);  -- characters
PRINT DATALENGTH(@sql); -- bytes
PRINT '';

DECLARE @Printed int = 1, @ToPrint int = LEN(@sql);

WHILE @Printed < @ToPrint BEGIN 
    PRINT(SUBSTRING(
        @sql, @Printed, 4000))
    SET @Printed = @Printed + 4000
    PRINT('Printed: ' + CONVERT(varchar(11), @Printed)) -- *
END

* Always specify length.

Output is:

36000
72000

How to Print Varchar(Max) Using Print Statementaa... 4000 As ...aaa
Printed: 4001
bb... 4000 Bs ...bbb
Printed: 8001
cccccccccc... 4000 Cs ...ccc
Printed: 12001
       


       
Related Topics
          

             
 How to Create Multiple One to One's
 Generate_Series() Equivalent in MySQL
 Required to Join 2 Tables With Their Fks in a 3Rd Table
 Fast Way to Discover the Row Count of a Table in Postgresql
 What Is the Simplest SQL Query to Find the Second Largest Value
 What Is Self Join and When Would You Use It
 How to Query a Value in SQL Server Xml Column
 How to Return the Column Names of a Table
 SQL - How to Store and Navigate Hierarchies
 How to List the Primary Key of a SQL Server Table
 Convert Varchar into Datetime in SQL Server
 Insert into Multiple Tables in One Query
 How to Split a Single Column Values to Multiple Column Values
 Can a Foreign Key Be Null And/Or Duplicate
 Copy Tables from One Database to Another in SQL Server
 Are There Any Disadvantages to Always Using Nvarchar(Max)
 Insert Data in 3 Tables At a Time Using Postgres
 How to Count Items in Comma Separated List MySQL
          
dd... 4000 Ds ...ddd
Printed: 16001
How to print VARCHAR(MAX) using Print Statement? How to print a variable of type NVARCHAR(MAX) while looping over a CURSOR how to use print statement when you have moreee... 4000 Es ...eee
Printed: 20001
ffffffffff... 4000 Cs ...fff
Printed: 24001
gggggggggg... 4000 As ...ggg
Printed: 28001
hhhhhhhhhh... 4000 Bs ...hhh
Printed: 32001
iiiiiiiiii... 4000 Cs ...iii
Printed: 36001

So, I think the problem is elsewhere. In any case, this is a really sloppy way to validate the contents of dynamic SQL. Instead I would do:

SELECT CONVERT(xml, @sql);

Then you can click on the output cell and it opens in an XML text editor for review (you can then copy and paste that output into a query window if you want IntelliSense or any chance in executing, but you'll have to replace encoded characters like > --> >. I talk about this approach (and another one) here:

• Validate the contents of large dynamic SQL strings

If you insist on doing it this bricklaying way, perhaps there is some kind of non-printing or string-termination character that's at that point. If you say it is around character 5,600 then you could do:

DECLARE @i int = 5550, @c nchar(1);
WHILE @i <= 5650
BEGIN
  PRINT '';
  SET @c = SUBSTRING(@sql, @i, 1);
  PRINT '------   ' + RTRIM(@i) + '------:';
  PRINT 'Raw:     ' + @c;
  PRINT 'ASCII:   ' + ASCII(@c);
  PRINT 'UNICODE: ' + UNICODE(@c);
  SET @i += 1;
END

You should be able to scan down and match the last sequence of characters you see in the broken print output. Then look for anything where the Raw: line is empty and the ASCII: line is anything other than typical (9, 10, 13, 32).

But I don't think this is the problem. I'll go back to an earlier comment where I suggested that the string itself is the problem. In the question, you mention @sql, but don't show how it's populated. I would bet that some string you're adding to that is getting truncated. Some things to look out for:

• Intermediate variables/parameters declared as varchar/nvarchar but with no length (which sometimes leads to silent truncation at 1 character, and sometimes 30):

    DECLARE @sql nvarchar(max) = N'SELECT * FROM dbo.table ';
    DECLARE @where nvarchar = N'WHERE some condition...';
    SET @sql += @where;
    PRINT @sql;
  

  Output:

    SELECT * FROM dbo.table W
  

• Intermediate variables/parameters declared as varchar/nvarchar but too short (which leads to silent truncation at whatever the declaration is):

    DECLARE @sql nvarchar(max) = N'SELECT * FROM dbo.table ';
    DECLARE @where nvarchar(10) = N'WHERE some condition...';
    SET @sql += @where;
    PRINT @sql;
  

  Output:

    SELECT * FROM dbo.table WHERE some
  

• Explicit CONCAT with NULL, which leads to silently dropping any NULL input):

    DECLARE @sql nvarchar(max) = N'SELECT * FROM dbo.table ';
    DECLARE @where nvarchar(32);
    DECLARE @orderby nvarchar(32) = N' ORDER BY col1';
    SET @sql = CONCAT(@sql, @where, @orderby);
    PRINT @sql;
  

  Output:

    SELECT * FROM dbo.table  ORDER BY col1
  

• Not using the N prefix when concatenating Unicode string literals > 4000 characters (example here):

    DECLARE @sql nvarchar(max) = '';
  
    SET @sql = @sql + '... literally 4001 characters ...';
  

  The output here (as shown in the example) will be truncated at 4,000 characters. However if you define your strings properly, this won't happen:

    DECLARE @sql nvarchar(max) = N'';
  
    SET @sql = @sql + N'... literally 4001 characters ...';
  

These things can be hard to spot in overly complex dynamic SQL generation, so it's never a bad idea to simplify and try any way you can to divide & conquer the major components in the eventual string. Based on the repro you attempted I would almost certainly guess it is the "variable declared too short" symptom. Safest is to ensure every input to a dynamic SQL string should be declared as nvarchar(max); no real good reason to use anything else except for entity names which are constrained by metadata anyway.

---

Related Topics