Problem COMMIT with MARS connections enable (ODBC)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem COMMIT with MARS connections enable (ODBC)

Author

Topic

Yan302
Starting Member

3 Posts

Posted - 2009-03-26 : 12:36:20

Hi all,

I've got trouble using commit with MARS connection enable.
Here is my steps :

1°) Connection :
connectString = Driver={SQL Native Client};Server=BROL\SQLEXPRESS; Database=test;Trusted_Connection=Yes;MARS_Connection=Yes;"

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &ODBCenv)
SQLSetEnvAttr(ODBCenv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER *)SQL_OV_ODBC3, 0);
SQLAllocHandle(SQL_HANDLE_DBC, ODBCenv, &ODBCcon)
SQLDriverConnect(ODBCcon, NULL, connectString, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT)

2°) set implicit_transaction to ON :
SQLExecDirect(stmt, "SET IMPLICIT_TRANSACTIONS ON", strlen("SET IMPLICIT_TRANSACTIONS ON"))

3°) Alloc, prepare and bind for an insert statement :
SQLAllocHandle(SQL_HANDLE_STMT, ODBCcon,&(curs->stmt)
SQLPrepare(curs->stmt, curs->str, strlen(curs->str)
[some bind]

4°) Then execute :
SQLExecute(curs->stmt)

No problem/error until that point.

5°) Want to commit :
SQLExecDirect(stmt, "COMMIT", strlen("COMMIT"))

And there I got the error :
"The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION."

But in fact, a begin transaction should not be present while IMPLICIT_TRANSACTION is set to ON. If I remove the option "Mars Connection" from the connection string ; I don't have the problem anymore but I need this option.

Someone got any idea ?

Thanks,

Yan302

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-27 : 09:22:05

It may have to do with SQLExecDirect being designed to get another result set. Instead, you might try using SQLEndTran to commit the transaction.

Yan302
Starting Member

3 Posts

Posted - 2009-03-30 : 05:28:57

Hi,

Thanks for your answer !

I've tried to use SQLEndTrans instead of SQLExecDirect (COMMIT). Got no errors but the commit has not been executed (new record inserted is not visible).

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-30 : 11:35:46

I was mistaken in my initial recommendation to use SQLEndTrans. Even outside of MARS, using T-SQL IMPLICIT_TRANSACTIONS to open a transaction and then invoking an ODBC API to commit it seems to be against Microsoft recommendations.

For example, on this page look for the section titled "Specifying Transaction Boundaries": http://msdn.microsoft.com/en-us/library/ms175523.aspx.

They go so far as to say that that "can lead to undefined results". So, what you were attempting to do was probably the right thing conceptually, but I don't have an answer as to why it is failing. You might try disabling MARS in the connection string to see if it is MARS that is causing the issue.

Yan302
Starting Member

3 Posts

Posted - 2009-03-31 : 04:20:12

Hi,

Yes, disabling MARS "resolve" the problem but well I need to have this functionnality activated...

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-31 : 09:26:26

You can't use an EXPLICIT transaction instead?

If not, perhaps try tacking the COMMIT onto the last sql statement you sent, something like


3.5) stmt &= ' ; COMMIT'
4°) Then execute : SQLExecute(curs->stmt)

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources