SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Stored procedure limits??
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

eagleprof
Starting Member

1 Posts

Posted - 03/11/2007 :  08:16:15  Show Profile  Reply with Quote
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?

rlaubert
Yak Posting Veteran

USA
96 Posts

Posted - 03/13/2007 :  08:00:57  Show Profile  Visit rlaubert's Homepage  Send rlaubert a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000