Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Not looking for a COMMITment...

    OK, so I'm working on a metadata database that issues a crapload of dynamic sql. I'm getting this error message when the procedure completes:
    "Msg 266, Level 16, State 2, Line 0
    Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 2."

    I've printed out the dynamic sql and executed it independently, I the error appears to be coming from this statement:
    Code:
    update	IndexColumns
    set		DateFound = getdate(),
    		DateChecked = getdate(),
    		DateLost = null
    from	dbadb2005..IndexColumns IndexColumns
    		inner join dbadb2005..Indexes Indexes on IndexColumns.IndexID = Indexes.IndexID
    		inner join dbadb2005..Objects Objects on Indexes.ObjectID = Objects.ObjectID
    		inner join dbadb2005..ObjectTypes ObjectTypes on Objects.ObjectTypeID = ObjectTypes.ObjectTypeID
    		inner join dbadb2005..Schemas Schemas on Objects.SchemaID = Schemas.SchemaID
    		inner join #IndexColumnList IndexColumnList
    			on Schemas.SchemaName = IndexColumnList.SchemaName
    			and Objects.ObjectName = IndexColumnList.ObjectName
    			and ObjectTypes.ObjectType = IndexColumnList.ObjectType
    			and Indexes.IndexName = IndexColumnList.IndexName
    			and IndexColumns.IndexColumnName = IndexColumnList.IndexColumnName
    where	Schemas.DatabaseID = 'B9012E36-F2B7-DD11-9295-00188B39600E'
    		and IndexColumns.DateLost is not null
    For some reason I cannot figure out, I have to issue a commit after running this. Note that at this point in the process I am no longer accessing any system tables. All the objects referenced are user tables, though they are in two different databases.
    Anybody got a clue? Da blindman is stumped.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Does the system use any non-standard transaction isolation levels?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Nope. At least, I did not change any from the defaults.
    Plus, I have similar update statements in the same script that work fine.
    Something for me to check into, though.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by blindman
    Da blindman is stumped.
    bookmarked
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Do you get the same error when you run the script outside of its usual dynamically generated home?

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    bad trigger?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by MCrowley
    Do you get the same error when you run the script outside of its usual dynamically generated home?
    Yes. And this message is too short.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Thrasymachus
    bad trigger?
    Ooh! That's a good one. Should have thought of it. I'll check it out.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Thrasymachus
    bad trigger?
    Bingo.
    Somehow, probably a copy-paste error, a BEGIN TRANSACTION statement was included at the bottom of the trigger.
    Nice catch, Sean. For correctly solving this problem I have reserved two seats for you, and a guest, in the front row for today's Presidential Inauguration Ceremony. Better hurry if you want to be on time, and when you get there just wink and tell the Secret Service "Blindman sent me."
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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