April 4, 2007 at 8:35 am
Hi all,
I am getting this error and it looked wierd
4/3/2007 11:50:05 PM - ERROR : -2147217900 - Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). - 1000440
4/3/2007 11:50:05 PM - Error!!!
how can i solve it
April 4, 2007 at 10:05 am
Each time a stored procedure calls another, the nesting level increases. So if I call proc 1, I have a level of 1. If proc 1 updates table A that has a trigger, I'm at a nesting level of 2. If that trigger calls a proc, nesting is 3 and so on.
If you're 32 levels deep, you should reexamine your code.
April 4, 2007 at 10:56 am
bellow is the code
ALTER TRIGGER [dbo].[updateOnErrors] ON [dbo].[bandwidth]
FOR UPDATE
AS
SET NOCOUNT ON
DECLARE @thisAdd VARCHAR(15),
@thisDIR VARCHAR(50),
@thisFN VARCHAR(50),
@thisQ VARCHAR(10),
@thisSer VARCHAR(10),
@thistest VARCHAR(50),
@thisNumber INT,
@firstIP INT,
@SecondIP INT,
@ThirdIP INT
SELECT @thisAdd = i.ipAddress, @thisNumber = i.errorNumber
FROM inserted i
IF @thisNumber = 53
BEGIN
-- Get File, Directory, Sku
SELECT @thisAdd = add, @thisFN = FN_NAME, @thisDIR = Dir, @thistest = SMU
FROM test_tracks
WHERE add= @thisAdd
-- Get Quota
SELECT @thisQ = transfer
FROM Test_Track_SMU
WHERE SMU = @thistest
IF @thisFN <> 'Unknown'
BEGIN
SELECT @firstIP = value
FROM test.dbo.fn_Split (@thisFN, '.')
WHERE idx = 0
SELECT @SecondIP = value
FROM test.dbo.fn_Split (@thisFN, '.')
WHERE idx = 1
SELECT @ThirdIP = value
FROM test.dbo.fn_Split (@thisFN, '.')
WHERE idx = 2
-- Get New MRTG if exists
SELECT @thisSer = mrtg_server
FROM test_MTR
WHERE ip_1 = @firstIP
AND ip_2 = @SecondIP
AND (ip_3 = @ThirdIP OR ip_3 IS NULL)
IF LEN(@thisSer) > 0
BEGIN
SET @thisDIR = '\\' + @thisSer + '\d$\'
END
END
IF @thisDIR IS NOT NULL AND @thisFN IS NOT NULL AND @thisQ IS NOT NULL
BEGIN
UPDATE testwidth WITH (ROWLOCK)
SET DIR = @thisDIR,
FN_NAME = @thisFN,
total = @thisQ
WHERE (ipAddress = @thisAdd)
AND totalquota = 0
END
END
SET NOCOUNT OFF
April 5, 2007 at 1:04 am
Does the table testwidth have a trigger?
there's another problem with your trigger. The way it's written, you will get some very odd behaviour if multi-row updates are done.
If an update affects multiple rows, the trigger is clled once and all the rows are in the inserted table. Your trigger assumes there's only one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2007 at 7:30 am
I ran into something like this theo ther day - it turns out I was accidentally calling the stored procedure from itself. This caused recursion and bombed at the 32nd level.
In my case, I build my sprocs all in one file with three blocks of code:
There should be a GO statement between each step, and I left one out between 2 and 3...
But boss, why must the urgent always take precedence over the important?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply