Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
speno
Starting Member
18 Posts |
 Posted - 2005-08-26 : 13:53:43
|
| I've got a stored proc that is trying to update a single field for a table. Problem is it will work if the number of records that the loop is updating is 28 or less. When there's 29 records a trace indicates that the first record of the loop will not execute but just hangs. Only when there are 28 will it fully execute. The sp_lock results are below and I don't know what to make of it. Perhaps someone else can help (sorry if paste didn't format columns correctly).152 16 0 0 DB S GRANT154 16 0 0 DB S GRANT154 16 1913773875 0 TAB IX GRANT154 16 1913773875 1 KEY (f500842c21fa) X GRANT154 16 1913773875 17 KEY (fa00f58ba7d5) X WAIT154 16 1913773875 1 PAG 1:45047 IX GRANT |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-26 : 14:42:57
|
| Could you post the query?Tara |
 |
|
|
speno
Starting Member
18 Posts |
Posted - 2005-08-26 : 15:57:27
|
| The loop query is "Select * from InvoiceMaster_View order by Tracking_Number" which runs the update statement (stored proc) for each record. The update statement is "Update Ship_Master set Status = @Status where tracking_number = @tracking_number"Not sure if this matters or not but Ship_Master is set up for replication to another server on the network. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-26 : 15:58:45
|
| You've only posted partial code, so it's hard to give you a definitive answer. But the loop is most likely the problem. Get rid of the loop by using a set-based approach, and it'll almost certainly be faster.Tara |
|
|
|
speno
Starting Member
18 Posts |
Posted - 2005-08-26 : 17:51:20
|
| Thanks for the reply but why would it not execute even the first record if 29 or more are involved. What could be putting a lock on it and do the results that I posted from the sp_lock yield any clues? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-26 : 18:15:31
|
| It's hard to say what's going on without seeing the code.Tara |
|
|
|
|
|
|
Timeout expired problem