Size of Script put in NVARCHAR(MAX) variable
Posted on 2010-11-17
I have this script below that I am running against SQLServer2008 to pull out
Dynamic pivot query.
In part where is --- CTE_tblProduct as (select …from ….)--- instead I have three CTE tables and they are working ok.Script runs and gave out query.
However when I put one more CTE table there I got error.
It cut of part of my script it looks like variable @SQLstr is not large enough. I checked size of string is 4600 characters.
If I drop out my last CTE from script,script is size around 3000 and it runs ok.
I checked single quotes in my string and double them.
I thought that declaring variable NVARCHAR(MAX) will give me option to put large script in as in my case.
Where I am making mistake?
DECLARE @SQLStr NVARCHAR(MAX)
SELECT @SQLStr = COALESCE(@SQLStr + ',', '') + '[' + [a].[Column] + ']'
FROM (Select DISTINCT Service as [Column]
From dbo.tblUtilization) as a
SET @SQLStr=';with CTE_tblProduct as ( select .... from .... )SELECT ProdID, ProdDesc,' + @SQLStr
+ 'From(Select a.ProdID, b.ProdDesc, a.Service,a.Units from dbo.Utilization a
inner join CTE_tblProduct b on a.ProdID = b.ProdID) sq '
+ ' PIVOT (Sum (Units) For Service IN('
+ @SQLStr + ')) As Pt'
EXEC sp_executesql @SQLStr