Hi, I've recently upgraded from SQL Server 6.5 to 2000 and now my application (PB9) sometimes displays the message 'The COMMIT transaction request has no corresponding BEGIN transaction ' when I try to save changes. Any suggestions would be very very helpful. Thanks.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
PB9 and SQL Server 2000
- Thread starter 061178
- Start date
- Thread starter
- #3
- Thread starter
- #4
In our applications we use a custom transaction object (PFC based) with a variety of methods on it for handling transactions. We intially connect with autocommit set to true. Prior to starting a transaction we set autocommit to false which forces a begin transaction with sql2000. If the database saves/deletes/updates are successful we set the autocommit back to true which commits the transaction. If the saves/etc. are unsuccessful we issue a rollback statement and then set the autocommit to true (completing the previously opened transaction).
Matt
"Nature forges everything on the anvil of time
Matt
"Nature forges everything on the anvil of time
- Thread starter
- #6
Thanks for your help Matt. In our application it is actually doing it the other way around as in AC = True we then send our own 'Begin Transaction' and commit. I could try it the other way that you've mentioned, but that would involve altering the PFC and this would affect all datawindows so I would prefer not to do this. Another reason I've found out is that some of the triggers and stored procedures are performing ROLLBACK which would mean that when the code in Powerbuilder comes to the COMMIT there is nothing to COMMIT. To check these out will take time, is it feasible to set the compatability level in 2000 to 6.5. This fixes the problem, but is it recommended?
- Thread starter
- #8
Hi, I think that I sorted the problem in the pfc (pfc_endtran) there is an instance where the code performs a COMMIT and then if fails tries to ROLLBACK. This was the problem.
I wonder if you can help me on my next question. Once all new scripts have been run on SQL Server 2000 database is it necessary to re-compile the stored procedures? If it is, do datawindows need to know that SPs have been recompiled (internally SPs could have different ObjectID now they are recompiled and will almost certainly have different queryplan.) For example do DWs need to be opened and saved to ensure links are OK? Thanks.
I wonder if you can help me on my next question. Once all new scripts have been run on SQL Server 2000 database is it necessary to re-compile the stored procedures? If it is, do datawindows need to know that SPs have been recompiled (internally SPs could have different ObjectID now they are recompiled and will almost certainly have different queryplan.) For example do DWs need to be opened and saved to ensure links are OK? Thanks.
- Status
Similar threads
- Locked
- Question