Could Not Complete Cursor Operation

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Could Not Complete Cursor Operation

Author

Topic

agdavidson
Starting Member

17 Posts

Posted - 2010-06-04 : 10:32:40

I am running a stored procedure that uses a cursor to processes a few hundred records each time it runs. It will process a few dozen records successfully and then the procedure fails with this error message: "Could not complete cursor operation because the table schema changed after the cursor was declared." If I kick the SP off again, it will work for awhile, but inevitably it will fail with this same error message somewhere along the way.

I am not changing the table schema, dropping/recreating it, using auto-shrink, or anything else that could cause this. We used to see this error on rare occasions when the SP was on our SQL Server 2005 box, but since we migrated to SQL Server 2008, we run into this problem literally every time the SP executes. Please help! Our VP of Technology has suddenly taken an interest in the problem and really wants it fixed asap! :-\

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-06-04 : 12:16:57

post the code. cursors are the devils spawn. 99.9999% of the time there is a better non-cursor solution.

agdavidson
Starting Member

17 Posts

Posted - 2010-06-04 : 14:37:34

I know they are! But in this case I think it's needed. Maybe I'm wrong?? If there's another way to do that, that would be great!!

USE [CIS]
GO
/****** Object: StoredProcedure [dbo].[ssp_recycle_new] Script Date: 06/02/2010 09:57:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

--Edit History:
--04/18/1999: Originally authored by Randy Beyer
--08/10/1999: Added Trans to recycle (RAB)
--09/06/1999: Added extra parameter for VM recycle (RAB)
--11/09/1999: Added check for DB_BILLING..MAILBOX (RAB)
--11/11/1999: Added recycling of VM2 numbers in Billing (RAB)
--02/15/2001: Excluded recycle of info or reb boxes (Zach Murphy)
--06/02/2010: Major overhaul (AGD)

alter procedure [dbo].[ssp_recycle_new]
as

declare
@i_customer_account int,
@pk_mailbox int,
@i_prefix int,
@i_extension int,
@i_return int,
@c_customer_account char(7),
@c_mailbox char(9),
@c_text varchar(255),
@c_dnis char(6),
@c_phone_number varchar(10),
@dt_cutoff datetime,
@guid uniqueidentifier

set nocount on

--set cutoff date of 3 months
set @dt_cutoff = getdate() - 90

-- create guid
set @guid = newid()

--create cursor of mailboxes that have been closed for over 6 months.
declare recy_cursor cursor for
select
c.i_customer_account,
m.i_prefix,
m.i_extension,
m.pk_mailbox
from
mailbox m (nolock)
join customer c (nolock)
on c.pk_customer = m.fk_customer
where
m.i_mailbox_status = 2
and m.dt_off < @dt_cutoff
and m.fk_product not in (65, 66, 69)
and m.fk_customer <> 134727 -- state of lousiana http://intranet/new_vomits/details.asp?incident=82556
order by
m.i_prefix,
m.i_extension

-- open the cursor and start getting records
open recy_cursor

fetch next
from
recy_cursor
into
@i_customer_account,
@i_prefix,
@i_extension,
@pk_mailbox

while (@@fetch_status = 0)
begin

-- call the delete mailbox sp to remove the mailbox
exec dbm_delete_mailbox_new @i_prefix, @i_extension, 'sysa', null, @guid

-- set variables for the auto cst
set @c_customer_account = right('000000' + convert(varchar(7),@i_customer_account),7)
set @c_mailbox = convert(char(3),@i_prefix) + '-' + right('0000' + convert(varchar(5),@i_extension),5)
set @c_text = 'mailbox ' + @c_mailbox + ' was recycled on ' + convert(char(10),getdate(),101) + '.'

-- and create
exec cis..sp_auto_cst @c_customer_account, 'recycle', 'closed', @c_text, @c_mailbox, 'recy'

fetch next from recy_cursor into @i_customer_account, @i_prefix, @i_extension, @pk_mailbox

end

-- close and deallocate the cursor
close recy_cursor
deallocate recy_cursor

return 0

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2010-06-05 : 23:28:02

does dbm_delete_mailbox_new do anything strange, like create/drop tables? what about cis..sp_auto_cst?

also I just noticed you are using nolock in the result set to iterate over. If the read fails due to data movement (page splits, etc) then it may be the reason for the failure. try removing the (nolock).

also, how do you expect it to work if you exclude new_vomits?

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources