Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Stored procedure limits??

Author  Topic 

Starting Member

1 Post

Posted - 2007-03-11 : 08:16:15
I have a stored procedure in my SQL Server 2000 database that has a large number of different queries (about 200 or so all up) over which a cursor iterates. This procedure works perfectly in the Query Analyser. However, when I attempt to run this stored procedure in Enterprise Manager in a SQL query pane or through a SQL Server Agent job, it fails to complete.

There are no error messages and the error status is 0. But not all of the queries are executed. It simply seems to stop partway through. By varying the number of queries I can get it to stop in different places. To me this suggests that the query has a timeout limit or a limit on the number of queries or something like that. However, I can't figure out what or where that limit is. I should point out that in the Query Analyzer, the whole query takes less than 2 seconds anyway.

I have also tried splitting my large stored procedure into several smaller procedures to no effect.

Anyone out there got any idea why or how this is happening?

Yak Posting Veteran

96 Posts

Posted - 2007-03-13 : 08:00:57
From a troubleshooting standpoint I would place some print statements in the file and print after each section/query.

Also do you have query governor turned on? Could be the 'cost' of the query is exceeding the governor setting and therefore SQL is not letting it run. What does the execution plan tell you.

A cursor and 200 queries seem awful fast for 2 secs unless there are a lot of skipped queries.

Raymond Laubert
MCDBA, MCITP:Administration, MCT
Go to Top of Page

- Advertisement -