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.
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?
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
[quote=437699:@Jay Madren]
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?[/quote]
I’ll give that a shot.
I’ll test that too.
[quote=437700:@Norman Palardy]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[/quote]
Didn’t even know you could do that. I’ll give it shot.
The last time I tested, the transaction count was always +1 of what it should have been.
that suggests the plugin is auto creating one and there doesnt appear to be a way to tell it NOT to behave this way
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.
How have you solved it last time? Flashback 
[quote=437700:@Norman Palardy]have you tried one of the other begin transaction syntaxes that are possible ?
maybe name the transaction ? something like[/quote]
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, …).
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…
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
[quote=437713:@Jürg Otter]
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…[/quote]
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 !!!)
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.
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).
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).
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.
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.
is it possible you are doing the begin tran on a different connection?
the spid wont survive if you close the connection.
Ah, right - thanks for the info
Bingo…! I guess you’re on the right track with this.
I’ve just found my old <https://xojo.com/issue/32121>.
@Bob Keeney : Is it possible that you’re running into this issue?
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.