In manual commit mode, SQLEndTran() starts a new transaction

• chris.breemer

  SSC Journeyman

  Points: 82

  More actions

  March 21, 2017 at 9:38 am

  #335831

  (Also posted on MSDN - I hope this is not considered cross posting - if it is, I apologize )

  Hi all,

  I have not posted in these forums before. Hope someone can help me - I could not find the answer to my query anywhere.

  I'm accessing SQL Server via the ODBC API, nothing complicated. I set SQL_AUTOCOMMIT_OFF, then make the connection.

  Now when I call SQLExecDirect(), a transaction is implicitly created. All fine, this is as expected and documented.

  Then I want to end this transaction, so I call SQLEndTran(). It does end the transaction (which is good) but also immediately starts a new one with a different ID (which is bad, IMO). I can't find anything in the docs to justify this. In fact the docs say that a new transaction will be started by the first command AFTER SqlEndTran().

  I'm using SQL Server 2014 and the Microsoft SQL Server Native Client Version 11.00.2100.

  Interestingly, this problem does not occur when I use the Microsoft SQL Server ODBC Driver Version 10.00.10240. Now, the SQlExecDirect() call does not produce a transaction, which seems contrary to the documentation too....

  My complete C code is vanilla as can be:

  #define _CRT_SECURE_NO_WARNINGS
#include <windows.h>
#include <stdio.h>
#include <sqlext.h>

  #define DSN   "mss-native"

  SQLHENV  hEnv;
SQLHDBC  hDbc;
SQLHSTMT  hSta;
SQLCHAR  message[1025];
SQLRETURN ret;
SQLSMALLINT len = 1024;
char   conn[80];

  void main()
{
  sprintf(conn, "DSN=%s", DSN);
  ret = SQLAllocHandle  (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
  ret = SQLSetEnvAttr  (hEnv,SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
  ret = SQLAllocHandle  (SQL_HANDLE_DBC, hEnv, &hDbc);
  ret = SQLSetConnectAttr (hDbc, SQL_ATTR_AUTOCOMMIT, (void *)SQL_AUTOCOMMIT_OFF, 0);
  ret = SQLDriverConnectA (hDbc, 0, conn, (SQLSMALLINT)strlen(conn), message, sizeof(message)-1, &len, SQL_DRIVER_NOPROMPT);
  ret = SQLAllocHandle  (SQL_HANDLE_STMT, hDbc, &hSta);
  ret = SQLExecDirectA  (hSta, "select @@version", 16);
  ret = SQLEndTran   (SQL_HANDLE_DBC, hDbc, SQL_COMMIT);
}


  Maybe I am missing something. Any help/ideas would be hugely appreciated.

  Thanks,

  Chris