Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    2

    Post Unanswered: Query Analyzer SQL Debugger - Error 1222

    While debugging a stored procedure that creates global temp tables (##MyTempTable) I am not able to (Select) access this table from another connection while pause at a break point. I receive an error 1222 Lock request time out. If I add a “commit” statement (break point right after) at a given point within the stored procedure then I am able to view the contents of (##MyTempTable) while still in the debugger. I know it’s due to being within a transaction and the “commit” ends this transaction but….my question is –

    Is there a global SQL connection Flag,switch,option or what have you that can be set to avoid having to add these “commit” statements sporadically through out my SP while debugging?

    Thanks for your time!

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Take a look at SET IMPLICIT_TRANSACTIONS
    MCDBA

  3. #3
    Join Date
    Jul 2002
    Posts
    2

    Thumbs up

    F.Y.I
    I did try various settings involving IMPLICIT_TRANSACTION (SQL Query Analyzer, options and SQL server properties connections) but had no positive effects. But after getting your reply I decided to add the SET IMPLICIT_TRANSACTION OFF statement directly to the stored procedure I was debugging and that did the trick - @@Trancount remained 0 and I was able to asynchronously view the update ##Temp table contents from another connection.

    Thanks for replying !!!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •