Very Computer

I'm running a stored procedure building a large cursor (1 mill rows) to
update a large table (64 mill rows).  It stops sometimes with an error
"Could not generate asynchronous keyset.  The cursor has been
deallocated."

Has anyone had this problem?

Thanks for the help

Beverly,

To be honest this is a guess ... but who knows, it might be the right one :-)

If you reference section "Keyset-driven Cursors" in the SQL Server 7.0 Books
Online, it states "The keyset for a keyset-driven cursor is built in tempdb when
the cursor is opened." So it might just be that you need a larger tempdb.

Quote:> I'm running a stored procedure building a large cursor (1 mill rows) to
> update a large table (64 mill rows).  It stops sometimes with an error
> "Could not generate asynchronous keyset.  The cursor has been
> deallocated."

> Has anyone had this problem?

> Thanks for the help

Thanks for the info.

Since I posted this question, I found that this error is a known SQL7 problem and there may be a fix coming.  I found the error on the support website for Microsoft.com.

Our DBA's solution was to re-write the SP without a cursor.  This is not always possible.  What is your feeling about the use of cursors in stored procedures?

Thanks again.

* Sent from Devdex.com http://www.devdex.com The Web Developers Index *
The world's largest index site for Microsoft web technologies.

Beverly,

I believe it is always possible to code an SP without a cursor. Either SQL (the
language) or T-SQL (which supports looping) can be used to avoid cursors.

When possible, straight SQL outperforms cursors usually by an order of
magnitude. The performance difference can vary when one is forced to use T-SQL
and looping.

BTW, thanks to Ron Talmadge for the discussion we had on this point last week.

Quote:> Thanks for the info.

> Since I posted this question, I found that this error is a known SQL7 problem

and there may be a fix coming.  I found the error on the support website for
Microsoft.com.

Quote:

> Our DBA's solution was to re-write the SP without a cursor.  This is not

always possible.  What is your feeling about the use of cursors in stored
procedures?

Quote:

> Thanks again.

> * Sent from Devdex.com http://www.devdex.com The Web Developers Index *
> The world's largest index site for Microsoft web technologies.

Could you point me in the direction of some resources regarding looping in a stored procedure without cursors?  This is really the only reason I'm using a cursor in the first place.  I am attempting to update a 'batch' of rows at a time, then issue a commit.  If I knew how to grab a group of rows without a cursor, this would accomplish the same thing.

* Sent from Devdex.com http://www.devdex.com The Web Developers Index *
The world's largest index site for Microsoft web technologies.

Here are two templates I copy and use when I want impact to be low during
working hours:

/* update every row in a table one row at a time by primary key */
set nocount on



begin
  UPDATE bky SET orig_seller_info = 0 where orig_seller_info is null and


end
go

/* update every row in a table one row at a time */
set nocount on
set rowcount 1  -- or 10 or 100 or n
go



begin
  UPDATE max_hold_bky SET year = xyear, docket_number = xdocket_number where
year is null

end
go
set rowcount 0
go

Could you point me in the direction of some resources regarding looping in a
stored procedure without cursors?  This is really the only reason I'm using
a cursor in the first place.  I am attempting to update a 'batch' of rows at
a time, then issue a commit.  If I knew how to grab a group of rows without
a cursor, this would accomplish the same thing.

* Sent from Devdex.com http://www.devdex.com The Web Developers Index *
The world's largest index site for Microsoft web technologies.