Infinite Loop in cursor

Hi

I have an infinite loop in a trigger I and I cant reslove it.

In my system the user updates a stock table from the GUI and on the update I need to check values to see if I need to add records to a StockHistory table. For Example: If the user changes the grade of Product X from A to B then I need to add a new line in StockHistory for product X grade A that decrements the total number of products in the warehouse. Similary I need to increase the quantity of stock for Product X grade B.
I had the trigger working for single updates but now when stock is added to the database (from another db) it has status of 'New'. This isn't actually 'in stock' until the user sets the status to 'Goods In'. This process will then update the status for all records in the category. This caused my trigger to fail as the 'inserted' table now contains many records.
Now the problem I have is the trigger is in an infinite loop. It always shows the id of the first record it finds and the @.Quantity values increases as expected. I've taken all my procesing code out of the trigger and adding some debugging stuff but it still doesnt work:
CREATE TRIGGER [StockReturns_on_change] ON [dbo].[StockReturns]
FOR UPDATE
AS
DECLARE INDIVIDUAL Cursor Cursor for all the rows being updated
FOR
SELECT Id FROM inserted
OPEN INDIVIDUAL
FETCH NEXT FROM INDIVIDUAL INTO @.Id
select @.Quantity = 1
print @.@.FETCH_STATUS
print @.Id
print @.Quantity
WHILE @.@.FETCH_STATUS = 0
begin
select @.Quantity = @.Quantity + 1
print @.@.FETCH_STATUS
print @.Id
print @.Quantity
-- Get the next row from the inserted table
FETCH NEXT FROM INDIVIDUAL INTO @.Id
End -- While loop on the cursor
-- no close off the cursors
CLOSE INDIVIDUAL
DEALLOCATE INDIVIDUAL


Can you help me please?
Kind Regards

i think this is not an infinite query but a "long running" query. its going to finish

its just taking a lot of time

i suggest you get rid of the cursor replace it with a faster and better update code that process multiple records at the same time

use aggregate function(sum, count,min, max.. etc) in place of your counters (@.x=@.x+1)

cursors are very slow way of doing things

regards,

|||

Thanks for the reply.

Nop its an infinite loop. The print statements show that id is always the same and the quanityt counter is being incremented showing that its travelling round the cursor. Or are you saying its looping around but still waiting for the first transaction to finish? I went out yesterday for 20 minutes and it was still showing the 1st record.

If I knew what code to write that would work then id do it. Unfortunatley this is the only way I can think of doing it.

Each record needs to be treated seperately as i have to inpect the values of 2 variables (both in inserted and deleted) to see if they have changed for each record. I cannot do a bulk insert.

|||

here are your watch point

1. cursors are realy slow

2. maybe the triggers are in recurssion. meaning this trigger is fired by an update event of table1. in case the "update triggers" updates the same table (table1) again its going to call the same update trigger again and the cycle go an and on until 32 level deep per record.

if thats the case it will realy take a while to finish

solution:

you can write queries or correlated subqueries that joins your inserted and deleted table with the base tables to do the comparison.

such as

update basetable1 set fieldname = select count(xx) from

from inserted where inserted.id=basetable.id

in this way you do a one way trip to the server so even if it will be in recursion it is still fast

|||

this trigger is on the stock table and it doesnt insert or update anything. Check the code i posted. It merely attempts to get the next value from the "inserted" table. I'm running that exact code and it does loop forever.

Heres dome debug ive collected whilst running the sql "Update stockreturns set itemstatus = 1 where id = 26301": (so its only updating 1 record)

0 fetch status
26301 id updating
1 loop counter
0
26301
2
0
26301
3
0
26301
4
0
26301
5
0
26301
6
0
26301
7
0
26301
8
0
26301
9
etc

Regards

|||

i've modified your trigger and apply it to northind. employees

here's the code

use northwind

CREATE TRIGGER [StockReturns_on_change] ON [dbo].[employees]
FOR UPDATE
AS
declare @.id int
declare @.quantity int
DECLARE INDIVIDUAL Cursor Cursor for all the rows being updated

FOR
SELECT employeeId FROM inserted

OPEN INDIVIDUAL

FETCH NEXT FROM INDIVIDUAL INTO @.Id

select @.Quantity = 1

print @.@.FETCH_STATUS
print @.Id
print @.Quantity

WHILE @.@.FETCH_STATUS = 0
begin

select @.Quantity = @.Quantity + 1

print @.@.FETCH_STATUS
print @.Id
print @.Quantity

-- Get the next row from the inserted table
FETCH NEXT FROM INDIVIDUAL INTO @.Id

End -- While loop on the cursor

-- no close off the cursors
CLOSE INDIVIDUAL
DEALLOCATE INDIVIDUAL

go

update employees set lastname='joey' where employeeid=1

and heres the result

0
1
1
0
1
2

|||

now i've tried this

begin transaction
update employees set lastname='joey' where employeeid<6

and here's the result

0
1
1
0
1
2
0
2
3
0
3
4
0
4
5
0
5
6

(5 row(s) affected)

meaning this is not a infinite loop but a slow running query. how many records are you updating. by the way whats your requirements?

|||

Hi Joey

Thanks for your efforts in trying to get me to understand!!

My requirements, ok here goes.

The user prints a stock manifest when stock comes into the warehouse which copies all the records to do with that manifest (store and date) from another database to my stock database. When these records are copied they are copied with a statusid of 0 (new stock). The manifest can have anywhere between 1 and 100 products on it.

The warehouse will then classify each product into grades A,B,C or D. The products by default are grade C in the database. After the products for this manifest have been classified a "goods in confirmation" report is produced which updates all the products on that manifest from statusid = 0 to statusid = 1 (goods in). Meaning that only now will they appear on stock reports and can be picked for despatched.

Now, we have a table called stockHistory which holds movements for every product (piece of equipment). Only when the products are cliassified as statusid = 1 do they offically enter the warehouse and so the historic table needs to be updated to include these products. Historic data is never changed or deleted. Only inserts are allowed. The historic table also holds the total number of each product in stock so when we are adding Product X Grade C to the warehouse we get the max(id) for Product X Grade C and increment the total quantity value by 1 as we are added a product to the warehouse of that type.

But, the stock can also go to Despatched which means we decrement a value. Despatched items have status of 5.

Also the grade of the stock can change which will mean we need to decrement from the old grade and increment from the new grade.

My trigger before i tried to get it working looked like:

SELECT @.newItemState = (SELECT itemStatus FROM Inserted)
SELECT @.oldItemState = (SELECT itemStatus FROM deleted)
select @.OldGrade = (select Grade from deleted) -- fetch the product id
select @.Grade = (select Grade from inserted) -- fetch the product id

DECLARE @.State tinyint

-- If its a product going to stock then insert
if (@.oldItemState = 0 and @.newItemState = 1)
begin
select @.State = 1
end

-- If the grade has changed we need to remove from old grade and add to new grade
else if (@.newItemState = 1 and @.Grade <> @.OldGrade)
begin
select @.State = 2
end

-- If the item has been dispatched remove from new grade
else if (@.newItemState = 5)
begin
select @.State = 3
end

so then i increment the current value on state 1 and 2 and decrement the value of the old product on state 2 and 3.

So if its state 1 or 3 then you use the values contained in "inserted" otherwise if state = 2 then i need to use the record of the old product grade "deleted" to decrement the quantity and use the new product grade to increment the quantity.

Damn that was hard work explaining, I hope that makes your understanding of my problem easier!!

At first I was under the impression that the trigger would fire for each SINGLE update but when I found out it didnt I thought looping round the inserted table was the thing to do.

So why does my loop go on forever and yours stops. Both bits of code are reading from the "inserted" table and simply printing the "id". We arent updating any other tables to cause a problem. Is there a setting in SQL 2000 which I have switched off?

Regards

|||

You don't need a cursor loop to perform this logic. You can just do it via DML statements alone.

-- If its a product going to stock then insert

insert into ....

select ....

from inserted as i

join deleted as d

on d.key_col = i.key_col

where d.itemStatus = 0 and i.itemStatus = 1

-- If the grade has changed we need to remove from old grade and add to new grade
/* I am not sure if remove means update existing row or delete from table. */

Anyway, I hope you get the idea. Using cursor loop for this is probably overkill and has performance implications. It is much simpler to write series of DML statements by inspecting the rows of inserted/deleted tables as necessary. If you need more help then please post some sample schema, data and expected results for one particular case. You can then do the same for the rest.

|||

OK cheers for that but Ive got one thing I have forgotten to say.

The stockHistory table contains 2 columns that come from a different table when the product is being despatched.

When a product is despatched the advicenote and processorid both need to be from the picklist table.

The stock table holds the fk to the picklist table called picklistid. From the picklist table I need the processorid and the despatchnote which get inserted into the processorid and advicenote columns respectively of the stockHistory table.

So I don't think I can simply do a bulk insert using data straight from the inserted table. Or can I?

Thanks

|||

UNBELIEVABLE!!

Joey, I just tried my code against the nortwind.employees table and it looped continuously. I tried your code and it worked.

I then have put your code against my stock table and run the upodate and it worked.

I've compared yours against mine and they are (as far as I can see) identical.

Im going to give it a go now and see what happens!!

Cheers

|||

IVE GOT IT.

THE LINE

-- Get the next row from the inserted table

causes it to forever loop. Without this it works fine!

|||

All sorted now and the trigger works fine.

Cheers for testing out my code Joey.

INF & IND errors

How do I update or delete a record with this floating point error in X
field? And, what the heck causes it?
Thanks!
Chris
Are you seeing 1.#inf values? That generally has something
to do with Microsoft's implementation of the IEEE standard
for floating point values - there are some values where the
representation of the value gets hosed out.
Are you trying to use column X in the where clause of your
delete or update statement?
-Sue
On Fri, 7 Oct 2005 10:43:54 -0400, "Chris Marsh"
<cmarsh@.synergy-intl.com> wrote:

>How do I update or delete a record with this floating point error in X
>field? And, what the heck causes it?
>Thanks!
>Chris
>

INF & IND errors

How do I update or delete a record with this floating point error in X
field? And, what the heck causes it?
Thanks!
ChrisAre you seeing 1.#inf values? That generally has something
to do with Microsoft's implementation of the IEEE standard
for floating point values - there are some values where the
representation of the value gets hosed out.
Are you trying to use column X in the where clause of your
delete or update statement?
-Sue
On Fri, 7 Oct 2005 10:43:54 -0400, "Chris Marsh"
<cmarsh@.synergy-intl.com> wrote:

>How do I update or delete a record with this floating point error in X
>field? And, what the heck causes it?
>Thanks!
>Chris
>

inerting/updating a collection of data values into SQL Server db all at once.

I'd like to insert/update a collection of data values from VS2005 (C#) into sql server in one insert/update statement. For instance, I'd like to insert all values from a checkboxlist that are checked without having to perform an insert statement for each value. What's the best way to go about this?

thx.

Assuming that you have the list as a joined string array, you could something like this with the following function I once wrote:

CREATE FUNCTION dbo.Split

(

@.String VARCHAR(200),

@.Delimiter VARCHAR(5)

)

RETURNS @.SplittedValues TABLE

(

OccurenceId SMALLINT IDENTITY(1,1),

SplitValue VARCHAR(200)

)

AS

BEGIN

DECLARE @.SplitLength INT

WHILE LEN(@.String) > 0

BEGIN

SELECT @.SplitLength = (CASE CHARINDEX(@.Delimiter,@.String) WHEN 0 THEN

LEN(@.String) ELSE CHARINDEX(@.Delimiter,@.String) -1 END)

INSERT INTO @.SplittedValues

SELECT SUBSTRING(@.String,1,@.SplitLength)

SELECT @.String = (CASE (LEN(@.String) - @.SplitLength) WHEN 0 THEN ''

ELSE RIGHT(@.String, LEN(@.String) - @.SplitLength - 1) END)

END

RETURN

END

So this would evaluate in your case to:

Set @.ListOfIDs = '1, 2, 3, 4, 5'

INSERT INTO SomeTable
(Column)
Select SplitValue From dbo.Split(@.ListOfIDs,',')

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I thought that SQL Server 2005 allowed the insertion of VS 2005 DataTables etc. no?|||

MarilynJ wrote:

I'd like to insert/update a collection of data values from VS2005 (C#) into sql server in one insert/update statement. For instance, I'd like to insert all values from a checkboxlist that are checked without having to perform an insert statement for each value. What's the best way to go about this?

thx.

fill the dataset from data from sql server

update on the front end. vs2005 is using twoway binding

so there's not much work to be done

then

call the tableadapter update method

to commit chages to the database as a batch.

this is known as batch update

|||And remember that i you are working completly disconnected from the database that you have to specify your own Commands to update / insert / delete the data. Otherwise you could use the commandbuilder which will get you the appropiate commands if you read the schema from the database.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.desql

Indexes update when table is modified

Hello again,

Two Short questions this time,

I have a table with several indexes, currently most of them are very narrow (one column), and the question is, when I modify the table by updating a record, does all the indexes are calculated again?? Even if the modified field isn't indexed? Or the server is smart and knows what indexes to calculate if any.

Second question, can I give to a query a low priority(In dynamic SQL), for example when I don't want my query to exploit too many system resources so it won't interfere the main system ?

Inon.update only affects the modified data

second one I have no idea what a priority setting is...|||You can't normally set the scheduler priority within SQL Server, because the timeslice manager affects so many other things if it gets even a little bit confused. A given spid can elect for lower priority treatment by setting its DEADLOCK_PRIORITY (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_8ynt.asp) to LOW.

-PatP|||update only affects the modified data

Ok, just allow me to be sure, if I have a table with 10 indexes and I modify a field in some record which is not indexed, none of the indexed are recalculated right?

I'm asking because I read something that made me unsure about this process, see this link: http://www.sql-server-performance.com/q&a59.asp
Look at the part where he writes: "For every data modification you have, each index in your table needs to be updated".

Thanks,

Inon.|||OK, somebody else can correct me if I'm wrong, but SQL Server does not strictly "update" a record. It copies the record with the modifications and then deletes the original. And it has to copy the entire page of records. That entails suffling of the clustered index, and for non-clustered indexes all the references have to be updated.|||To be honest I have to get Kalen's book

BUT copying the entire page for an update?

Sounds like a lot of IO and overhead...|||There is no deletion-insertion going on. An update of "circus" to "circle" only overwrites the last 2 bytes ("us" to "le"). The whole process can be easily seen when analyzing a transaction log with Log Explorer.|||There is no deletion-insertion going on. An update of "circus" to "circle" only overwrites the last 2 bytes ("us" to "le"). The whole process can be easily seen when analyzing a transaction log with Log Explorer.In terms of changes to the data page, yes, but the log page still gets written in its entirety. As a second issue, if you change "circus" to "circuses" then things may (or may not) get complex if the page is full.

-PatP|||Of course complexity increases when circus is in town! But in respect to log page being written, - you answered your own question, - it does get written!|||I can't find squat on Microsoft's site that deals with this. Thanks, MS.

But here is some info from Kalen:

http://www.winnetmag.com/SQLServer/Article/ArticleID/8031/8031.html

In SQL Server 7.0 (and presumably 2000...blindman), updates can happen in place or as a delete followed by an insert. An in-place update is one where SQL Server changes the bytes in the row with no movement of data necessary.

The leaf level of nonclustered indexes contains a row locator for every row in the table. If the table has a clustered index, the row locator in every nonclustered index is the clustering key for that row. So ifand only ifthe clustered index key is updated, modifications are required in every nonclustered index.|||That's correct. But as Pat mention (not fully, but kinda hinted) that if the data modification of non-indexed fields affects physical location (page) of indexed fields, and no clustered index is defined, - that "may" (!!!) require an update of non-clustered index pages.|||Ok, just allow me to be sure, if I have a table with 10 indexes and I modify a field in some record which is not indexed, none of the indexed are recalculated right?

I'm asking because I read something that made me unsure about this process, see this link: http://www.sql-server-performance.com/q&a59.asp
Look at the part where he writes: "For every data modification you have, each index in your table needs to be updated".

Thanks,

Inon.

Below is the reply to my question from the author of the article you were referring to:

If you modify a non-indexed column, then other indexes are not affected.

Brad

_____________________________________________
From: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Sent: Tuesday, June 08, 2004 11:34 AM
To: webmaster@.sql-server-performance.com
Subject: SQL Tuning

In your Q&A posting (http://www.sql-server-performance.com/q&a59.asp) you're stating that "every data modification you have, each index in your table needs to be updated". Does it hold true even if you modify non-indexed fields?

Thanks in advance.|||Great, Thanks a lot for the help!

I thought I knew the answer, but after I accidentally bumped into that article I wasn't sure and had to be sure.

Inon.