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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-02-27 : 08:31:02
|
| gasbag writes "Hi,I've been getting a problem with cursors that you might be able to help me on. The problem is this:I have a stored procedure that creates a temporary table that stores a selected list of values from a user table. I then declare a forward_only cursor to loop through the recordsin the temp table to do some data comparisons. The cursor is declared and opened successfully, the problem starts when the proc starts looping through the cursors records. Usually, the cursor manages to loop through about 50 or so records but then the loop stops and the proc is terminated immediately. The cursor's record size can vary alot and can be anywhere between 100 to 100,000+ records. No matter what size the cursor is, after looping after say 50+ records the looping stops and the proc is terminated. The number of records it can loop through also varies, there is never a consistent number. Oh yeah, no errors are returned either. As far as my calling app is concerned, the proc ran successfully.I ran a trace on the proc which showed some interesting things. After the DECLARE CURSOR statement in the proc, this exception was returned:Error: 208, Severity: 16, State: 0There was no exception description. After this exception, the cursor was declared again and the proc carried on. No more exceptions were returnedAnother thing to consider, running the code directly in query analyser works. If I call the proc from a third party app using ADO, the proc fails.Hope you can help, coz this is starting to do my head in!!!Thanks" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-27 : 08:45:05
|
| Don't declare a cursor on the temp table.You can put an identity on it and loop through that if you mustdeclare @id int, @maxid intselect @id = 0, @maxid = max(id) from #awhile @id < @maxidbeginselect @id = min(id) from #a where id > @idselect * from #a where id = @idendThen you can still access the whole of #a if you need to.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
Cursor problems