Solved

SQL sub-procedure repeats itself intermittently.

Posted on 2008-06-23
5
215 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
Comment Utility
>> 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
Comment Utility
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
Comment Utility
Still not a real Definitve answer
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
Comment Utility
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
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

763 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

10 Experts available now in Live!

Get 1:1 Help Now