[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


SQL sub-procedure repeats itself intermittently.

Posted on 2008-06-23
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

656 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