Solved

SQL sub-procedure repeats itself intermittently.

Posted on 2008-06-23
5
218 Views
Last Modified: 2011-10-19
I have created an application in SSMS 2005 running on a SQL Server 2000 Engine comprised of a cursor and numerous statements inside that cursor. On a very rare occasion, the calling procedure appears to double-up on some of the sub-called SP's. It has the appearance of starting/stopping and forgetting where it's been. The data is doubled up, but it's still hard to tell how much of the procedure it ran before it hiccupped.The main procedure does, on occasion, say it's deadlocked on resources with another process. Is this a problem with locked resources? Is there something I can do to assign a higher priority/lock tables to the Calling Procedure than to the other processes which use the same tables.

Would using "Return" in my Calling procedure do any good?

What steps would you take to track down a wandering cursor's exploits?


0
Comment
Question by:ttinsley
  • 3
  • 2
5 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 21861537
>> What steps would you take to track down a wandering cursor's exploits?

Create a logging table and write entries to it in every step of your stored procedure, along with an automatic datestamp.

Then you can query the table afterwards.


>> the calling procedure appears to double-up on some of the sub-called SP's

I think its more likely that the cursor has two rows where you are expecting only one.

What does the outside cursor look like? Can you post the SQL?


>> Is there something I can do to assign a higher priority/lock tables to the Calling Procedure

You can add hints such as WITH (HOLDLOCK) to the code inside your stored procedure or you can wrap the whole SP up in an OPEN TRANS then COMMIT TRANS.

This has the effect of grabbing the table and not letting go until its finished.

However, usually when you do things like that it that results in all the other processes suffering, and you end up with a great big chain of locked processes in the database, which means everything grinds to a halt.


In actual fact usually things can be achieved without cursors, and can then be achieved much more quickly, which in turn reduces blocking times.

0
 

Author Comment

by:ttinsley
ID: 21867458
You're absolutely correct about the need for cursors, except this one was over my head when I started and there was a need to decrement the warehouse quantities on a line by line basis. The procedure basically processes parts orders and transfers stock when the stock levels are high enough. If the stocking level of the item is below what is being ordered the item is placed onto a purchase order. In this manner it uses up all of our stock first, then it automatically switches to placing items on purchase orders. So the same run of the procedure may see both processes depending on quantities ordered and quantities in stock. Normally it works great, it's only very occasionally that we see a doublling up effect.

Here is the code
Parts-Order-Processor.txt
0
 

Author Comment

by:ttinsley
ID: 22060687
Still not a real Definitve answer
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
ID: 22097868
I haven't gone through your SP in detail. I'm afraid the best I can offer is for you to write everything to a logging table, and check it in detail when a double up occurs, or rewrite your SP using bulk updates or inserts instead of cursors. That way everything gets updated in one go.
You could also try using the INSENSITIVE cursor keyword to stop your cursor reflecting any change which occurs to it. That may make it worse or better depending on how the logic works.
I can't help you too much more sorry because I know will never get time to go thourgh your SP!!
0
 

Author Closing Comment

by:ttinsley
ID: 31469977
I don't blame you, it's a lunker. I believe you are correct about the logging, however, as it turns out, I was upgrading the error I suspected was causing the issues not too long ago, and the problem has seemed to stop occurring. If it does begin reoccurring I will follow the steps you have outlined.

Thanks nmcdermaid!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now