MS SQL Server Transactions

  1. 7 months ago

    Bob K

    22 May 2019 Pre-Release Testers, Xojo Pro, Third Party Store Kansas City

    Is there a trick to using Transactions in SQL Server? If I do a Begin Transaction and then attempt to do a Commit I always get the following error:

    The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

    At first I thought was because I was using PreparedStatements but this happens with straight SQL too.

    For my purposes MBS is NOT a solution so please don't bring it up.

    Need a solution or I'm giving some money back to a client.

  2. Jay M

    22 May 2019 Pre-Release Testers, Xojo Pro NC, USA

    Just a shot in the dark, as I haven't attempted transactions on SQL Server, but have you tried both Xojo's database.Commit method and executing a "Commit Transaction" statement? And both give the same result?

    How about checking "Select @@TRANCOUNT"; right after the Begin Transaction, to verify the transaction is open?

  3. Norman P

    22 May 2019 Pre-Release Testers, Xojo Pro under a bus
    Edited 7 months ago

    is the plugin maybe autostarting a transaction ?
    the select of @@trancount would tell you

    have you tried one of the other begin transaction syntaxes that are possible ?
    maybe name the transaction ? something like

    BEGIN  TRANSACTION  < transaction_name_here>

    which you'd have to likely commit with a manual

    COMMIT  TRANSACTION  < transaction_name_here>

    EDIT :
    is the behaviour new to aspecific release of Xojo & associated plugins ?
    I'll assume it is and that youv'e not had this issue before since I cant imagine you hadnt checked this before

    been a long time since I touched ms sql

  4. Bob K

    22 May 2019 Pre-Release Testers, Xojo Pro, Third Party Store Kansas City

    @Jay M Just a shot in the dark, as I haven't attempted transactions on SQL Server, but have you tried both Xojo's database.Commit method and executing a "Commit Transaction" statement? And both give the same result?

    I'll give that a shot.

    @Jay M How about checking "Select @@TRANCOUNT";; right after the Begin Transaction, to verify the transaction is open?

    I'll test that too.

  5. Bob K

    22 May 2019 Pre-Release Testers, Xojo Pro, Third Party Store Kansas City

    @Norman P is the plugin maybe autostarting a transaction ?
    the select of @@trancount would tell you

    have you tried one of the other begin transaction syntaxes that are possible ?
    maybe name the transaction ? something like

    BEGIN  TRANSACTION  < transaction_name_here>

    which you'd have to likely commit with a manual

    COMMIT  TRANSACTION  < transaction_name_here>

    been a long time since I touched ms sql

    Didn't even know you could do that. I'll give it shot.

  6. Tim P

    22 May 2019 Pre-Release Testers Rochester, NY

    @Jay M How about checking "Select @@TRANCOUNT"; right after the Begin Transaction, to verify the transaction is open?

    The last time I tested, the transaction count was always +1 of what it should have been.

  7. Norman P

    22 May 2019 Pre-Release Testers, Xojo Pro under a bus

    that suggests the plugin is auto creating one and there doesnt appear to be a way to tell it NOT to behave this way

  8. Bob K

    22 May 2019 Pre-Release Testers, Xojo Pro, Third Party Store Kansas City

    @Jay M How about checking "Select @@TRANCOUNT";; right after the Begin Transaction, to verify the transaction is open?

    Actually, it looks like this might be the problem. Doing the Select @@TRANCOUNT right after the Begin Transaction brings back a count of 0. This is probably the culprit.

  9. Jürg O

    22 May 2019 Pre-Release Testers, Xojo Pro
    Edited 7 months ago

    @Bob K Is there a trick to using Transactions in SQL Server?

    How have you solved it last time? Flashback ;)

    @Norman P have you tried one of the other begin transaction syntaxes that are possible ?
    maybe name the transaction ? something like

    I think to remember that if you want to name your Transaction TRAN it's:

    BEGIN TRAN
    INSERT/UPDATE ...whatever...
    END TRAN

    And I think to remember I've been having issues when trying to use Stored Procedures inside Transactions (sp_helplogins, sp_addrolemember, ...).

    @Norman P is the plugin maybe autostarting a transaction ?

    Last time I've used Xojo's MSSQLServerDatabase Plugin, there hasn't been an .AutoCommit setting available.
    What I see in our projects is that the very first .SQLExecute after .Connect for MS-SQL is always:

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

    But I can't say if that makes this difference...

  10. Norman P

    22 May 2019 Pre-Release Testers, Xojo Pro under a bus
    Edited 7 months ago

    @Jürg O I think to remember that if you want to name your Transaction TRAN it's:

    BEGIN TRAN
    INSERT/UPDATE ...whatever...
    END TRAN

    Nope - you can name them v
    This is a Sybaseism that has roots way way way back when (like 1993/4)
    see https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-2017

    @Jürg O What I see in our projects is that the very first .SQLExecute after .Connect for MS-SQL is always:

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

    But I can't say if that makes this difference...

    It shouldnt be necessary but it's safe
    Basically sets the session to not do dirty reads of uncommited data (which you could do but its not that generally useful since a transaction COULD rollback then what you read is invalid and blech !!!!!!!!!)

  11. Jay M

    22 May 2019 Pre-Release Testers, Xojo Pro NC, USA

    @Bob K Actually, it looks like this might be the problem. Doing the Select @@TRANCOUNT right after the Begin Transaction brings back a count of 0. This is probably the culprit.

    Have you checked for errors after the Begin Transaction?

  12. Bob K

    22 May 2019 Pre-Release Testers, Xojo Pro, Third Party Store Kansas City

    @Jay M Have you checked for errors after the Begin Transaction?

    Yes.

    If I take this into a simplified project it something that ActiveRecord is doing. It's odd because neither MySQL, Postgres, CubeSQL or any of the other DB servers have this same issue. Time to dig into some very old code.

  13. Jay M

    22 May 2019 Pre-Release Testers, Xojo Pro NC, USA

    @Bob K If I take this into a simplified project it something that ActiveRecord is doing.

    I don't follow. Are you saying that the error doesn't occur in a simple project?

  14. Bob K

    22 May 2019 Pre-Release Testers, Xojo Pro, Third Party Store Kansas City

    @Jay M I don't follow. Are you saying that the error doesn't occur in a simple project?

    Correct. This is something specific to our ActiveRecord implementation. In a simple standard xojo project the Transactions work as expected. Something changed in the plugin that's affecting ActiveRecord because it worked at some point.

    What are the things that would cause a transaction to get dropped? We're not closing the connection. Nor are we trying to reconnect (to the best of my knowledge).

  15. Jay M

    22 May 2019 Pre-Release Testers, Xojo Pro NC, USA

    @Bob K What are the things that would cause a transaction to get dropped? We're not closing the connection. Nor are we trying to reconnect (to the best of my knowledge).

    If @@trancount is zero right after beginning the transaction, then I'd say the transaction never started. You should look at the SQL Server logs for clues (may have to enable or configure some logging options to capture any meaningful data).

  16. Scott C

    22 May 2019 Pre-Release Testers, Xojo Pro Vancouver, Canada
    Edited 7 months ago

    If you have Admin access to the SQL Server, maybe try installing SQL Server Profiler (it's free), if it isn't installed already, then you can see all the connections, commits and SQL being passed from your client application to the database - and when. Profiler has to be installed on the database server, not the machine running your application.

    I don't recommend installing Profiler on a production server, only if you have full control over your database in a dev environment, because Profiler will pick up all client traffic (unless you apply some advanced filtering).

    I hope that helps.

  17. natale p

    22 May 2019 Pre-Release Testers, Xojo Pro

    @Bob K Is there a trick to using Transactions in SQL Server?

    some time ago I couldn't solve the problem.
    then I solved it using the stored procedures of Sql Server.
    maybe this isn't the right solution for you.

  18. Russ L

    23 May 2019 Pre-Release Testers, Xojo Pro

    is it possible you are doing the begin tran on a different connection?
    the spid wont survive if you close the connection.

  19. Jürg O

    23 May 2019 Pre-Release Testers, Xojo Pro
    Edited 7 months ago

    @Norman P Nope - you can name them vThis is a Sybaseism that has roots way way way back when (like 1993/4)see https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-2017

    Ah, right - thanks for the info ;)

    @Russ L is it possible you are doing the begin tran on a different connection?

    Bingo...! I guess you're on the right track with this.
    I've just found my old Feedback Case #32121.

    @Bob K : Is it possible that you're running into this issue?

  20. Jay M

    23 May 2019 Pre-Release Testers, Xojo Pro NC, USA

    Bob,

    Did some testing and found that if you create a RecordSet and don't close it before reusing the RecordSet (or creating a new one), you will cause a new SQL Server connection/session to start on the next Select, which will have its own transaction state. Maybe you're doing something like this in ActiveRecord?

    Jürg, I see in your feedback example project that you are not closing the RecordSet after the Select, which then produces this problem.

    Also found that you can get back to the original session by closing the RecordSet, and the original transaction state is still intact. In other words, you could start a second RecordSet without closing the first one, which will then be in its own session & transaction, then close both recordsets and start a new recordset, and your original transaction state will still be open, ready for you to commit it.

  21. Newer ›

or Sign Up to reply!