SQL Server Trouble with Rails

Question

Ok, I've been trying to get this to work like all day now and I'm barely any further from when I started.

I'm trying to get Ruby On Rails to connect to SQL Server. I've installed unixODBC and configured it and FreeTDS and installed just about every Ruby gem relating to ODBC that exists.

(This has been updated to show the output of isql with -v)

[earlz@earlzarch myproject]$ tsql -S AVP1 -U sa -P pass
locale is "en_US.UTF-8"
locale charset is "UTF-8"
1> quit
[earlz@earlzarch ~]$ isql -v AVP1 sa jarhead1
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect
[earlz@earlzarch myproject]$ rake db:version
(in /home/earlz/myproject)
rake aborted!
IM002 (0) [unixODBC][Driver Manager]Data source name not found, and no default driver specified

(See full trace by running task with --trace)

so, as you can see, tsql works, but not isql. What is the difference in the two that breaks it?

/etc/odbc.ini

[AVP1]
      Description     = ODBC connection via FreeTDS
      Driver = TDS
      Servername      = my.server
      UID = sa
      PWD = pass
      port = 1232
      Database        = mydatabase

/etc/odbcinst.ini

[TDS]
     Description     = v0.6 with protocol v7.0
     Driver          = /usr/lib/libtdsodbc.so
     Setup           = /usr/lib/libtdsS.so
     CPTimeout       =
     CPReuse         =
     FileUsage       = 1

(and yes, I've made sure that the .so files exist)

the relevant part in freetds.conf

[AVP1]
      host = my.server
      port = 1232
      tds version = 8.0

and finally, my database.yml

development:
    adapter: sqlserver
    mode: odbc
    dsn: AVP1
    username: sa
    password: pass

Can anyone please help me before I pull all my hair out?

I am using a 64 bit Arch Linux that is completely up to date.

What could be causing isql to fail. I've tried every solution I've seen so far for this problem but none of them are actually working for me. Do I have to recompile FreeTDS or something?

Ok, I have also verified with strace that it is finding the configuration file, as shown by this excerpt:

open("/etc/odbc.ini", O_RDONLY)         = 3
fstat(3, {st_mode=S_IFREG|0644, st_size=159, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fc71fe09000
read(3, "[AVP1]\n      Description = ODBC "..., 4096) = 159

Answer 1

Ok, I finally figured it out after only 2 straight days of banging my head against the wall.

I'll try to give as much info as possible so that if someone finds this in the same situation I was in, they'll find this useful.

[earlz@earlzarch ~]$ cat /etc/odbc.ini
[AVP1]
Description=ODBC connection via FreeTDS
Driver=/usr/lib/libtdsodbc.so
Server=192.168.0.100
UID=sa
PWD=pass
Port=1232
ReadOnly=No
[earlz@earlzarch ~]$ cat /etc/odbcinst.ini
[TDS]
     Description     = v0.60 with protocol v7.0
     Driver          = /usr/lib/libtdsodbc.so
     Driver64 = /usr/lib
     Setup           = /usr/lib/libtdsS.so
     Setup64 = /usr/lib
     CPTimeout       =
     CPReuse         =
     FileUsage       = 1
[earlz@earlzarch ~]$ cat /etc/freetds/freetds.conf
[global]
        tds version = 8.0
        initial block size = 512
        swap broken dates = no
        swap broken money = no
        try server login = yes
        try domain login = no
        cross domain login = no
        # 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

[TDS]
        host = 192.168.0.100
        port = 1232
        tds version = 8.0

and if your lucky, after that:

[earlz@earlzarch ~]$ isql -v AVP1
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed
[ISQL]ERROR: Could not SQLConnect
[earlz@earlzarch ~]$ isql -v AVP1 sa pass
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

I did not have to set any kind of environmental variables and I didn't have to manually compile anything either with Arch Linux 64bit (date April 7th, 2010). After getting isql to work, Rails immediately connected to the database also. Now I just have to figure out why db:schema:load isn't working, but thats another question :)

Also, notice the only real difference between this set of files and the last is in /etc/odbc.ini I set the Driver field to be the actual file name of a driver rather than named for some configuration entry.