Cursors
-
Hi,
I was wondering if someone has an explanation for this.
We got a following error on the process that's been working fine for months:
SQL Err Number: 16943
SQL Err Message: Could not complete cursor operation because the table schema changed after the cursor was declared.
The cursor that it's referring to is operating on the local temp table (#MyTable). The purpose of the cursor is to identify records that have identical phone number, keep the 1st records and insert the other ones into the table variable. those records are marked later as "scrubbed".
We've tried to recreate the problem and so far had no luck.
PS... please don't answer "Don't use cursors". I know that. But right now rewriting the process is not an option.
-
Ok, do not use cursors
.Has someone added and index, column whatever to that table while the cursor was running?
-
nope. the only difference was that there were a little more concurrent processes running then usual. And that cursor uses a local temp table. No indexes on it.
-
Hmm. Can you send the code of the procs (complete)
-
Just did.
I appreciate your help. Any idea in general would be appreciated. Unfortunately, I didn't find much on this error on the web.
-
I meant in here
. I'm not the only one who needs this info to help you. -
Here is the code. thanks
ALTER PROCEDURE ecmspEliminateDuplPhones(
@CampaignID BIGINT,
@RuleID INTEGER,
@ErrorMessage VARCHAR(4000) OUTPUT)
AS
DECLARE @DuplPhoneNumber CHAR(10)
DECLARE @DuplPhones TABLE (UserID BIGINT)
DECLARE @ErrorNumber INTEGER
DECLARE @PhoneNumber CHAR(10)
DECLARE @ProcName VARCHAR(50)
DECLARE @ScrubReasonID INTEGER
DECLARE @StatusScrubbed INTEGER
DECLARE @TimeStamp DATETIME
DECLARE @user-id BIGINT
SET NOCOUNT ON
/*Scrub Users that have the same phone number as a "primary" User
The ecmspGetDuplPhoneSets stored procedure returns a cursor that
contains Users grouped by PhoneNumber, with the "primary" User
first and all the others immediately following.*/
CREATE TABLE #DuplPhoneSets (
RecordID INTEGER IDENTITY,
CampaignID BIGINT,
RuleID INTEGER,
UserID BIGINT)
SET @ProcName = 'GetDuplPhoneSets'
EXEC @ErrorNumber = dbo.ecmspGetDuplPhoneSets @CampaignID, @RuleID,
@ErrorMessage OUTPUT
IF (@ErrorNumber = 0)
BEGIN
SET @ScrubReasonID = dbo.ecmfnScrubReasonID('DUP_PHONE_NUMBER')
SET @StatusScrubbed = dbo.ecmfnStatusID('USER', 'SCRUBBED')
SET @TimeStamp = GETDATE()
DECLARE DuplPhoneSets CURSOR FOR
SELECT s.UserID, u.PhoneNumber
FROM #DuplPhoneSets s INNER JOIN
Users u ON s.UserID = u.UserID
ORDER BY s.RecordID ASC
OPEN DuplPhoneSets
SET @ErrorNumber = @@ERROR
IF (@ErrorNumber <> 0)
SET @ErrorMessage = 'Cannot open Cursor on #DuplPhoneSets Temporary Table'
/*Now, iterate through all the Users and insert the scrubbed Users
into the @DuplPhones TABLE variable*/
FETCH NEXT FROM DuplPhoneSets INTO @user-id, @PhoneNumber
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@PhoneNumber = IsNull(@DuplPhoneNumber, ''))
INSERT INTO @DuplPhones
(UserID)
VALUES
(@UserID)
ELSE
SET @DuplPhoneNumber = @PhoneNumber
FETCH NEXT FROM DuplPhoneSets INTO @user-id, @PhoneNumber
END
CLOSE DuplPhoneSets
DEALLOCATE DuplPhoneSets
UPDATE Users WITH (ROWLOCK)
SET StatusID = @StatusScrubbed,
ScrubReasonID = @ScrubReasonID,
Modified = @TimeStamp
FROM Users u WITH (INDEX (ix_Users_CampaignID_ScrubReasonID))
INNER JOIN
@DuplPhones s ON u.UserID = s.UserID
WHERE (u.CampaignID = @CampaignID) AND (u.ScrubReasonID IS NULL)
SET @ErrorNumber = @@ERROR
END
IF (@ErrorNumber <> 0)
BEGIN
IF (@ProcName IS NOT NULL)
EXEC ecmspInsertLoggedMessages @CampaignID,
@RuleID = @RuleID,
@ProcName = @ProcName,
@ErrorNumber = @ErrorNumber,
@MessageText = @ErrorMessage
SET @ErrorMessage = 'Cannot eliminate Duplicate Phones'
EXEC ecmspInsertLoggedMessages @CampaignID,
@RuleID = @RuleID,
@ProcName = 'EliminateDuplPhones',
@ErrorNumber = @ErrorNumber,
@MessageText = @ErrorMessage
EXEC ecmspUpdateRuleStatus @CampaignID, @RuleID, 'FAILURE'
END
ELSE
EXEC ecmspUpdateRuleStatus @CampaignID, @RuleID, 'SUCCESS'
SET NOCOUNT OFF
RETURN @ErrorNumber
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
As the users table been modified in any way recently (that means anyone running any script, or even pressing save in EM even when it seems no changes were made).
Are you sure not???
That's the only way (I know) to cause this error.
-
ROWLOCK is being used. Might there be a conflict if another is altering the record that the cursor is currently trying to update?
Otherwise, I agree with Remi. I have, (early when I was learning about Cursors) altered a #TempTable during the Cursor running and that is the only time I have seen that error as well...
I wasn't born stupid - I had to study.
-
Hmm. There is one process that drops the triggers and then recreates them.
Could that be it?
-
That's it... there's a refference to the trigger in the table's row of sysobjects, hence modifying the table (however slightly). Try running both at the same time to see if you get the same error.
I'd also try to simple user Alter trigger instead of drop/create.
-
Remi...you are awesome!!!
I got that same error message using pubs when I tried to disable triggers while the cursor was open. Now I just have to recreate that problem in our test environment.
SSC RULES!
-
HTH.
This is where it pays to know the systables by heart (almost)
. -
Please tell us if you can avoid the problem using alter instead of drop/create.
-
it's actually using ALTER TABLE <MyTable> DISABLE TRIGGER ALL.
Dropping the trigger caused the same error as ALTER.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply