SQL Azure Query Editor Transaction Blog

  • ESAT ERKEC

    SSCertifiable

    Points: 5558

    I could not find any logical explanation about the following issue.
    I connected SQL Azure Query Editor and I executed the transaction blog in two step.
    First I executed BEGIN TRANSACTION blog and then I tried to run COMMIT blog
    but the query editor returns very interesting error about this transaction blot.
    BEGIN TRANSACTION
    UPDATE EditorSampleTable SET UserName='xxxxx'
    WHERE ID=2
     

    after this execution ı tried to run COMMIT BLOG in the same session

    COMMIT TRAN
    The error message is very interesting
    Failed to execute query. Error: The COMMIT TRANSACTION request has no corresponding
    BEGIN TRANSACTION.

    And when ı tried to execute the same query Manegment Studio it executed  proper.

  • Grant Fritchey

    SSC Guru

    Points: 396385

    So here's the deal, the Azure Query Editor window doesn't allow you to highlight a query and run it in parts. It runs the whole query as a single statement. I tested this several times. You can't do what you're trying to do there.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey

    SSC Guru

    Points: 396385

    Wait though.... That doesn't make sense. Hang on. More testing needed.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey

    SSC Guru

    Points: 396385

    Sorry about that. I was able to replicate your problem. It looks like some sort of bug. From what I can tell (and I haven't tried setting up extended events to monitor this), the transaction without a commit or rollback is just rolled back. I can't see any evidence of any open or active transactions through the DMVs.  I reported this as a bug. You should too. Click the little feedback icon and put in a frowny face (I guess we're all kids now).

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • ESAT ERKEC

    SSCertifiable

    Points: 5558

    Hi Grant;
    Thanks for your reply. 
    Actually I thought that issue is  a bug but
    SQL Azure default isolation level confused me. Therefor
    I posted a question.

  • ESAT ERKEC

    SSCertifiable

    Points: 5558

    Hi Grant;
    Could you share your bug report link?

    Thanks

  • Grant Fritchey

    SSC Guru

    Points: 396385

    ESAT ERKEC - Wednesday, September 12, 2018 6:17 AM

    Hi Grant;
    Could you share your bug report link?

    Thanks

    I used the feedback link. There isn't a way to share it.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply