[freetds] MSSQL gotcha

Andrew Pimlott andrew at pimlott.net
Wed Jun 25 15:10:58 EDT 2003


I just tracked down a problem connecting to MSSQL (2000?), which I
thought I'd report here.  I'm using sqsh and freetds on Debian
GNU/Linux testing.  I found that I could connect to the MSSQL
database and run commands, but they didn't behave the same as when I
used native MSSQL tools.  For example, in MSSQL,

    create table foo (bar image);
    insert into foo values (null);

works fine.  But in sqsh, it would reply

    Cannot insert the value NULL into column 'bar', table 'pimlott.dbo.foo';
    column does not allow nulls. INSERT fails.

After some tinkering, I discovered in freetds.conf, the protocol
version was set to 4.2, and when I changed it to 7.0, I got the same
behaviour as in MSSQL.

The moral is that the protocol version does not just affect what
features are available, but may affect how the database server
interprets commands.  It would be nice to add a note about this to
the User Guide, Choosing a TDS protocol version
(http://www.freetds.org/userguide/x574.htm).  Perhaps something like

    The protocol version may also affect how database servers
    interpret commands.  For example, Microsoft SQL Server 2000 is
    known to behave differently with versions 4.2 and 7.0.  Version
    7.0 is recommended for compatibility with SQL Server tools.

It would be nice if you could auto-detect this and choose a protocol
version appropriate for the product, but I don't know if that's
feasible.

Andrew



More information about the FreeTDS mailing list