nvarchar max still being truncated

Question

So I m writing a stored procedure in MS SQL Server 2008  It s a really long query and I have to write it dynamically  so I create a variable called  Query and make it of type NVARCHAR MAX   Now  I have been told that in modern versions of SQL Server  NVARCHAR MAX  can hold a ridiculous amount of data  way more than the original 4000 character maximum  However   Query is still getting truncated to 4000 characters when I try to print it out   DECLARE  Query NVARCHAR max   SET  Query    SELECT     -- some of the query gets set here SET  Query    Query         -- more query gets added on  etc   -- later on    PRINT LEN  Query  -- Prints out 4273  which is correct as far as I can tell PRINT  Query      -- Truncates value to 4000 characters EXEC sp executesql  Query -- totally crashes due to malformed  truncated  query   Am I doing something incorrectly  or am I completely wrong about how NVARCHAR MAX  works

User · Answer

Results to text only allows a maximum of 8192 characters.

Screenshot

I use this approach

DECLARE @Query NVARCHAR(max);

set @Query = REPLICATE('A',4000)
set @Query = @Query + REPLICATE('B',4000)
set @Query = @Query + REPLICATE('C',4000)
set @Query = @Query + REPLICATE('D',4000)

select LEN(@Query)

SELECT @Query /*Won't contain any "D"s*/
SELECT @Query as [processing-instruction(x)] FOR XML PATH /*Not truncated*/

User · Answer

The problem is with implicit conversion   If you have Unicode nChar nVarChar values you are concatenating  then SQL Server will implicitly convert your string to nVarChar 4000   and it is unfortunately too dumb to realize it will truncate your string or even give you a Warning that data has been truncated for that matter   When concatenating long strings  or strings that you feel could be long  always pre-concatenate your string building with CAST    as nVarChar MAX   like so   SET  Query   CAST    as nVarChar MAX  --Force implicit conversion to nVarChar MAX                SELECT    -- some of the query gets set here                   -- more query gets added on  etc    What a pain and scary to think this is just how SQL Server works       I know other workarounds on the web say to break up your code into multiple SET SELECT assignments using multiple variables  but this is unnecessary given the solution above  For those who hit an 8000 character max  it was probably because you had no Unicode so it was implicitly converted to VarChar 8000    Explanation  What s happening behind the scenes is that even though the variable you are assigning to uses  MAX   SQL Server will evaluate the right-hand side of the value you are assigning first and default to nVarChar 4000  or VarChar 8000   depending on what you re concatenating    After it is done figuring out the value  and after truncating it for you  it then converts it to  MAX  when assigning it to your variable  but by then it is too late

User · Answer

The problem with creating dynamic SQL using string expression is that SQL does limit the evaluation of string expressions to 4 000 chars  You can assign a longer string to an nvarchar max  variable  but as soon as you include   in the expression  such as   CASE     END      then the expression result is limited to 4 000 chars   One way to fix this is to use CONCAT instead of    For example   SET  sql   CONCAT  sql  N           dynamic SQL statements            CASE     END  N           dynamic SQL statements              Where  sql is declared as nvarchar max

User · Answer

I was creating a JSON-LD to create a site review script    DECLARE  json  VARCHAR MAX     The actual JSON is about 94K   I got this to work by using the CAST    AS VARCHAR MAX      json  as explained by other contributors -  so   SET  json   CAST    AS VARCHAR MAX      SELECT          2  I also had to change the Query Options - Query Options -   results  -   grid  -   Maximum Characters received  -   non-XML Data  SET to 2000000    I left the  results  -   text  -   Maximum number of characters displayed in each column  as the default

User · Answer

I have encountered the same problem today and found that beyond that 4000 character limit  I had to split the dynamic query into two strings and concatenate them when executing the query   DECLARE  Query NVARCHAR max   DECLARE  Query2 NVARCHAR max   SET  Query    SELECT     -- some of the query gets set here SET  Query2         -- more query gets added on  etc   EXEC   Query    Query2

User · Answer

To see the dynamic SQL generated  change to text mode  shortcut  Ctrl-T   then use SELECT  PRINT LEN  Query  -- Prints out 4273  which is correct as far as I can tell --SET NOCOUNT ON SELECT  Query   As for sp executesql  try this  in text mode   it should show the three aaaaa    s the middle one being the longest with  SELECT     added  Watch the  Ln    Col   indicator in the status bar at bottom right showing 4510 at the end of the 2nd output   declare  n nvarchar max  set  n   REPLICATE convert nvarchar max    a    4500  SET  N    SELECT        n        print  n   -- up to 4000 select  n  -- up to max exec sp Executesql  n

User · Answer

Print truncates the varchar MAX  to 8000  nvarchar MAX  to 4000 chars   But   PRINT CAST  query AS NTEXT    will print the whole query

User · Answer

Your first problem is a limitation of the PRINT statement  I m not sure why sp executesql is failing  It should support pretty much any length of input   Perhaps the reason the query is malformed is something other than truncation

User · Answer

Problem seems to be associated with the SET statement  I think the expression can t be more than 4 000 bytes in size  There is no need to make any changes to any settings if all you are trying to do is to assign a dynamically generated statement that is more than 4 000 characters  What you need to do is to split your assignment  If your statement is 6 000 characters long  find a logical break point and then concatenate second half to the same variable  For example   SET  Query    SELECT        Up To 4 000 characters  then rest of statement as below   SET  Query    Query    rest of statement    Now run your query as normal i e  EXEC    Query

User · Answer

Use this PRINT BIG function to output everything   IF OBJECT ID  tempdb   printBig   IS NOT NULL   DROP PROCEDURE  printBig  GO  CREATE PROCEDURE  printBig       text NVARCHAR MAX     AS  --DECLARE  text NVARCHAR MAX     YourTextHere  DECLARE  lineSep NVARCHAR 2    CHAR 13    CHAR 10   -- Windows  r n  DECLARE  off INT   1 DECLARE  maxLen INT   4000 DECLARE  len INT  WHILE  off  lt  LEN  text  BEGIN    SELECT  len       CASE       WHEN LEN  text  -  off - 1  lt    maxLen THEN LEN  text        ELSE  maxLen              - CHARINDEX REVERSE  lineSep    REVERSE SUBSTRING  text   off   maxLen                 - LEN  lineSep                 1     END   PRINT SUBSTRING  text   off   len    --PRINT   off     CAST  off AS VARCHAR       len     CAST  len AS VARCHAR    SET  off     len   LEN  lineSep   END   Source   https   www richardswinbank net doku php id tsql print big

[sql] nvarchar(max) still being truncated

Examples related to sql

Examples related to sql-server-2008

Examples related to dynamic-sql