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,@AgentGivenNa me,'')


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,@AgentGivenNa me,'')
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

shop (AT) pacifictabla (DOT) com wrote:
Just curious why you are using a global cursor and not a static, forward
only cursor. You appear to be just using the cursor to move through the
results and perform actions elsewhere.

I think you should be fetching before the while loop and at the end
(both places). You're probably picking up an incorrect status on the
second execute (it's global to the connection).

See this example from BOL:

DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName FROM Northwind.dbo.Employees
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor


--
David Gugick
Imceda Software
www.imceda.com