Queries with cursors can't be executed back to back

shop_at_pacifictabla.com
Date: 01/17/05 

Date: 16 Jan 2005 18:25:15 -0800

Hi:

I have two queries shown below. They both use cursors created from the
same table (but with completely different data), and they both write to
the same table. Problem is, when I run them back to back in Query
Analyzer, the first one will execute and insert rows in the
"CallByCallYesterdayFinal" table, and if I then run the second one it
will say it completed successfully but it hasn't updated any rows. If I
log off and back on to Query Analyzer, I can then run the second one
and it will update. Since the cursors have different names and have
completely different recordsets, should it matter that I am writing to
the same table?

There is much more code than this and, although I don't like using
cursors, I feel they are necessary here and the performance is
perfectly acceptable for my purposes. I cut out a lot of code to
simplify things (if you're wondering what the point is of the code
below)--it just seems the problem running two cursors back to back.
This is a problem for me because I am running these queries back to
back in a DTS package, and there is the same result there, the 2nd one
does nothing.

Let me know if you require CREATE table scripts to assist me.
(TestProc3 runs first, TestProc4 runs 2nd):

____________________________________________________________

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[TestProc3] AS

DECLARE @SiteName varchar (40)
DECLARE @CallID int
DECLARE @Timestamp datetime
DECLARE @CEvtName varchar (80)
DECLARE @LoginID varchar (16)
DECLARE @AssData varchar (40)
DECLARE @Dest varchar (40)
DECLARE @Source varchar (40)

DECLARE @Reporting_ID int
DECLARE @SiteCallID varchar (16)
DECLARE @ArriveTime datetime
DECLARE @AgentLogin varchar (16)
DECLARE @App varchar(40)
DECLARE @SK varchar(40)
DECLARE @TranInterCallType varchar(40)
DECLARE @Trans_Intercall_ID int
DECLARE @TranTime datetime
DECLARE @Intercall_ID varchar(16)
DECLARE @TranType varchar (40)
DECLARE @AgentGivenName varchar (40)
DECLARE @CallEventNum int
DECLARE @StopInsert char (5)

DECLARE @LastRecordCallID int
DECLARE @LastRecordTimestamp datetime

SELECT @Reporting_ID = Max(Reporting_ID) FROM CallByCallYesterdayFinal

IF @Reporting_ID IS NULL
BEGIN
SELECT @Reporting_ID = 1
END
ELSE
BEGIN
SELECT @Reporting_ID = @Reporting_ID + 2
END

SELECT @LastRecordCallID = -1
SELECT @CallEventNum = 0

DECLARE Parse_Cleaned_CBC_Data CURSOR

GLOBAL
FOR

SELECT * FROM CallByCallYesterdayCleaned
WHERE LEFT(CallEventName,2) <> 'DN'
ORDER BY Site, CallID, [Timestamp]

OPEN Parse_Cleaned_CBC_Data

/* Loop through the cursor*/

WHILE @@FETCH_STATUS = 0

BEGIN
FETCH NEXT FROM Parse_Cleaned_CBC_Data INTO
@SiteName, @CallID, @Timestamp, @CEvtName, @LoginID, @AssData, @Dest,
@Source

IF @CallID <> @LastRecordCallID OR DATEDIFF(s, @LastRecordTimestamp,
@Timestamp) > 1800 or @TranTime <> NULL
BEGIN
INSERT INTO CallByCallYesterdayFinal VALUES
(@Reporting_ID,@SiteCallID,@ArriveTime,@AgentLogin,@App,@SK,@TranInterCallType,@Trans_Intercall_ID,
@TranTime,@Intercall_ID,'',@TranType,@AgentGivenName,'')

END

SELECT @SiteCallID = 'FirstProc' + CONVERT(char(20),@CallID)
SELECT @LastRecordCallID = @CallID
SELECT @LastRecordTimestamp = @Timestamp
SELECT @CallEventNum = @CallEventNum + 1

END

CLOSE Parse_Cleaned_CBC_Data
DEALLOCATE Parse_Cleaned_CBC_Data

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

__________________________________________________________________

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[TestProc4] AS

DECLARE @SiteName varchar (40)
DECLARE @CallID int
DECLARE @Timestamp datetime
DECLARE @CEvtName varchar (80)
DECLARE @LoginID varchar (16)
DECLARE @AssData varchar (40)
DECLARE @Dest varchar (40)
DECLARE @Source varchar (40)

DECLARE @Reporting_ID int
DECLARE @SiteCallID varchar (16)
DECLARE @ArriveTime datetime
DECLARE @AgentLogin varchar (16)
DECLARE @App varchar(40)
DECLARE @SK varchar(40)
DECLARE @TranInterCallType varchar(40)
DECLARE @Trans_Intercall_ID int
DECLARE @TranTime datetime
DECLARE @Intercall_ID varchar(16)
DECLARE @TranType varchar (40)
DECLARE @AgentGivenName varchar (40)
DECLARE @StopInsert char (5)
DECLARE @IsScriptedCall char (5)
DECLARE @LastRecordCallID int
DECLARE @LastRecordTimestamp datetime
DECLARE @CallEventNum int

SELECT @Reporting_ID = Max(Reporting_ID) FROM CallByCallYesterdayFinal

IF @Reporting_ID IS NULL
BEGIN
SELECT @Reporting_ID = 1
END
ELSE
BEGIN
SELECT @Reporting_ID = @Reporting_ID + 2
END

SELECT @LastRecordCallID = -1
SELECT @CallEventNum = 0

DECLARE Parse_Cleaned_CBC_Data_DN CURSOR

GLOBAL
FOR

SELECT * FROM CallByCallYesterdayCleaned
WHERE LEFT(CallEventName,2) = 'DN' OR CallEventName = 'Call
Transferred' OR
CallEventName = 'Handed Over To Master Application' OR
CallEventName = 'Handed Over To Network Application'
ORDER BY Site, CallID, [Timestamp]

OPEN Parse_Cleaned_CBC_Data_DN

/* Loop through the cursor*/

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM Parse_Cleaned_CBC_Data_DN INTO
@SiteName, @CallID, @Timestamp, @CEvtName, @LoginID, @AssData, @Dest,
@Source

IF @CallID <> @LastRecordCallID OR DATEDIFF(s, @LastRecordTimestamp,
@Timestamp) > 1800 or @TranTime <> NULL
BEGIN
INSERT INTO CallByCallYesterdayFinal VALUES
(@Reporting_ID,@SiteCallID,@ArriveTime,@AgentLogin,@App,@SK,@TranInterCallType,@Trans_Intercall_ID,
@TranTime,@Intercall_ID,'',@TranType,@AgentGivenName,'')
END

SELECT @SiteCallID = '2nd Proc' + CONVERT(char(20),@CallID)
SELECT @LastRecordCallID = @CallID
SELECT @LastRecordTimestamp = @Timestamp
SELECT @CallEventNum = @CallEventNum + 1

END

CLOSE Parse_Cleaned_CBC_Data_DN
DEALLOCATE Parse_Cleaned_CBC_Data_DN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Thanks!
Kayda

Relevant Pages