Having Troubles with UnixODBC, FreeTDS, and PyODBC

So I am having great difficulty getting all three of these to work together in harmony. I guess I’ll list all the various configurations, along with the test code to see if a pair of fresh eyes can realize my stupidity.

I’m running 12.04 Unbuntu Server and I’m trying to connect to a MSSQL Server 2008 and end up using it with PyODBC.

  • SQL How can I optimize splitting a string and inserting the words into a new table?
  • Change Value on SQL View
  • How to drop a list of SQL Server tables, ignoring constraints?
  • How to check are there JSON Functions by SQL query?
  • my query works in SQL but not in SSRS
  • Split values over multiple rows
  • However, when just putting in

    tsql -S T2 -U Foo -P Bar
    

    I get the

    1>
    2>
    3>
    4>
    5>
    6>
    7>
    8>
    9>
    10>
    11>
    

    and etc.

    Anyway, if anyone would be able to help (and I would be eternally grateful if you can clear me of this haze), here are my current configurations.

    This is my /etc/odbc.ini

    [ODBC Data Sources]
    odbcname     = MySQL
    T2           = MSSQL
    
    [odbcname]
    Driver       = /usr/lib/odbc/libmyodbc.so
    Description  = MyODBC 3.51 Driver DSN
    SERVER       = Foobar
    PORT         = 3306
    USER         = Foo
    Password     = Bar
    Database     = Foobar
    OPTION       = 3
    SOCKET       =
    
    [T2]
    Driver       = FreeTDS
    Description  = ODBC connection via FreeTDS
    SERVER       = FOOBAR
    PORT         = 1433
    USER         = Foo
    Password     = Bar
    Database     = Foobar
    OPTION       = 3
    SOCKET       =
    
    [Default]
    Driver       = /usr/local/lib/libmyodbc3.so
    Description  = MyODBC 3.51 Driver DSN
    SERVER       = FOOBAR
    PORT         = 3306
    USER         = foo
    Password     = bar
    Database     = FOOBAR
    OPTION       = 3
    SOCKET       =
    

    The following is my /etc/odbcinst.ini

    [FreeTDS]
    Description=FreeTDS Driver
    Driver=/usr/lib/odbc/libtdsodbc.so
    Setup=/usr/lib/odbc/libtdsS.so
    CPTimeout=
    CPReuse=
    FileUsage=1
    

    The following is my freetds.conf

    # This file is installed by FreeTDS if no file by the same
    # name is found in the installation directory.
    #
    # For information about the layout of this file and its settings,
    # see the freetds.conf manpage "man freetds.conf".
    
    # Global settings are overridden by those in a database
    # server specific section
    [global]
            # TDS protocol version
    ;       tds version = 4.2
    
            # Whether to write a TDSDUMP file for diagnostic purposes
            # (setting this to /tmp is insecure on a multi-user system)
    ;       dump file = /tmp/freetds.log
    ;       debug flags = 0xffff
    
            # Command and connection timeouts
    ;       timeout = 10
    ;       connect timeout = 10
    
            # If you get out-of-memory errors, it may mean that your client
            # is trying to allocate a huge buffer for a TEXT field.
            # Try setting 'text size' to a more reasonable limit
            #text size = 64512
    
    [T2]
            host = FOOBAR
            port = 1433
            tds version = 7.0
            client charset = UTF-8
            text size = 20971520
    [global]
            # TDS protocol version
            tds version = 7.0
    

    And my Python test file just for good measure

    import pyodbc
    import sys 
    
    try:
        #tempsystrends = pyodbc.connect('DRIVER=FreeTDS;SERVER=FOOBAR;PORT=1433;DATABASE=T2;UID=FOO;PWD=bar;TDS_Version=7.0;')
        cursor = tempsystrends.cursor()
    except pyodbc.Error as e:
            print "Error: %s" % (e.args[1])
            sys.exit(1)
    

    One Solution collect form web for “Having Troubles with UnixODBC, FreeTDS, and PyODBC”

    I connect to various databases via PHP using UnixODBC, here is my configuration for a Microsoft SQL Server:

    /etc/odbc.ini

    # Define a connection to a Microsoft SQL server
    # The Description can be whatever we want it to be.
    # The Driver value must match what we have defined in /etc/odbcinst.ini
    # The Database name must be the name of the database this connection will connect to.
    # The ServerName is the name we defined in /etc/freetds/freetds.conf
    # The TDS_Version should match what we defined in /etc/freetds/freetds.conf
    [mssql]
    Description             = MSSQL Server
    Driver                  = freetds
    Database                = XXXXXX
    ServerName              = MSSQL
    TDS_Version             = 8.0
    

    /etc/odbcinst.ini

    # Define where to find the driver for the Free TDS connections.
    [freetds]
    Description     = MS SQL database access with Free TDS
    Driver          = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
    Setup           = /usr/lib/i386-linux-gnu/odbc/libtdsS.so
    UsageCount      = 1
    

    /etc/freetds/freetds.conf

    # The basics for defining a DSN (Data Source Name)
    # [data_source_name]
    #       host = <hostname or IP address>
    #       port = <port number to connect to - probably 1433>
    #       tds version = <TDS version to use - probably 8.0>
    
    # Define a connection to the Microsoft SQL Server
    [mssql]
            host = XXXXXX
            port = 1433
            tds version = 8.0
    

    Then test your connection:

    isql mssql username password
    

    Depending on your environment your username might have to be in the format: domain\username

    After issuing the command you should see something like:

    +---------------------------------------+
    | Connected!                            |
    |                                       |
    | sql-statement                         |
    | help [tablename]                      |
    | quit                                  |
    |                                       |
    +---------------------------------------+
    SQL>
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.