Batch with cursor successful, but not really - 12-20-2004 , 09:04 AM
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
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
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
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
SELECT Site, CallID, [Timestamp], SUBSTRING(AssociatedData, 15, 8)
CallEventName = "Call Transferred" AND
RIGHT(Destination,11) = "NETWORK OUT"
/* Loop through the cursor*/
WHILE @@FETCH_STATUS = 0
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"
SELECT @SitePrefixForUpdate = "PZ"
SELECT @SitePrefix = "TC"
ELSE IF @SiteName = "SympPeeZ"
SELECT @SitePrefixForUpdate = "TC"
SELECT @SitePrefix = "PZ"
UPDATE CallByCallYesterdayFinal SET IntercallID = @SitePrefixForUpdate
WHERE CallID = @SitePrefix + STR(@Intercall_ID,8) AND
ABS(DATEDIFF(s,@Timestamp,CallByCallYesterdayFinal .TimeArrived)) < 900
/* Clean up our cursor*/
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
Re: Batch with cursor successful, but not really - 12-21-2004 , 12:36 PM
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.
= CASE WHEN @site_name = 'SYMPTEEC'
WHEN @site_name = 'SYMPPEEZ'
ELSE inter_call_id END
+ RIGHT(call_id, 8)
= CASE WHEN @site_name = 'SYMPPEEZ'
WHEN @site_name = 'SYMPTEEC'
ELSE NULL END
+ STR(@intercall_id, 8)
AND ABS(DATEDIFF(S, @timestamp,
Re: Batch with cursor successful, but not really - 12-22-2004 , 10:00 AM
That's not what is happening here:
1. If I deleted the records in the target table it still does not
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?