SQL - Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Posted on 2009-02-08
Last Modified: 2012-05-06
We've just moved our website to a new server from SQL2000 to SQL2008 web edition.

Now when we try to use the CMS to change the website and implement the changes we get this error.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Any ideas on a fix?  It was working fine before
Question by:andrewmilner
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >It was working fine before
    something has changed :)
    for example, the max nestlevel is set by default to 32.

    now, if you have a trigger that does need this hight level, you might have a problem anyhow.
    can you identify and post the offending trigger?

    Author Comment

    [Microsoft][ODBC SQL Server Driver][SQL Server]Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

    /merchantadmin/catalogue/implementparams_subcatpages.asp, line 169

    Looks like it's the last line - template.execute.

    Is that a trigger then?

    Any ideas?
    '*******************GENERATE THE SUBCATEGORY PAGES**********************
     set oRScsb = Server.CreateObject("ADODB.Recordset")
    ' Maciej - 11.04.2005 - I added : 
    '	support for parent(category) template if current subcategory has no template
    '	I fixed the problem with getting templateid of current subcategory.
    '		Mark, your idea of getting templateID from templates ( inner join templetes ) was good however you forgot to 
    '		use it : you were still using sc.templateid instead of template.temp_id
    ' old query
    ' ExecuteString = "select sc.CatID,subcatid, sc.templateid, sc.category as subcatname, c.category as catname,sc.imageon, sc.imageoff, sc.imageover, sc.mainimage, temp_type,sc.categorytext1, sc.categorytext2, sc.categorytext3, sc.categorytext4, sc.categorytext5,sc.Notes, template_notes from subcategory sc left outer join category c on sc.catid = c.catid left outer join templates on sc.templateid = temp_id left outer join product_template on template_id = temp_id WHERE sc.[processed] = 0 order by sc.category"
    ' new query
     E =     "SELECT SC.[CatID], SC.[SubCatID], SC.[Category] as SubCatName, C.[Category] as CatName, SC.[ImageOn], SC.[ImageOff], SC.[ImageOver], "
     E = E & "SC.[MainImage], SC.[CategoryText1], SC.[CategoryText2], SC.[CategoryText3], SC.[CategoryText4], SC.[CategoryText5], "
     E = E & "T.[Temp_ID] as TemplateID, T.[Temp_Type], CT.[Temp_ID] as CategoryTemplateID, CT.[Temp_Type] as CategoryTemp_Type, "
     E = E & "SC.[Notes], P.[Template_Notes], CP.[Template_Notes] as CategoryTemplate_Notes "
     E = E & "FROM SubCategory SC "
     E = E & "LEFT OUTER JOIN Category			C  ON SC.[CatID]     = C.[CatID] "
     E = E & "LEFT OUTER JOIN Templates			T  ON SC.[TemplateID]= T.[Temp_ID] "
     E = E & "LEFT OUTER JOIN Product_Template	P  ON P.[Template_ID]= T.[Temp_ID] "
     E = E & "LEFT OUTER JOIN Templates			CT ON C.[TemplateID] = CT.[temp_id] "
     E = E & "LEFT OUTER JOIN Product_Template	CP ON C.[TemplateID] = CP.[Template_ID] "
     E = E & "WHERE SC.[Processed] = 0 "
     E = E & "ORDER BY SC.[Category]"
     set oRs3 = template.execute(E)
     if not oRs3.eof then
           fixcatid = oRs3("catid")
     end if
     subcatlisttxt = ""
     do while not oRs3.EOF 
        t_Catid = oRs3("catid")
        t_subcatid = oRs3("subcatid")
        t_subcatname = trim(oRs3("subcatname"))        
        t_catname = dotrim(oRs3("catname"))
        cimageon = dotrim(oRs3("imageon"))
        cimageoff = dotrim(oRs3("imageoff"))
        cimageover = dotrim(oRs3("imageover"))
        cmainimage = dotrim(oRs3("mainimage"))
        ccategorytext1 = dotrim(oRs3("categorytext1"))
        ccategorytext2 = dotrim(oRs3("categorytext2"))
        ccategorytext3 = dotrim(oRs3("categorytext3"))
        ccategorytext4 = dotrim(oRs3("categorytext4"))
        ccategorytext5 = dotrim(oRs3("categorytext5"))
        ctemplateid = oRs3("templateid")
        ctemp_type = dotrim(oRs3("temp_type"))  
        CategoryTemplateID = oRs3("CategoryTemplateID").value    
        CategoryTempType = dotrim(oRs3("CategoryTemp_Type")) 
        catnotes = oRs3("notes")
        if isnull(catnotes) then
           catnotes = ""
           catnotes = trim(catnotes)
        end if  
        template_notes = oRs3("template_notes")
        if isnull(template_notes) then
           template_notes = ""
           template_notes = trim(template_notes)
        end if
        categorytemplate_notes = oRs3("categorytemplate_notes")
        if isnull(template_notes) then
           categorytemplate_notes = ""
           categorytemplate_notes = trim(categorytemplate_notes)
        end if
        ' set template for subcategory
        cusetemplate = false
        if not isnull(ctemplateid) then
           if ctemplateid > 0 and ctemp_type = "category page" then
              cusetemplate = true
           end if
        end if
        ' set template for category    
        CategoryUseTemplate = false
        If Not isnull(CategoryTemplateID) Then
    		If CategoryTemplateID > 0 And CategoryTempType = "category page" Then
    			CategoryUseTemplate = True
    		End If
    	End If
        if cusetemplate then	
           ReturnString = template_notes	' use template for this subcategory
    		' if there is no template for this subcategory
    		' check for template for category
    		If CategoryUseTemplate Then
    			ReturnString = categorytemplate_notes		' use template for parent(category)
    			If cattemp_id > 0 Then
    				ReturnString = cattemp_notes						' use main default template 
    				ReturnString = catnotes								' if no default page is set	use SubCategory Notes
    			End If
    		End If
    	End If
        if isnull(returnstring) then
           returnstring = ""
        end if
        returnstring = replace(returnstring,"{subcategoryname}",t_subcatname)
        returnstring = replace(returnstring,"{categoryname}",t_catname) 
        url_catname = replace(t_catname," ","_")
    	if useurlrewriting then 
    		ttfile = session("path_info") & "mcp/" & url_catname & ".html"
    		ttfile = session("path_info") & "main.asp?category=" & server.URLencode(t_catname)
    	end if
    	tt = "<a href='" & ttfile & "' title='" & t_catname & "'>" & t_catname & "</a>"
    	returnstring = replace(returnstring, "{category:link}", tt)
    	url_subcatname = replace(t_subcatname," ","_")     
    	if useurlrewriting then
    		ttfile = session("path_info") & "scp/" & url_catname & "/" & url_subcatname & ".html"
    		ttfile = session("path_info") & "products.asp?subcategory=" & server.URLencode(t_subcatname) & "&category=" & server.URLencode(t_catname)
    	end if
    	tt = "<a href='" & ttfile & "' title='" & t_subcatname & "'>" & t_subcatname & "</a>"
      	returnstring = replace(returnstring, "{subcategory:link}", tt) 
        returnstring = replace(returnstring,"{longdescription}",catnotes)
        cmainimagerendered = "<img src='" & path_info & "/images/" & cmainimage & "' border=0>"
        returnstring = replace(returnstring,"{mainimage}",cmainimagerendered)
        returnstring = replace(returnstring,"{mainimagename}",cmainimage)
        csmallimagerendered = "<img src='" & path_info & "/images/" & cimageoff & "' border=0>"    
        returnstring = replace(returnstring,"{smallimage}",csmallimagerendered)
        returnstring = replace(returnstring,"{smallimagename}",cimageoff)  
        returnstring = replace(returnstring,"{catvalue1}",ccategorytext1)
        returnstring = replace(returnstring,"{catvalue2}",ccategorytext2)      
        returnstring = replace(returnstring,"{catvalue3}",ccategorytext3)
        returnstring = replace(returnstring,"{catvalue4}",ccategorytext4)       
        returnstring = replace(returnstring,"{catvalue5}",ccategorytext5)
        'parse for {catsummary:xx} tags (ie category summary templates)
        returnstring = cattemplateparser(returnstring, "category",t_catid)
        'parse for {subcatsummary:xx} tags (ie subcategory summary templates)
        returnstring = cattemplateparser(returnstring, "subcategory",t_catid)     
        returnstring = featuredparser("subcategory",t_subcatname, returnstring)
        returnstring = sitemap(returnstring)
        returnstring = tagreplace(returnstring,"subcategory", t_catid, t_subcatid, t_catname, t_subcatname, "ParamSearch" & t_subcatid)
        returnstring = replace(returnstring,"{subcatid}",t_subcatid)    
        quadrentmaker returnstring, "subcategory", t_catname & t_subcatname
     ExecuteString = "UPDATE subcategory SET [processed] = 1"

    Open in new window


    Author Comment

    Any ideas?

    Author Comment

    The only thing that has changed is the database ie now 2008 web edition was 2000.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >Is that a trigger then?

    yes, you surely have a trigger on subcategory table.

    Accepted Solution

    Okay fixed it.  The kind support guys of the server host provided the solution.....
    Disable Nested Triggers within the main instance settings.


    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    734 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

    17 Experts available now in Live!

    Get 1:1 Help Now