Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Our application interfaces with a partner's application by calling one of their stored procedures. They insist on sprinkling NOLOCK hints throughout their code and this is a long stored procedure. We are intermittently getting bad data and suspect a race condition.

Is there any way, short of manually changing their stored procedure, that we can override their NOLOCK hints when executing it?

Note -- our code making the stored procedure call is a .NET application, in case that gives us any other options.

share|improve this question

migrated from stackoverflow.com Aug 23 '12 at 12:29

This question came from our site for professional and enthusiast programmers.

3  
No, you cannot. Short of pre-locking all tables involved to prevent any concurrent writes... –  Remus Rusanu Aug 21 '12 at 21:26
    
Was afraid of that. :( Can you submit as answer so I can accept it? –  Calvin Fisher Aug 21 '12 at 21:39
1  
Mr 106k probably doesn't care too much about his answers being accepted... =) Let Mr 50k have it. –  sam yi Aug 21 '12 at 21:46
    
Well, he got that 106k by offering his expertise and having the rest of us give him props for it. :) +1 and thanks to Remus as well. –  Calvin Fisher Aug 22 '12 at 14:58
    
You have the 2008 R2 tag on your question, so I should point out, there does not seem to be any performance advantage to using the hint NOLOCK. stackoverflow.com/questions/15770402/… –  James Jenkins Apr 29 '13 at 13:37

1 Answer 1

up vote 4 down vote accepted

No, query-level hints and options override session and server-level settings. Basically, a hint applied at the query level is the lowest common denominator, and wins (except in the case of MAXDOP and Resource Governor, but that's slightly different than what we're talking about here anyway).

This used to be documented for SQL Server 2000, but this verbiage has been removed from more modern versions of the documentation.

share|improve this answer
    
Good in-depth answer! –  Calvin Fisher Aug 22 '12 at 15:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.