Link to home
Start Free TrialLog in
Avatar of Taras
TarasFlag for Canada

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
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

I am guessing that you may have done this step
However when I put one more CTE table there I got error.
incorrectly.
Is it safe (nothing proprietary) to show all your SQL? with and without the 4th CTE?
Avatar of Taras

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……..



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)
Avatar of Taras

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_Number 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_Event_Loc
 ,CTE_E.New_Event_Caseload
 ,CTE_E.New_Event_Source
 ,CTE_E.Caseload_Start_DAte
 ,ROW_NUMBER()OVER(Partition 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_source
 ,CTE_L.T_Event_Spec_Event_Loc
 ,CTE_L.Caseload_Start_Date
 ,ROW_NUMBER()OVER(Partition 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_Date,GETDATE())
             Else
                        DATEDIFF(dd,Code_Start_Date,Code_End_Date)
             end as LOS, ' + @SQLStr + ' From (Select
              a.CustNum
              ,b.BRN
              ,g.New_Event_Caseload
              ,g.New_Event_Spec_Event_Loc
,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,GetDate())) sq '
 +  'PIVOT (Sum (Units) For Service IN('
 + @SQLStr + ')) As Pt'
 PRINT @SQLStr
 EXEC sp_executesql @SQLStr
 
I tried option (maxrecursion 1000)  did not help
Avatar of Taras

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.
Avatar of Taras

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
ASKER CERTIFIED SOLUTION
Avatar of Taras
Taras
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Taras

ASKER

I found solution that worked for me
Avatar of Taras

ASKER

My solution worked