SQL Server - MBS SQL - Backup not allowed within implicit or explicit transaction

Hi,

I’m using MBS SQL Plugin to connect to a SQL Server Database 2008r2.
The connection type is ODBC.

Connection (for Windows) is:

sqmDB = New SQLConnectionMBS sqmDB.Option("OLEDBProvider") = "SQLNCLI11" try Dim odbc As String = "Driver={SQL Server Native Client " + sqClient + "};DriverCompletion=SQL_DRIVER_NOPROMPT;MARS_Connection=yes;SERVER=" + sqServer + ";Database=" + sqDBname sqmDB.Connect(odbc, sqDBuser, sqDBpassword, SQLConnectionMBS.kODBCClient) // PcName\\SqlServerInstanceName@DatabaseName
I’m trying to create a backup but I’m getting an error “backup not allowed within implicit or explicit transaction”.

MS doc says ODBC connection has IMPLICIT_TRANSACTION ON.

How can I set the implicit transaction off ?

I tried setting the Autocommit to False

    sqmDB.AutoCommit = SQLConnectionMBS.kAutoCommitOff

I tried to execute a SQL command to set it off

"SET IMPLICIT_TRANSACTIONS OFF;"

I’m still getting an error “backup not allowed within implicit or explicit transaction”.

All ideas are welcome.

Thanks,

Olivier

Maybe you need to send a SET ANSI_DEFAULTS OFF; before the SET IMPLICIT_TRANSACTIONS OFF; ?
Because the ODBC Driver uses Ansi ON by default and the ANSI ON causes an I.T. ON?

Hi Sascha,

Thanks for helping.
It doesn’t work. Both ANSI_DEFAULTS and IMPLICIT_TRANSACTIONS seems to be ignored.

However, I finally got it working by changing the AutoCommit just before performing the BACKUP DATABASE.
The working sequence is

sqmDB = New SQLConnectionMBS
odbc = "Driver={...};...;SERVER=" + sqServer + ";Database=" + sqDBname
sqmDB.Connect(odbc, sqDBuser, sqDBpassword, SQLConnectionMBS.kODBCClient)       
sqc = New SQLCommandMBS
str = "BACKUP DATABASE " + sqDBname + " TO DISK = '" + bkp.NativePath + "'"
Try
  sqc.Connection = sqmDB
  sqc.setCommandText(Str)
  
  ' Execute command
 sqmDB.AutoCommit = sqmDB.kAutoCommitOff
 sqc.Execute
  sqmDB.AutoCommit = sqmDB.kAutoCommitOn
  
  ' Error Handling
Catch r as SQLErrorExceptionMBS
  ...
End Try

Now it’s fine.

Thanks anyway !

Olivier