Andreas_Sprosec_7439

SQL Server - Implicit Transactions

Discussion created by Andreas_Sprosec_7439 on Jun 7, 2017

You Asked 

Is IMPLICIT_TRANSACTIONS turned on by default by Automic or Microsoft?

Also, is there a way to turn it off globally or is setting SET IMPLICIT_TRANSACTIONS off; in the job the only way? 

And we said … 

By default, each statement ends with a semicolon. If this character is used within an SQL statement, a different separator is defined and the job automatically inserts the statement SQL_SET_STATEMENT_TERMINATOR in the script. 

The database agent uses SQL statements exactly as specified in the job. Statements are not adjusted before they are passed on to the database. Therefore, Automic strongly recommends only using SQL statements the database supports. For example, the MS SQL Server command GO is adjusted by the Query Analyzer before it is passed on to the database. No such adjustment takes place within the database agent. 

For MS SQL Server Databases (especially in combination with transactions): SET IMPLICIT_TRANSACTIONS is set to the default value ON. Further information is available in the corresponding Microsoft documentation. 

https://technet.microsoft.com/en-us/library/ms175182(v=sql.105).aspx 

API Implicit Transactions 

ODBC

  • Call the SQLSetConnectAttr function with Attribute set to SQL_ATTR_AUTOCOMMIT and ValuePtr set to SQL_AUTOCOMMIT_OFF to start implicit transaction mode.
  • The connection remains in implicit transaction mode until you call SQLSetConnectAttr with Attribute set to SQL_ATTR_AUTOCOMMIT and ValuePtr set to SQL_AUTOCOMMIT_ON.
  • Call the SQLEndTran function with CompletionType set to either SQL_COMMIT or SQL_ROLLBACK to commit or roll back each transaction.
  • When SQL_AUTOCOMMIT_OFF is set by an ODBC application, the SQL Server ODBC driver issues a SET IMPLICIT_TRANSACTION ON statement.

Outcomes