April 1, 2002 at 8:52 am
Hi,
I want to create a DTS for execute a stored procedures that use a nested stored procedure (for get a hierarchical information), but when i want to do with a Execute SQL Task i get a error "Maximum stored procedure, function, trigger or view nesting level exceeded (limit 32)", but when i run the sp in the Query Analyzer it's right, also, when i use the Query Designer in the DTS Designer it's OK, but when i want to accept (Press OK button) i get the error.
I'm using a SQLServer 2000 without any Service Pack. I don't know what is the problem.
I helpfull to somebody can help me. It's urgent. Thanks
Deusdit Correa Cornejo
----------------------
Br. Computer Science
Microsoft Certified Database Administrator
Analyst Programmer
----------------------
Deusdit Correa Cornejo
----------------------
Br. Computer Science
Microsoft Certified Database Administrator
Analyst Programmer
----------------------
April 1, 2002 at 10:42 am
You are calling more than 32 procedures. The nesting is a stack of the callers of the program and it is limited to 32. The DTS package may be one or two, so you need to call less nested procedures.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 1, 2002 at 1:16 pm
Thanks Steve, but this is not the problem, because in Query Analyzer all it's correct and i don't get the error the limit execeed. The problem is with DTS Designer.
Do you know if is necesary some special setting for run a nested procedure in the DTS Designer?
Thanks again.
quote:
You are calling more than 32 procedures. The nesting is a stack of the callers of the program and it is limited to 32. The DTS package may be one or two, so you need to call less nested procedures.Steve Jones
Deusdit Correa Cornejo
----------------------
Br. Computer Science
Microsoft Certified Database Administrator
Analyst Programmer
----------------------
Deusdit Correa Cornejo
----------------------
Br. Computer Science
Microsoft Certified Database Administrator
Analyst Programmer
----------------------
April 1, 2002 at 3:06 pm
Ran this in Northwind:
alter procedure spTwo
as
declare @i int
select @i = orderid
from orders
return @i
go
alter procedure spOne
as
declare @j-2 int
exec @j-2 = spTwo
select * from orders
where orderid = @j-2
return
spOne
Created DTS package that has a single SQL connection and a Stored Proc task that calls spOne. Works manually in DTS and as a scheduled job. Are you sure you are not calling something. Are there views called in these procedures?
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply