Hi:

When working in Query Analyzer, I was working executing different code
in different batches. I noticed that if I executed the same batch twice
in a row, the second time it would complete immediately (and say it was
successful), but it wouldn't actually do anything. The same would
happen if I tried to execute two different batches that both use
cursors. If I disconnected from Query Analyzer and reconnected again, I
could then execute the batch. The same thing happens if I execute two
batches using cursors next to each other in a DTS package. This first
batch runs and actually does something, the next one runs and does
nothing.

Ignoring the fact that maybe I could do this better another way than
using cursors maybe (when they run performance is fine), what is the
reason? Here is all of the code --I haven't included any table
definitions or anything because I suspect that the code in the middle
isn't really important, and I'm just forgetting something in this batch
to do with the cursor. This only seems to happen when the batch has a
cursor.:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

DECLARE @CallID int
DECLARE @Timestamp datetime
DECLARE @Intercall_ID int
DECLARE @SiteName varchar (40)
DECLARE @SitePrefix varchar (2)
DECLARE @SitePrefixForUpdate varchar (2)

DECLARE Change_Network_Out_Calls CURSOR

GLOBAL
FOR

SELECT Site, CallID, [Timestamp], SUBSTRING(AssociatedData, 15, 8)
FROM CallByCallYesterdayCleaned
WHERE
CallEventName = "Call Transferred" AND
RIGHT(Destination,11) = "NETWORK OUT"
ORDER BY
Site, CallID

OPEN Change_Network_Out_Calls

/* Loop through the cursor*/

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM Change_Network_Out_Calls INTO
@SiteName, @CallID, @Timestamp, @Intercall_ID

/* Whatever the site name is, set it to the opposite prefix*/

IF @SiteName = "SympTeeC"
BEGIN
SELECT @SitePrefixForUpdate = "PZ"
SELECT @SitePrefix = "TC"
END
ELSE IF @SiteName = "SympPeeZ"
BEGIN
SELECT @SitePrefixForUpdate = "TC"
SELECT @SitePrefix = "PZ"
END




UPDATE CallByCallYesterdayFinal SET IntercallID = @SitePrefixForUpdate
+ right(CallID,8)
WHERE CallID = @SitePrefix + STR(@Intercall_ID,8) AND
ABS(DATEDIFF(s,@Timestamp,CallByCallYesterdayFinal .TimeArrived)) < 900


END

/* Clean up our cursor*/

CLOSE Change_Network_Out_Calls
DEALLOCATE Change_Network_Out_Calls
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


Thanks!
Blair

successful), but it wouldn't actually do anything... <<

What did you expect it to do the second time? Once teh update is made,
there is nothign else to do with the data, is there? So the second
execution looks for qualified rows; there are none; it goes home
without change the base table, the indexes or anything else. It is
effectively an empty SELECT.


UPDATE Call_By_Call_Yesterday_Final
SET inter_call_id
= CASE WHEN @site_name = 'SYMPTEEC'
THEN 'PZ'
WHEN @site_name = 'SYMPPEEZ'
THEN 'TC'
ELSE inter_call_id END
+ RIGHT(call_id, 8)
WHERE call_id
= CASE WHEN @site_name = 'SYMPPEEZ'
THEN 'PZ'
WHEN @site_name = 'SYMPTEEC'
THEN 'TC'
ELSE NULL END
+ STR(@intercall_id, 8)
AND ABS(DATEDIFF(S, @timestamp,
Call_Bycall_Yesterday_Final.time_arrived))
< 900;

That's not what is happening here:
1. If I deleted the records in the target table it still does not
execute
2. If I run two different batches in a row that do the different things
the second one does not work.
3. If I leave the target tables the same, disconnect from Query
Analyzer and reconnect, it will then work.

I actually figured this out without a cursor as well (different way),
but I have to build sometthing else that uses a cursor (has to, really)
that will run after a similar batch.

Any idea what is going on?

Kayda