Solved

Size of Script put in NVARCHAR(MAX) variable

Posted on 2010-11-17
9
259 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:Taras
  • 7
  • 2
9 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34160677
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?
0
 

Author Comment

by:Taras
ID: 34161519
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……..



0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34161996
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)
0
 

Author Comment

by:Taras
ID: 34164341
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Taras
ID: 34164767
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.
0
 

Author Comment

by:Taras
ID: 34165326
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
0
 

Accepted Solution

by:
Taras earned 0 total points
ID: 34167048
I found solution
I had to declare variable as Varchar(8000)
and remove sp_executesql
as sp_executesql ask for ntext,nchar,nvarchar.
thanks anyway for try.
0
 

Author Closing Comment

by:Taras
ID: 37517351
I found solution that worked for me
0
 

Author Comment

by:Taras
ID: 37494508
My solution worked
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now