FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

Federico Alves wrote:
>   sp_configure        'user options'
> name                                minimum     maximum     config_value
> run_value
> ----------------------------------- ----------- ----------- ------------
> -----------
> user options                        0           32767       4486
> 4486
> 
> I think the experiment is irrefutable: Set the option "tds version =
> 8.0", connect with tsql, and the settings "implicit transactions" is OFF
> (correct). Disconnect, comment out the option on freetds, reconnect
> (same user and password), and "implicit transactions" Is ON. 

Bingo!  Your database's "user options" is configured to 4486. 
IMPLICIT_TRANSACTIONS is the second bit, value 2, turned on:

1> select 4486 & 2 as impval, 4486 & ~2 as newval
2> go
 impval      newval     
 ----------- -----------
           2        4484

As Peter suggests, 

        sp_configure 'user options', 0

or, to turn off only implicit transactions, 

        sp_configure 'user options', 4484

should cause connections to be created with implicit_transactions OFF.  

You did say, though

> My user-options in SQL Server are the default, no change there. 

Your currently configured value is not a default I've ever seen.  I am
fairly confident that after changing it as above that you will find
implicit transactions are off for TDS 4.2 ct-lib connections.  You might
want to look into the effects of the other settings -- the rest of the
bits in the "user options" setting -- in case they also are having
unexpected effects.  (Probably you just want to set it to be the same as
your other servers.) 

I followed Microsoft's procedure:

http://support.microsoft.com/kb/156498/EN-US/

and the created procedure does just what you need to explain which options
are in effect for the connection.  It would be interesting to see the
output for TDS 4.2 and 8.0 connections.  Too bad it's not part of the
regular set of stored procedures.  

Please note that the *documented* behavior of the implicit_transactions
bit in "user options" is that it affects only db-lib connections.  As I
have explained, a ct-lib connection is not a db-lib connection, and by
rights this option should not affect a ct-lib connection.  But my guess is
the documentation is simply wrong.  It wouldn't be the first time the
documentation was misleading or wrong with regard to default options. 
Microsoft evinces some confusion about what their driver does versus what
their server does.  

Let us know how you fare.  

--jkl