SQL sub-procedure repeats itself intermittently.

Posted on 2008-06-23
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?

Question by:ttinsley
  • 3
  • 2
LVL 30

Expert Comment

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.


Author Comment

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

Author Comment

ID: 22060687
Still not a real Definitve answer
LVL 30

Accepted Solution

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!!

Author Closing Comment

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!

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

860 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