Data Truncated in SQL Server when assign json to variable

Question

I want to generate JSON using T-SQL and stored it in a variable. When I assign the JSON to a NVARCHAR(MAX) the data is being truncated, But it is not being truncated if I directly use the select statement.

DECLARE @DateOne DATETIME, @DateTwo DATETIME,@DrillThroughData NVARCHAR(MAX)
SELECT @DateOne = '2016-01-01',@DateTwo = '2017-07-31'

 CREATE TABLE #DrillThroughData
 (
[Date] DATE
,TotalAmountIncome DECIMAL(18,4)
,TotalAmountRefunded DECIMAL(18,4)
,ProductCostIncome DECIMAL(18,4)
,ProductCostRefunded DECIMAL(18,4)
)

INSERT INTO #DrillThroughData
VALUES('2017-07-13',839.2000,-241.4000,0.0000,0.0000)

;WITH CTE AS
     (
    SELECT 1 SNo,CAST(CONVERT(NVARCHAR(6),@DateOne,112)+'01' AS DATE) AS   StartDate  
        UNION ALL 
    SELECT SNo+1 SNo,DATEADD(DAY,1,StartDate ) StartDate 
    FROM CTE WHERE DATEADD(Day,1,StartDate ) <= @DateTwo
)
SELECT StartDate [Date],SNo
INTO #AllDays
FROM CTE
OPTION(MAXRECURSION 0)

-- Data not truncated here.
SELECT ad.[Date]
    ,FORMAT(ad.[Date],'yyyy-MMM') [Month]
    ,ISNULL(d.TotalAmountIncome,0) TotalAmountIncome
    ,ISNULL(d.TotalAmountRefunded,0) TotalAmountRefunded
    ,ISNULL(d.ProductCostIncome,0) ProductCostIncome
    ,ISNULL(d.ProductCostRefunded,0) ProductCostRefunded
FROM  #DrillThroughData d
RIGHT JOIN #AllDays ad
    ON d.[Date] = ad.Date
ORDER BY SNo
FOR JSON AUTO


SET @DrillThroughData = (SELECT ad.[Date]
    ,FORMAT(ad.[Date],'yyyy-MMM') [Month]
    ,ISNULL(d.TotalAmountIncome,0) TotalAmountIncome
    ,ISNULL(d.TotalAmountRefunded,0) TotalAmountRefunded
    ,ISNULL(d.ProductCostIncome,0) ProductCostIncome
    ,ISNULL(d.ProductCostRefunded,0) ProductCostRefunded
FROM  #DrillThroughData d
RIGHT JOIN #AllDays ad
    ON d.[Date] = ad.Date
ORDER BY SNo
FOR JSON AUTO)

-- Data is being Truncated even though the varaible is nvarchar(max)
SELECT @DrillThroughData

The actual length of the json when not truncated is 88441, but it is truncated at 13680.

Thanks in advance.

this seems to be a limitation of ssms..check this answers :https://stackoverflow.com/questions/4833549/nvarcharmax-still-being-truncated — TheGameiswar, Aug 02 '17 at 04:58
Possible duplicate of [how to get the full resultset from SSMS](https://stackoverflow.com/questions/8930034/how-to-get-the-full-resultset-from-ssms) — TheGameiswar, Aug 02 '17 at 05:14
You can always check the length of the contents (in bytes): `SELECT datalength(@DrillThroughData);` — Roger Wolf, Aug 02 '17 at 06:18
Thanks @TheGameiswar, This is a limitation of SSMS, I'm getting the full data in the application. — Joe Samraj, Aug 04 '17 at 08:39
Possible duplicate of [nvarchar(max) still being truncated](https://stackoverflow.com/questions/4833549/nvarcharmax-still-being-truncated) — Ahmed Yousif, Jul 17 '18 at 06:12

Data Truncated in SQL Server when assign json to variable

0 Answers0