Taras
asked on
Size of Script put in NVARCHAR(MAX) variable
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'
PRINT @SQLStr
EXEC sp_executesql @SQLStr
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'
PRINT @SQLStr
EXEC sp_executesql @SQLStr
ASKER
cyberkiw
i;
They are pretty long CTE tables. And I can not show them as they are. I can try to rename tables and fields that will take a time.
Just to mention that if I take that CTE and run that separately it runs ok.
To simplify it if I have it like this I get error.
;With CTE_1
As
Select(…….),
CTE_2
As
Select(…..),
CTE_3
As
Select(…..),
CTE_4
As
Select(…..)
Select CTE_1.aa,CT_2.bb,CTE_3.cc, CTE_4.dd
From
CTE_1 Inner Join
CTE_2
On
CTE_1.xx =CTE_2.xx
Inner Join
CTE_3
On……..
If I take out
One of those CTE out then adjusts joins and shortens size of script it will run.
e.g. I will take out second CTE_2 , it could be any one.
;With CTE_1
As
Select(…….),
CTE_3
As
Select(…..),
CTE_4
As
Select(…..)
Select CTE_1.aa,CTE_3.cc,CTE_4.dd
From
CTE_1 Inner Join
CTE_3
On
CTE_1.xx =CTE_3.xx
Inner Join
CTE_4
On……..
They are pretty long CTE tables. And I can not show them as they are. I can try to rename tables and fields that will take a time.
Just to mention that if I take that CTE and run that separately it runs ok.
To simplify it if I have it like this I get error.
;With CTE_1
As
Select(…….),
CTE_2
As
Select(…..),
CTE_3
As
Select(…..),
CTE_4
As
Select(…..)
Select CTE_1.aa,CT_2.bb,CTE_3.cc,
From
CTE_1 Inner Join
CTE_2
On
CTE_1.xx =CTE_2.xx
Inner Join
CTE_3
On……..
If I take out
One of those CTE out then adjusts joins and shortens size of script it will run.
e.g. I will take out second CTE_2 , it could be any one.
;With CTE_1
As
Select(…….),
CTE_3
As
Select(…..),
CTE_4
As
Select(…..)
Select CTE_1.aa,CTE_3.cc,CTE_4.dd
From
CTE_1 Inner Join
CTE_3
On
CTE_1.xx =CTE_3.xx
Inner Join
CTE_4
On……..
Is the error you are getting
maximum recursion reached or something like that?
At the very end of your big query, add the line
option (maxrecursion 1000)
maximum recursion reached or something like that?
At the very end of your big query, add the line
option (maxrecursion 1000)
ASKER
Hi cyberdiwi.
Here is my script :
DECLARE @SQLStr NVARCHAR(Max)
SELECT @SQLStr = COALESCE(@SQLStr + ',', '') + '[' + [a].[Column] + ']'
FROM (Select DISTINCT Service as [Column]
From dbo.tblSUtilization) as a
SET @SQLStr=';with CTE_C as (SELECT
c.Cust_Id
,c.CustNum
,e_a.Event_Reference_Numbe r as BRN
,ecl.Caseload
,ecl.Start_Date as Caseload_Start_Date
,ecl.End_Date as Caseload_End_Date
,ecd.Code
,ecd.Start_Date As Code_Start_Date
,ecd.End_Date AS Code_End_Date
,e_a.Event_Start_Date
,e_a.Event_End_Date
From dbo.Customer c
LEFT OUTER JOIN
dbo.Event as e_a
ON
c.Cust_Id = e_a.Cust_Id
LEFT OUTER JOIN
dbo.Event_Caseloads ecl
ON
e_a.Event_Id = ecl.Event_Id
LEFT OUTER JOIN
dbo.Event_Coding ecd
ON
e_a.Event_Id = ecd.Event_Id
where
ecd.Code IN(''HHH-AAAA'',''HHH-BBBB '')
And
ecl.Caseload In(''MMM1'',''MMM2'')
and
ecl.Is_Active = 1 ),
CTE_E as ( SELECT
c.Cust_Id
,l.Display_Name as New_Event_Spec_Event_Loc
,''Yes''as New_Event_Caseload
,e_a.Event_Source as New_Event_Source
,ecl.Event_Id
,ecl.Start_Date as Caseload_Start_Date
From dbo.Customer c
LEFT OUTER JOIN
dbo.Event as e_a
ON
c.Cust_Id = e_a.Cust_Id
LEFT OUTER JOIN
dbo.Event_Caseloads ecl
ON
e_a.Event_Id = ecl.Event_Id
LEFT OUTER JOIN
dbo.Event_Coding ecd
ON
e_a.Event_Id = ecd.Event_Id
LEFT OUTER JOIN
dbo.Location l
ON
e_a.location_Id = l.location_Id
where
ecl.Event_Id in (
select [Event_Id]
from dbo.Event_Caseloads group by [Event_Id] having count(*) = 1)
And
ecd.Code IN(''HHH-AAAA'',''HHH-BBBB '')
And
ecl.Caseload In(''MMM1'',''MMM2'')
and
ecl.Is_Active = 1
),
CTE_G
AS
(SELECT
Cust_Id
,New_Event_Spec_Event_Loc
,New_Event_Caseload
,New_Event_Source
FROM
(SELECT
CTE_E.Cust_Id
,CTE_E.New_Event_Spec_Even t_Loc
,CTE_E.New_Event_Caseload
,CTE_E.New_Event_Source
,CTE_E.Caseload_Start_DAte
,ROW_NUMBER()OVER(Partitio n BY CTE_E.Event_Id Order BY Caseload_Start_Date DESC) As RN
FROM CTE_E)k
Where
RN = 1),
CTE_L
as ( SELECT
c.cust_Id
,''Yes'' as T_Caseload
,e_a.Event_Source as T_Caseload_Event_source
,l.Display_Name as T_Event_Spec_Event_Loc
,ecl.Event_Id
,ecl.Start_Date as Caseload_Start_Date
From dbo.Customer c
LEFT OUTER JOIN
dbo.Event as e_a
ON
c.Cust_Id = e_a.Cust_Id
LEFT OUTER JOIN
dbo.Event_Caseloads ecl
ON
e_a.Event_Id = ecl.Event_Id
LEFT OUTER JOIN
dbo.Event_Coding ecd
ON
e_a.Event_Id = ecd.Event_Id
LEFT OUTER JOIN
dbo.Location l
ON
e_a.Location_Id = l.Location_Id
where
ecl.Event_Id in (
select [Event_Id]
from dbo.Event_Caseloads group by [Event_Id] having count(*) > 1)
And
ecd.Code IN(''HHH-AAAA'',''HHH-BBBB '')
And
ecl.Caseload In(''MMM1'',''MMM2'')
and
ecl.Is_Active = 1
),
CTE_M
AS
(SELECT
Cust_Id
,T_Caseload
,T_Caseload_Event_source
,T_Event_Spec_Event_Loc
FROM
(SELECT
CTE_L.Cust_Id
,CTE_L.T_Caseload
,CTE_L.T_Caseload_Event_so urce
,CTE_L.T_Event_Spec_Event_ Loc
,CTE_L.Caseload_Start_Date
,ROW_NUMBER()OVER(Partitio n BY CTE_L.Event_Id Order BY Caseload_Start_Date DESC)As RN
FROM CTE_L)n
Where
RN = 1),
CTE_D As(Select coh.Cust_Id,COUNT(coh.Cust _Id)as Count_On_Hold
From dbo.Cust_On_Holds as coh
LEFT OUTER JOIN
dbo.Event as r_b
ON
coh.Cust_Id = r_b.Cust_Id
LEFT OUTER JOIN
dbo.Event_Caseloads ecl_b
ON
e_b.Event_Id = ecl_b.Event_Id
LEFT OUTER JOIN
dbo.Event_Coding ecd_b
ON
e_b.Event_Id = ecd_b.Event_Id
where
ecd_b.Code IN(''HHH-AAAA'',''HHH-BBBB '')
And
ecl_b.Caseload In(''MMM1'',''MMM2'')
and
ecl_b.Is_Active = 1
group by coh.Cust_Id)
SELECT HIN
,BRN
,New_Event_Caseload
,New_Event_Spec_Event_Loc
,New_Event_Source
,T_Caseload
,Code
,Count_On_Hold
,Code_Start_Date
,Code_End_Date
,Event_Start_Date
,Event_End_Date
,Caseload_Start_Date
,Caseload_End_Date
,Case
When IsNull(Code_End_Date,'''') = '''' then
DATEDIFF(dd,Code_Start_Dat e,GETDATE( ))
Else
DATEDIFF(dd,Code_Start_Dat e,Code_End _Date)
end as LOS, ' + @SQLStr + ' From (Select
a.CustNum
,b.BRN
,g.New_Event_Caseload
,g.New_Event_Spec_Event_Lo c
,g.New_Event_Source
,m.T_Caseload
,b.code
,b.Code_start_Date
,b.Code_End_Date
,b.Event_Start_Date
,b.Event_End_Date
,b.Caseload_Start_Date
,b.Caseload_End_Date
,d.Count_On_Hold
,a.Service
,a.Units from dbo.tblSUtilization a
inner join CTE_C b on a.CustNum = b.CustNum
Left Outer Join
CTE_D d
on
b.Cust_Id = d.Cust_Id
Left Outer Join CTE_G g
On
b.Cust_Id = g.Cust_Id
Left Outer Join CTE_M m
ON
b.Cust_Id = m.Cust_Id
where
a.[Service Date]>= b.Code_Start_date
and
a.[Service Date]<= IsNULL(b.Code_End_Date,Get Date())) sq '
+ 'PIVOT (Sum (Units) For Service IN('
+ @SQLStr + ')) As Pt'
PRINT @SQLStr
EXEC sp_executesql @SQLStr
I tried option (maxrecursion 1000) did not help
Here is my script :
DECLARE @SQLStr NVARCHAR(Max)
SELECT @SQLStr = COALESCE(@SQLStr + ',', '') + '[' + [a].[Column] + ']'
FROM (Select DISTINCT Service as [Column]
From dbo.tblSUtilization) as a
SET @SQLStr=';with CTE_C as (SELECT
c.Cust_Id
,c.CustNum
,e_a.Event_Reference_Numbe
,ecl.Caseload
,ecl.Start_Date as Caseload_Start_Date
,ecl.End_Date as Caseload_End_Date
,ecd.Code
,ecd.Start_Date As Code_Start_Date
,ecd.End_Date AS Code_End_Date
,e_a.Event_Start_Date
,e_a.Event_End_Date
From dbo.Customer c
LEFT OUTER JOIN
dbo.Event as e_a
ON
c.Cust_Id = e_a.Cust_Id
LEFT OUTER JOIN
dbo.Event_Caseloads ecl
ON
e_a.Event_Id = ecl.Event_Id
LEFT OUTER JOIN
dbo.Event_Coding ecd
ON
e_a.Event_Id = ecd.Event_Id
where
ecd.Code IN(''HHH-AAAA'',''HHH-BBBB
And
ecl.Caseload In(''MMM1'',''MMM2'')
and
ecl.Is_Active = 1 ),
CTE_E as ( SELECT
c.Cust_Id
,l.Display_Name as New_Event_Spec_Event_Loc
,''Yes''as New_Event_Caseload
,e_a.Event_Source as New_Event_Source
,ecl.Event_Id
,ecl.Start_Date as Caseload_Start_Date
From dbo.Customer c
LEFT OUTER JOIN
dbo.Event as e_a
ON
c.Cust_Id = e_a.Cust_Id
LEFT OUTER JOIN
dbo.Event_Caseloads ecl
ON
e_a.Event_Id = ecl.Event_Id
LEFT OUTER JOIN
dbo.Event_Coding ecd
ON
e_a.Event_Id = ecd.Event_Id
LEFT OUTER JOIN
dbo.Location l
ON
e_a.location_Id = l.location_Id
where
ecl.Event_Id in (
select [Event_Id]
from dbo.Event_Caseloads group by [Event_Id] having count(*) = 1)
And
ecd.Code IN(''HHH-AAAA'',''HHH-BBBB
And
ecl.Caseload In(''MMM1'',''MMM2'')
and
ecl.Is_Active = 1
),
CTE_G
AS
(SELECT
Cust_Id
,New_Event_Spec_Event_Loc
,New_Event_Caseload
,New_Event_Source
FROM
(SELECT
CTE_E.Cust_Id
,CTE_E.New_Event_Spec_Even
,CTE_E.New_Event_Caseload
,CTE_E.New_Event_Source
,CTE_E.Caseload_Start_DAte
,ROW_NUMBER()OVER(Partitio
FROM CTE_E)k
Where
RN = 1),
CTE_L
as ( SELECT
c.cust_Id
,''Yes'' as T_Caseload
,e_a.Event_Source as T_Caseload_Event_source
,l.Display_Name as T_Event_Spec_Event_Loc
,ecl.Event_Id
,ecl.Start_Date as Caseload_Start_Date
From dbo.Customer c
LEFT OUTER JOIN
dbo.Event as e_a
ON
c.Cust_Id = e_a.Cust_Id
LEFT OUTER JOIN
dbo.Event_Caseloads ecl
ON
e_a.Event_Id = ecl.Event_Id
LEFT OUTER JOIN
dbo.Event_Coding ecd
ON
e_a.Event_Id = ecd.Event_Id
LEFT OUTER JOIN
dbo.Location l
ON
e_a.Location_Id = l.Location_Id
where
ecl.Event_Id in (
select [Event_Id]
from dbo.Event_Caseloads group by [Event_Id] having count(*) > 1)
And
ecd.Code IN(''HHH-AAAA'',''HHH-BBBB
And
ecl.Caseload In(''MMM1'',''MMM2'')
and
ecl.Is_Active = 1
),
CTE_M
AS
(SELECT
Cust_Id
,T_Caseload
,T_Caseload_Event_source
,T_Event_Spec_Event_Loc
FROM
(SELECT
CTE_L.Cust_Id
,CTE_L.T_Caseload
,CTE_L.T_Caseload_Event_so
,CTE_L.T_Event_Spec_Event_
,CTE_L.Caseload_Start_Date
,ROW_NUMBER()OVER(Partitio
FROM CTE_L)n
Where
RN = 1),
CTE_D As(Select coh.Cust_Id,COUNT(coh.Cust
From dbo.Cust_On_Holds as coh
LEFT OUTER JOIN
dbo.Event as r_b
ON
coh.Cust_Id = r_b.Cust_Id
LEFT OUTER JOIN
dbo.Event_Caseloads ecl_b
ON
e_b.Event_Id = ecl_b.Event_Id
LEFT OUTER JOIN
dbo.Event_Coding ecd_b
ON
e_b.Event_Id = ecd_b.Event_Id
where
ecd_b.Code IN(''HHH-AAAA'',''HHH-BBBB
And
ecl_b.Caseload In(''MMM1'',''MMM2'')
and
ecl_b.Is_Active = 1
group by coh.Cust_Id)
SELECT HIN
,BRN
,New_Event_Caseload
,New_Event_Spec_Event_Loc
,New_Event_Source
,T_Caseload
,Code
,Count_On_Hold
,Code_Start_Date
,Code_End_Date
,Event_Start_Date
,Event_End_Date
,Caseload_Start_Date
,Caseload_End_Date
,Case
When IsNull(Code_End_Date,'''')
DATEDIFF(dd,Code_Start_Dat
Else
DATEDIFF(dd,Code_Start_Dat
end as LOS, ' + @SQLStr + ' From (Select
a.CustNum
,b.BRN
,g.New_Event_Caseload
,g.New_Event_Spec_Event_Lo
,g.New_Event_Source
,m.T_Caseload
,b.code
,b.Code_start_Date
,b.Code_End_Date
,b.Event_Start_Date
,b.Event_End_Date
,b.Caseload_Start_Date
,b.Caseload_End_Date
,d.Count_On_Hold
,a.Service
,a.Units from dbo.tblSUtilization a
inner join CTE_C b on a.CustNum = b.CustNum
Left Outer Join
CTE_D d
on
b.Cust_Id = d.Cust_Id
Left Outer Join CTE_G g
On
b.Cust_Id = g.Cust_Id
Left Outer Join CTE_M m
ON
b.Cust_Id = m.Cust_Id
where
a.[Service Date]>= b.Code_Start_date
and
a.[Service Date]<= IsNULL(b.Code_End_Date,Get
+ 'PIVOT (Sum (Units) For Service IN('
+ @SQLStr + ')) As Pt'
PRINT @SQLStr
EXEC sp_executesql @SQLStr
I tried option (maxrecursion 1000) did not help
ASKER
Just to add.
I think that something must be wrong with size of variable.
If size of my variable –number of character that goes in is less than 4000 query will go and run ok.
Let say e.g. size of variable is 3500 if I add a comment that is large 555 characters it will gave me error.
It looks that is not CTE tables that are causing problem.
If it reacts on size of comment, I am sure that comment should not interfere with code in any program comment is comment.
I think that something must be wrong with size of variable.
If size of my variable –number of character that goes in is less than 4000 query will go and run ok.
Let say e.g. size of variable is 3500 if I add a comment that is large 555 characters it will gave me error.
It looks that is not CTE tables that are causing problem.
If it reacts on size of comment, I am sure that comment should not interfere with code in any program comment is comment.
ASKER
I definitely have issue with NVARCHAR(MAX) that will except only 4000 characters.
Is it possible to break my script in several variables and use EXEC.
Is it possible to broke my code in several variables and use, How to do that?
And go with
EXEC(@sql1 + @sql2) and not to use
EXEC sp_executesql @SQLStr
Is it possible to break my script in several variables and use EXEC.
Is it possible to broke my code in several variables and use, How to do that?
And go with
EXEC(@sql1 + @sql2) and not to use
EXEC sp_executesql @SQLStr
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I found solution that worked for me
ASKER
My solution worked
incorrectly.
Is it safe (nothing proprietary) to show all your SQL? with and without the 4th CTE?