Solved

Size of Script put in NVARCHAR(MAX) variable

Posted on 2010-11-17
9
258 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 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

20 Experts available now in Live!

Get 1:1 Help Now