November 18, 2013 at 1:04 pm
Strange behaviour happens with update cursors.
First, make sure triggers are not firing in those tables, triggers are the best away to create bugs and strange behaviours in legacy systems.
Second, caution with null! If there are some rows with null values at AccountID you ill get a bad day at work.
Third, to catch infinite loops try to select to a temp table and fetch from that temp table, it can ill give you some insight about the data the cursor actually fetched and updating it ill no affect that fetched rows.
Finally, after you fix the bug (understand why this happens) try to rewrite that query to get rid of that cursor.
November 18, 2013 at 1:57 pm
jcb - thanks, I checked, in fact there are no triggers anywhere in the database. Actually in the production database the AccountID column is an Identity column, sorry, I did not specify this in my sample code.
Lutz, I'll try to set up a test environment. So far I did not succeed. The weird behaviour did not occur when I set up any test environment. Only thing I am sure is that the sproc is called only once, it's not a repeated invocation, it does the turn-around all by itself.
Anyone knows if VB-6 code may be the culprit if it retrieves recordsets and then loops through these via Recordset.MoveNext and so on? That these somehow get counted by SQL Server in the same @@fetch_status variable and thus trick SQL Server into believing fetch_status is always zero?
(Still does not explain why it then turns around to the first row and starts counting anew though)
The apps running here are using VB-6 recordsets extensively, it looks even like they are hardly doing anything else ... As it is a distributed application with various parts running on different stations which must be online almost constantly, I have a bit of a hard time disconnecting everybody. But I'll try this next.
November 18, 2013 at 5:23 pm
Peter Schulz-485500 (11/18/2013)
I have a bit of a hard time disconnecting everybody. But I'll try this next.
Disconnecting everyone and everything is the easy part. Just make sure that you don't lose the connection after you run the following or some nasty ol' web service will snatch up the only connection available.
USE [master]
GO
ALTER DATABASE [YourDatabaseNameHere] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
If you're sure that high prived users/apps/servers aren't going to barge in, then you could move to a safer multi-connection mode that still keeps most people out and lets you back in if you lose the connection.
USE [master]
GO
ALTER DATABASE [YourDatabaseNameHere] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
When you're done, let everyone else back in.
USE [master]
GO
ALTER DATABASE [YourDatabaseNameHere] SET MULTI_USER;
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2013 at 9:21 am
VB6 argh... it smells as a old poor designed legacy app.
Give it a look.
http://technet.microsoft.com/pt-br/library/ms187308.aspx
"Because @@FETCH_STATUS is global to all cursors on a connection, use @@FETCH_STATUS carefully."
Other possible problems are:
someone changed the isolation level and that fetched data is dirty, the application was poor designed and cannot handle some unexpected weird data like duplicated rows.
November 19, 2013 at 7:11 pm
jcb (11/19/2013)
VB6 argh... it smells as a old poor designed legacy app.
Hey, Hey, [font="Arial Black"]HEY[/font]! I'm old, I used to be a great VB6 programmer, I AM a legacy app, and I'm RIGHT HERE! 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
November 20, 2013 at 4:11 am
Jeff Moden (11/19/2013)
jcb (11/19/2013)
VB6 argh... it smells as a old poor designed legacy app.Hey, Hey, [font="Arial Black"]HEY[/font]! I'm old, I used to be a great VB6 programmer, I AM a legacy app, and I'm RIGHT HERE! 😀
As I used to be a programer in VB6, Deplhi 3,4,5,6,6.5,7, Turbo Pascal 7, Borland C 5, Basic and to be a great DOS tunning whore.
But that don't make me miss to use a mini PC with 8k memory or a 286 with no HD and two 8" floppy. 😀
No problem to be a old legacy the problem is to be poor designed :hehe:
November 20, 2013 at 2:55 pm
jcb (11/20/2013)
Jeff Moden (11/19/2013)
jcb (11/19/2013)
VB6 argh... it smells as a old poor designed legacy app.Hey, Hey, [font="Arial Black"]HEY[/font]! I'm old, I used to be a great VB6 programmer, I AM a legacy app, and I'm RIGHT HERE! 😀
As I used to be a programer in VB6, Deplhi 3,4,5,6,6.5,7, Turbo Pascal 7, Borland C 5, Basic and to be a great DOS tunning whore.
But that don't make me miss to use a mini PC with 8k memory or a 286 with no HD and two 8" floppy. 😀
No problem to be a old legacy the problem is to be poor designed :hehe:
Heh... I gave all that kind of stuff up in 2002 (well, except some of the DOS stuff) and haven't looked back. I did like my 8K of memory but that was only because I had twice as much as all my friends. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2013 at 6:55 am
Jeff Moden (11/19/2013)
I used to be a great VB6 programmer
WHAT :exclamationmark: Are you a bad VB6 programmer now
And after everything you said about CLR sheesh :rolleyes:
p.s. Only joking Jeff, my old friend 😀
Far away is close at hand in the images of elsewhere.
Anon.
November 22, 2013 at 6:43 am
Everyone's already discussed using set-based queries - so I won't say any more on that - but in the spirit of understanding why this is happening :-
Peter, check the indexes on the Addresses table on your production server. You might be bumping into the Halloween Problem.
I was able to repro your problem like this:
set nocount on;
create table Addresses (AccountID int identity, Country nvarchar(20) not null, lpn int null);
insert into Addresses(Country) values ('HR'), ('HR'), ('ZA');
create clustered index FooBar on Addresses(lpn); -- this breaks it
create table lpn(ID int identity, DeliveryID int);
go
declare @newLPN int;
declare @currentID int;
set @newLPN = 0;
set @currentID = 0;
DECLARE cursorlpn CURSOR FOR
select AccountID from Addresses
where country = 'HR';
open cursorlpn;
fetch next from cursorlpn into @currentID
while (@@fetch_status = 0)
begin
raiserror('AccountID = %i', 0, 0, @currentID) with nowait;
insert into lpn (deliveryID) values (0);
set @newLPN = scope_identity();
update Addresses set lpn = @newLPN where AccountID = @currentID;
fetch next from cursorlpn into @currentID
end
close cursorlpn
deallocate cursorlpn
Which outputs
AccountID = 1
AccountID = 2
AccountID = 1
AccountID = 2
AccountID = 1
AccountID = 2
AccountID = 1
...
Adding the FooBar clustered index means that each address row that is updated moves to the end of the table, so after updating row 2, the cursor finds row 1 again, and after updating it a second time it then finds row 2 again, and so on.
November 22, 2013 at 7:28 am
Gavin Lock (11/22/2013)
You might be bumping into the Halloween Problem.I was able to repro your problem like this:
Changing the cursor to STATIC will stop your query looping.
Far away is close at hand in the images of elsewhere.
Anon.
November 22, 2013 at 1:41 pm
David Burrows (11/21/2013)
Jeff Moden (11/19/2013)
I used to be a great VB6 programmerWHAT :exclamationmark: Are you a bad VB6 programmer now
And after everything you said about CLR sheesh :rolleyes:
p.s. Only joking Jeff, my old friend 😀
BWAAA-HAAA!!!! I might be terrible at it now. After my old manager directed me to style a "warning" field with a Pale Yellow background/White lettering and a "danger" field with a Fusia background and a particular Pink lettering, I had all knowledge of front end and business layer programming surgically and chemically (mostly alchohol) removed. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2013 at 3:27 pm
Jeff Moden (11/22/2013)
David Burrows (11/21/2013)
Jeff Moden (11/19/2013)
I used to be a great VB6 programmerWHAT :exclamationmark: Are you a bad VB6 programmer now
And after everything you said about CLR sheesh :rolleyes:
p.s. Only joking Jeff, my old friend 😀
BWAAA-HAAA!!!! I might be terrible at it now. After my old manager directed me to style a "warning" field with a Pale Yellow background/White lettering and a "danger" field with a Fusia background and a particular Pink lettering, I had all knowledge of front end and business layer programming surgically and chemically (mostly alchohol) removed. 😛
Lordy - what a color combo! ach! I've had managers like that too 😛
But still - this one took me back. I had occasion to see the last of one of my legacy VB6 apps finally graduate up to .NET earlier this year, so I think I am finally out of that mess 🙂 That's another 8K of memory I can free up for relevant info.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 22, 2013 at 5:41 pm
Matt Miller (#4) (11/22/2013)
Jeff Moden (11/22/2013)
David Burrows (11/21/2013)
Jeff Moden (11/19/2013)
I used to be a great VB6 programmerWHAT :exclamationmark: Are you a bad VB6 programmer now
And after everything you said about CLR sheesh :rolleyes:
p.s. Only joking Jeff, my old friend 😀
BWAAA-HAAA!!!! I might be terrible at it now. After my old manager directed me to style a "warning" field with a Pale Yellow background/White lettering and a "danger" field with a Fusia background and a particular Pink lettering, I had all knowledge of front end and business layer programming surgically and chemically (mostly alchohol) removed. 😛
Lordy - what a color combo! ach! I've had managers like that too 😛
But still - this one took me back. I had occasion to see the last of one of my legacy VB6 apps finally graduate up to .NET earlier this year, so I think I am finally out of that mess 🙂 That's another 8K of memory I can free up for relevant info.
I still have VB5 and VB6 apps in production. I've moved on from that company, so there's virtually no chance of me having to rewrite them. Someone else can have at it, but there's no need as long as the VB runtimes still function on whatever server they're running on. 😀
January 10, 2014 at 12:08 pm
I am battling a similar "self-restarting cursor" . The defect
(a) occurs 100% on one server
(b) occurs 0% on another server
Pseudocode
Declare cursor as..
Open cursor
Fetch next
while @@FetchStatus
do stuff
FetchNext
--** right here, if it hits EndOfData, it just happily fetches First Row of Cursor and rolls on.
end
close cursor
Seems like there is a new bug in SQL Server, and it depends on some unknown aspect of how the server is configured.
January 10, 2014 at 12:40 pm
mwitthoft (1/10/2014)
I am battling a similar "self-restarting cursor" . The defect(a) occurs 100% on one server
(b) occurs 0% on another server
Pseudocode
Declare cursor as..
Open cursor
Fetch next
while @@FetchStatus
do stuff
FetchNext
--** right here, if it hits EndOfData, it just happily fetches First Row of Cursor and rolls on.
end
close cursor
Seems like there is a new bug in SQL Server, and it depends on some unknown aspect of how the server is configured.
First, there isn't enough to even try to help here. Second, I would start looking at finding a way to eliminate the cursor and replace it with a set-based process.
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply