Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
eagleprof
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? |
|
|
rlaubert
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 LaubertMCDBA, MCITP:Administration, MCT |
 |
|
|
|
|
|
Stored procedure limits??