Re: max sp, func, trig, or view nesting level...

From: Steve Kass (skass_at_drew.edu)
Date: 07/02/04 

Date: Thu, 01 Jul 2004 23:46:29 -0400

Beats me. While it doesn't explain the error, I don't quite see what
you're doing here - it may make sense, but inserting into a table a
cursor is based on in the middle of cursoring through is slippery
business. I don't know if the order in which the cursor rows are
visited is defined (thought it might not matter here). If donor (the
cursor) is empty, you insert into donor (the table), which then makes
donor (the cursor) not empty, and you hope to then insert from what just
went into the cursor into donorDonations... Is there really no way to
do this with simple insert queries?

The only think I can imagine is causing the trouble is if you have
something that can cause recursion - an indexed view somewhere?

I don't think Excel is involved, so can you possibly pare this down to
some sample data on which you get the error? I tried and failed to
cause an error.

SK

Alex wrote:

>No triggers - anywhere...
>
>
>"Steve Kass" wrote:
>
>
>
>>Did you look for triggers?
>>
>>SK
>>
>>Alex wrote:
>>
>>
>>
>>>None. That's the strange part....
>>>Does the Proc code seem good to you?
>>>
>>>All I'm doing is checking if record exists in the Donor table that matches that of sys_load. If it does, create a new record in DonorDonations, if it doesn't create a new record in the Donor table....
>>>
>>>I'm stumped. Thanks for your help, Steve.
>>>
>>>
>>>"Steve Kass" wrote:
>>>
>>>
>>>
>>>
>>>
>>>>Alex,
>>>>
>>>> Is there a trigger on dbo.Donor, dbo.DonorDonation, or dbo.sys_load?
>>>>I suspect the problem is not with the code you've posted here, since it
>>>>doesn't seem to be recursive.
>>>>
>>>>Steve Kass
>>>>Drew University
>>>>
>>>>Alex wrote:
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>>Hi,
>>>>>I have created the following procedure, which is returning the Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32) error message.
>>>>>
>>>>>Your assistance is appreciated....
>>>>>
>>>>>BEGIN
>>>>>SET nocount on
>>>>>TRUNCATE TABLE sys_load -- load the sys_load table initially
>>>>>insert into dbo.sys_load (FirstName, LastName, Address, City, State, Zip, Phone, Prayer, DateEntered, Station)
>>>>>select "First Name", "Last Name", "Address", "City", "State", "Zip", "Phone", "Prayer", "Date Entered", "Station"
>>>>>from OpenRowset('Microsoft.Jet.OLEDB.4.0',
>>>>>'Excel 8.0;Database=C:\MyExcelog.xls',
>>>>>'select * from [Excel Export File$]')
>>>>>-- see if a record already exists for the new Donor
>>>>>declare @intId int, @FirstName nvarchar(64), @LastName nvarchar(64),@Country nvarchar(64), @Email nvarchar(64)
>>>>>declare donor cursor for
>>>>> select a.intId, a.FirstName, a.LastName, (case when Country='Canada' then 'TN' else 'TU' end) Country, a.Email
>>>>> from dbo.Donor a, dbo.sys_load b
>>>>> where a.FirstName = b.FirstName
>>>>> and a.LastName = b.LastName
>>>>>open donor
>>>>>fetch next from donor into @intId, @FirstName, @LastName, @Country, @Email
>>>>>while (@@FETCH_STATUS != -1)
>>>>>begin
>>>>>-- NO Donor Found with that FirstName, LastName
>>>>>if (@@FETCH_STATUS = -2)
>>>>> begin
>>>>> insert into dbo.Donor (FirstName, LastName, City, State, Zip, Phone,CompanyID, DateAdded)
>>>>> select a.FirstName, a.LastName, a.City, a.State, a.Zip, a.Phone, 1,getdate()
>>>>> from dbo.sys_load a
>>>>> fetch next from donor into @intId, @FirstName, @LastName, @Country, @Email
>>>>> print 'Record created in the Donor table'
>>>>> end
>>>>>else
>>>>> -- if donor record found, insert a new record into DonorDonations
>>>>> begin
>>>>> while (@@FETCH_STATUS = 0)
>>>>> begin
>>>>> insert into dbo.DonorDonations (intId, OrderTerms, CreditCardType,Amount, DateReceived, Source, Operator, LetterId, Status)
>>>>> values (@intId, 1, 0, 0, getdate(), @Country, @Email, @Country, 1)
>>>>> fetch next from donor into @intId, @FirstName, @LastName, @Country, @Email
>>>>> end
>>>>> end
>>>>>end
>>>>>close donor
>>>>>deallocate donor
>>>>>END
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>>
>>
>>

Relevant Pages

  • Re: F8 Evolution and dropped reply-to style
    ... But then, Just as Stuart also noted, why is it inserting the empty line at ... with the cursor on that line. ... Nor am I looking for a flame war. ...
    (Fedora)
  • Re: F8 Evolution and dropped reply-to style
    ... But then, Just as Stuart also noted, why is it inserting the empty line at ... with the cursor on that line. ... Nor am I looking for a flame war. ...
    (Fedora)
  • Re: sp problem...
    ... this proc? ... For future reference... ... > you can accomplish this without cursor. ... > insert donor ...
    (microsoft.public.sqlserver.programming)
  • Re: sp problem...
    ... The cursor will not give you the rows in sys_load that do not ... exist in donor table. ... declare @a sysname, @b sysname ... fetch next from cc into @a,@b ...
    (microsoft.public.sqlserver.programming)
  • Re: sp problem...
    ... > exist in donor table. ... > If you want to do the cursor route, you would do something like this. ... > fetch next from cc into @a,@b ...
    (microsoft.public.sqlserver.programming)