Skip to main content
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.
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.
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.
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.
Yep! That got it. Thank you.
Terms & ConditionsAccessibility statement