Highlighted
Super Contributor.
Super Contributor.
2001 views

ALTER DATABASE statement in ESQL gets "not allowed in multi-statement transaction"?

Jump to solution
I am connecting to MSSQL 2012 from a managed console program using OpenESQL and ADO. I need to completely replace the data in several tables from some data files. I wrote a proof-of-concept that connected, truncated, read-and-inserted, committed and disconnected. It worked great. But in our production environment I need to minimize the volume of the database log files by temporarily shifting db RECOVERY from FULL to SIMPLE, and then shift it back after the loading is done. My "EXEC SQL ALTER DATABASE NEAS SET RECOVERY SIMPLE END-EXEC" statement returns with SQLCODE -226, SQLSTATE IM999 and MFSQLMESSAGETEXT "ALTER DATABASE statement not allowed within multi-statement transaction." Can anyone shed some light on this? VC 2.3.2 for VS 2013 on Windows 7.
0 Likes
1 Solution

Accepted Solutions
Highlighted
Absent Member.
Absent Member.
Basically the error is telling you that ALTER DATABASE needs to be executed in AUTOCOMMIT mode. You need to add an EXEC SQL SET AUTOCOMMIT ON END-EXEC before and an EXEC SQL SET AUTOCOMMIT OFF END-EXEC after the ALTER DATABASE. It's also a good idea to do an EXEC SQL COMMIT END-EXEC before any of this to ensure that you have no uncommitted work that might stop the switch to AUTOCOMMIT mode working.

View solution in original post

0 Likes
2 Replies
Highlighted
Absent Member.
Absent Member.
Basically the error is telling you that ALTER DATABASE needs to be executed in AUTOCOMMIT mode. You need to add an EXEC SQL SET AUTOCOMMIT ON END-EXEC before and an EXEC SQL SET AUTOCOMMIT OFF END-EXEC after the ALTER DATABASE. It's also a good idea to do an EXEC SQL COMMIT END-EXEC before any of this to ensure that you have no uncommitted work that might stop the switch to AUTOCOMMIT mode working.

View solution in original post

0 Likes
Super Contributor.
Super Contributor.
Yep! That got it. Thank you.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.