This document was built in hopes to make getting SQL Server talking to Rails on Linux as painless as possible. It can be overwhelming if you dont know exactly what you need, so I’ve tried to lay it out. Feel free to add anything you feel was not explained in great enough detail.
New: LinuxServersThatSupportMSSQL
There are 4 variables i have captialized which should be self explanatory, but if not:
- YOUR_DB_DEFINITION_NAME – Pick something and stick with it. Used for FreeTDS and as your DSN in unixODBC. I used the same for both to keep it simple.
- YOUR_ACTUAL_DB_NAME – The actual name of the database you are trying to connect to
- USERNAME – The username for that database
- PASSWORD – The password for that database
Replace these in the places they show up below.
Assumed:
- Ruby 1.8.2
- Rails 0.13
Required:
- unixODBC >= 2.2.11 (http://www.unixodbc.org/)
- FreeTDS >= 0.63 (http://www.freetds.org/)
- Ruby ODBC >= 0.996 (http://www.ch-werner.de/rubyodbc/)
- Ruby DBI >= 0.0.23 (http://ruby-dbi.sourceforge.net/)
NOTICE you need to install unixODBC BEFORE FreeTDS. The first clue you have to do this is in the ./configure step of FreeTDS: ./configure —with-unixodbc=/usr/local
First things first
Become root
Edit you profile
Use ~/.bashrc for your local profile or /etc/profile to set this for all users.
export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc
export FREETDSCONF=/etc/freetds/freetds.conf
Without this entries your DSN drivers will not be seen.
Also if you’re loged in as user other then root make sure you can access odbc and freetds.conf.
unixODBC:
Install this via apt-get
# apt-get install unixODBC
and maybe ( some headers needed for freeTDS to compile )
<pre>
- apt-get install unixODBC-dev
On Ubuntu make sure you also install tdsodbc as the library libtdsodbc.so is not included in the package unixODBC
Or you can do it manually:
# cd /usr/local/src
# wget <a href="http://www.unixodbc.org/unixODBC-2.2.11.tar.gz">http://www.unixodbc.org/unixODBC-2.2.11.tar.gz</a>
# gunzip unixODBC*.tar.gz
# tar xvf unixODBC*.tar
# cd unixODBC*
# ./configure
# make
# make install
- ./configure —prefix=/usr/local -sysconfdir=/etc —disable-gui # Seems to work better. (Avoids QT header problems)
FreeTDS:
# wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
# tar zvxf freetds-stable.tgz
# cd freetds*/
# ./configure --with-unixodbc=/usr/local
# make
# make install
# vi /etc/freetds/freetds.conf
Red Hat Edit Line 4
- ./configure —with-odbc=/usr/local/lib
NOTE: (RHEL4 – possibly others) You need to have unixODBC-devel (up2date unixODBC-devel) installed in order to use —with-unixodbc. Otherwise libtdsodbc.so will be missing, the above “Red Hat Edit Line 4” did not work for me (it built, but no libtdsodbc.so)
NOTE: You can also FreeTDS from apt (athough you will not get tsql, but you can get “sqsh” from apt and it works the same way, why compile?)
- apt-get install freetds-dev
Add a section to /etc/freetds/freetds.conf:
[YOUR_DB_DEFINITION_NAME]
host = 192.168.1.101 (change this to the right one for you)
port = 1433
tds version = 4.2 (might be different, check "here":http://www.freetds.org/userguide/choosingtdsprotocol.htm.)
# tsql -S YOUR_DB_DEFINITION_NAME -U USERNAME -P PASSWORD
or if you are using sqsh…
- sqsh -S YOUR_DB_DEFINITION_NAME -U USERNAME -P PASSWORD
You should see and do the following to make sure all went well:
locale is "en_US.ISO-8859-15"
locale charset is "ISO-8859-15"
1> use YOUR_ACTUAL_DB_NAME
2> go
1> select * from users (do this for a table that exists in your db)
2> go
id login
1 user1
3 user2 (make sure you get correct output)
1> quit
If everything went well, you can go back in to freetds.conf and clean it up if you wish, then move on…
Create DB Definition
/etc/odbc.ini:
[YOUR_DB_DEFINITION_NAME]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = YOUR_DB_DEFINITION_NAME
Database = YOUR_ACTUAL_DB_NAME
/etc/odbcinst.ini:
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1
Depending on your OS/distro, your unixODBC configuration files may be in a different location. On Gentoo (and others?) they are located in /etc/unixODBC/.
Try it out:
- isql YOUR_DB_DEFINITION_NAME USERNAME PASSWORD
———————————————————-
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
———————————————————-
SQL> select * from users; (again, do this for a table that exists in your db)
—————————————————————-+
| id | login |
—————————————————————-+
| 1 | user1 |
| 3 | user2 |
—————————————————————-+
2 rows affected
2 rows returned
SQL> quit
NOTE: If you have problems connecting read my notice at the end of this page
NOTE: For Ubuntu this tidbit helped me out. Basically, if you do the test above with isql and you get “[ISQL]ERROR: Could not SQLPrepare”, don’t worry about it. It will still work in Rails. — OpenOcean 4-29-2008
If all went well, move on…
Ruby ODBC:
Before building ruby-odbc, make sure that you have odbc-devel installed as this is a requirement to build ruby-odbc. I used yum to install odbc-devel.
Also, note that if you are running Ubuntu “Breezey Badger,” you probably don’t have make installed, and won’t be able to run the code below. Install make by running Synaptic and searching for “make.” [David R]
# cd /usr/local/src
# wget <a href="http://www.ch-werner.de/rubyodbc/ruby-odbc-0.996.tar.gz">http://www.ch-werner.de/rubyodbc/ruby-odbc-0.996.tar.gz</a>
# tar zvxf ruby-odbc-0.996.tar.gz
# cd ruby-odbc-0.996/
# ruby extconf.rb
# make
# make install
Ruby DBI ODBC driver:
# cd /usr/local/src
# wget <a href="http://rubyforge.org/frs/download.php/655/ruby-dbi-all-0.0.23.tar.gz">http://rubyforge.org/frs/download.php/655/ruby-dbi-all-0.0.23.tar.gz</a>
# tar zvxf ruby-dbi-all-0.0.23.tar.gz
# cd ruby-dbi-all
# ruby setup.rb config --with=dbi,dbd_odbc
# ruby setup.rb setup
# ruby setup.rb install
Test everything:
Heres a quick way to see if ruby can talk to SQL Server provided by Mark Imbriaco:
# irb1.8
irb(main):001:0> require "dbi"
=> true
irb(main):004:0> dbh = DBI.connect('dbi:ODBC:YOUR_DB_DEFINITION_NAME', 'USERNAME', 'PASSWORD')
=> #<DBI::DatabaseHandle:0xb7d28688 @trace_output=#<IO:0xb7d79064>, @trace_mode=2,
@handle=#<DBI::DBD::ODBC::Database:0xb7d28480 @attr={},
@handle=#<ODBC::Database:0xb7d284a8>>>
irb(main):005:0> quit
If everything went well, go make it work in Rails:
Setup Rails
database.yml:
development:
adapter: sqlserver
mode: odbc
dsn: YOUR_DB_DEFINITION_NAME
username: USERNAME
password: PASSWORD
Add other sections in the same manner.
SQL Server working with Rails on Linux!
Great thanks to Mark Imbriaco for his work on adding ODBC support, and the others involved in the SQL Server updates.
Troubleshooting
If you get an error along the lines of ‘[iODBC] [Driver Manager]Specified driver could not be loaded’, it might help to set the Driver value in /etc/odbc.ini to the path of libtdsodbc.so.
—Thom Lawrence
NOTICE about unixODBC configuration: I downloaded the tarball and compiled on \SuSE 9.2. The odbc.ini and odbcinst.ini files in /etc/ got ignored. If the user had no ~/.odbc.ini and ~/.odbcinst.ini files they were created automatically (being empty they led to connection failure). The right way to do it (for me) was to put the ini files in /usr/local/etc, then link them from the home directory of the user. Please make sure that you use the leading dots for the file names in the home directory.
—Created 07-03-2005 by Joe Noon
The previous problem seems to be due to the fact the default configuration of the unixODBC source looks in /etc/unixODBC/ and not /etc/ for the unixODBC configuration files. If the note after the configuration section is enough to correct this problem, then these two notes should probably go away.
—llasram (2006-04-05)
My odbc and \FreeTd files were not accessable/readable by user in neither /etc nor /usr/local/etc After setting appropriate permsions everything started to work smoothly.
— GregoryBluvshteyn (2006-6-21)
In order to get this to work on a RHEL4 box, the ruby-odbc driver was looking for headers that did not exist. I installed unixODBC-2.2.8-2.3.0.2.i386.rpm and unixODBC-devel-2.2.8-2.3.0.2.i386.rpm in order to compile ruby-odbc. This configuration worked fine with SQL Server 2005. Note that mssql2k5 uses TDS 8.0, the same as sql2k.
— Jeremie Blais (2006-7-18)
My ActiveRecord objects where not being loaded with valid ids. Checking SELECT statements with tsql, I saw the row counts as correct, but some recordsets where blank. I re-compiled FreeTDS with:
# ./configure --with-unixodbc=/usr/local/lib --with-tdsver=8.0 --with-msdblib.
Downloaded ruby-odbc 0.9991. Installed with (also ran the tests…):
- ruby extconf.rb —with-dlopen
- make
- ruby test.rb DSN [uid] [pwd]
- make install
tsql and my Rails app started to show good data. RHEL4 (x86_64), Apache2, and MS SqlServer 2000.
—TerryLorber (2006-09-01)
NOTE:
In freetds-0.63 at least the freetds.conf needs to be in /usr/local/etc/freetds.conf NOT /etc/freetds/freetds.conf:
To compile freetds in UBUNTU (in order to have tsql) apart from unixodbc the package unixodbc-dev is also necessary.
— Eduardo Aldaz (2006-10-11)
When using Debian packages (unstable amd64) I was able to get nearly everything working without compiling up to where the ruby libs needed to be compiled, I did this and it worked fine. There are issues with the default packages where I was receiving this message when trying to use dbi: [RubyODBC]Cannot allocate SQLHENV
apt-get install libiodbc2
— Adam Meehan (2006-11-07)
On Ubuntu you need tdsodbc package as well.
Try installing ‘libiodbc2’ package. I got the same error and this fix it.
— Juan Matias Repetti (2007-05-16)
— John Tjanaka (2007-01-08) \m/(^o^)\m/
Note for Gentoo users:
Remember to USE mssql and odbc, because … that is the whole point ^_^;;;
- echo “dev-db/freetds mssql odbc” >> /etc/portage/package.use
- echo “dev-ruby/ruby-dbi odbc” >> /etc/portage/package.use
- echo “dev-ruby/ruby-odbc ~x86” >> /etc/portage/package.keywords
- emerge -av freetds
- emerge -av ruby-dbi
- emerge -av ruby-odbc
freetds.conf is in /etc.
The
odbc.ini and odbcinst.ini is in /etc/unixODBC, so you must remember to:
export ODBCINI=/etc/unixODBC/odbc.ini export ODBCSYSINI=/etc/unixODBC
While the
libtdsodbc.so and libtdsS.so is in /usr/lib, so in your odbcinst.ini:
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/libtdsodbc.so
Setup = /usr/lib/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1
Tips, it’s possible to connect to SQL Server through SSH Tunneling (Port Forwarding):
- ssh -L localhost_port:sqlserver_ip_or_name:1433 username@gateway_ip_or_name
Need to add a line in
/etc/ssh/ssh_config to keep connection alive:
ServerAliveInterval 300
Assuming your sqlserver port is using 1433.
In your
freetds.conf:
[YOUR_CONNECTION_DEFINITION_NAME]
host = localhost
port = localhost_port # port you used above.
tds version = 8.0 # assuming you're using SQL Server 200x.
SQL Server 2005 and Ubuntu
SQL Server 2005 requires FreeTDS 0.64 (see http://lists.ibiblio.org/pipermail/freetds/2006q4/020738.html)
Ubuntu comes with FreeTDS 0.63 so you can’t apt-get it, you have to install it manually
category: Howto
* Has anyone been able to use this with MSSQl Sevrer 2005 on x86_64 ? * Yes it works for me just fine, as long as you remember to tel Sqlserver to allow untrusted connections.Writing UTF-8 to MS Sql Server
I have been able to write to the Sql Server with the freetds and unixodbc setup as above, but when I tried to write chinese words in UTF-8 it became a row of ??.
The same thing applies to data writes in php, but a solution exists in the form of adding a N before the data in the sql query, for example
insert into mytable values(N’unicodedata’)
The N prefix ensures that Sql Server treats the data as unicode – based on information on other forums.
Does any solution exist for ROR, since we are not working directly with SQL queries? Will using the win32ole setup prevent this problem? But my impression is that win32ole only exist on windows based systems.
Could not SQLConnect
If you get these errors when using the isql-command:
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[ISQL]ERROR: Could not SQLConnect
Try to change the server = YOUR_DB_DEFINITION_NAME
to:
server = IP_OF_SERVER
in odbc.ini. Then try to issue the isql-command again.
——
Had a lot of trouble connecting via ruby even though there was no problem with sqsh etc. Kept giving me this output:
DBI::DatabaseError: 01000 (0) [unixODBC][Driver Manager]Can't open lib '/usr/lib/libtdsodbc.so' : /usr/lib/libtdsodbc.so: cannot open shared object file: No such file or directory
from /usr/local/lib/site_ruby/1.8/DBD/ODBC/ODBC.rb:95:in `connect'
from /usr/local/lib/site_ruby/1.8/dbi/dbi.rb:584:in `connect'
from /usr/local/lib/site_ruby/1.8/dbi/dbi.rb:384:in `connect'
from (irb):2
Finally found out that the linked file in /usr/lib was missing. One can check by executing this:
ls --context /usr/lib/libtdsodbc.so
Found the solution here:
https://bugs.launchpad.net/ubuntu/source/freetds/bug/68239
Basically, install the tdsodbc package, delete the symbolic link /usr/lib/libtdsodbc.so, then re-link with /usr/lib/odbc/libtdsodbc.so and re-test.
—Red
——-
On my debian etch box ruby-odbc segfault with unixodbc, windows 2003 server and SQL Server 2005.
I have switched to iodbc:
aptitude install libiodbc2-dev iodbc
compile freetds from sources (0.64)
./configure --with-tdsver=8.0 --with-iodbc
make
make install
freetds.conf is in /usr/local/etc/freetds.conf
now everything works like a charm
Furthermore, I write my database.yml like this:
development: adapter: sqlserver mode: odbc dsn: "ODBC;Driver={FreeTDS};Server=my_server;Database=my_base;Uid=my_login;Pwd=my_password;Language=English"
thus it’s not needed to bother with odbc.ini, and on MS-Windows I just need to put “SQL Native Client” instead of “FreeTDS” (I think we can also have something more dynamic using an environment variable for this)
“Language=English” resolves problems with datetime format on a French MS-Windows Server
—Laurent
——
I’ve noticed some funny behaviour of tsql during installation/test. If I try the following line in my ubuntu machine:
#tsql -H database_server_ip -p port -U user
but it works perfectly when using the same parameters in the freetds.config file, calling
#tsql -S YOUR_DB_DEFINITION_NAME -U user
don’t ask me Why.This has made me lose more than an hour, hope it helps anyone.
—Carlos
This document was built in hopes to make getting SQL Server talking to Rails on Linux as painless as possible. It can be overwhelming if you dont know exactly what you need, so I’ve tried to lay it out. Feel free to add anything you feel was not explained in great enough detail.
New: LinuxServersThatSupportMSSQL
There are 4 variables i have captialized which should be self explanatory, but if not:
- YOUR_DB_DEFINITION_NAME – Pick something and stick with it. Used for FreeTDS and as your DSN in unixODBC. I used the same for both to keep it simple.
- YOUR_ACTUAL_DB_NAME – The actual name of the database you are trying to connect to
- USERNAME – The username for that database
- PASSWORD – The password for that database
Replace these in the places they show up below.
Assumed:
- Ruby 1.8.2
- Rails 0.13
Required:
- unixODBC >= 2.2.11 (http://www.unixodbc.org/)
- FreeTDS >= 0.63 (http://www.freetds.org/)
- Ruby ODBC >= 0.996 (http://www.ch-werner.de/rubyodbc/)
- Ruby DBI >= 0.0.23 (http://ruby-dbi.sourceforge.net/)
NOTICE you need to install unixODBC BEFORE FreeTDS. The first clue you have to do this is in the ./configure step of FreeTDS: ./configure —with-unixodbc=/usr/local
First things first
Become root
Edit you profile
Use ~/.bashrc for your local profile or /etc/profile to set this for all users.
export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc
export FREETDSCONF=/etc/freetds/freetds.conf
Without this entries your DSN drivers will not be seen.
Also if you’re loged in as user other then root make sure you can access odbc and freetds.conf.
unixODBC:
Install this via apt-get
# apt-get install unixODBC
and maybe ( some headers needed for freeTDS to compile )
<pre>
- apt-get install unixODBC-dev
On Ubuntu make sure you also install tdsodbc as the library libtdsodbc.so is not included in the package unixODBC
Or you can do it manually:
# cd /usr/local/src
# wget <a href="http://www.unixodbc.org/unixODBC-2.2.11.tar.gz">http://www.unixodbc.org/unixODBC-2.2.11.tar.gz</a>
# gunzip unixODBC*.tar.gz
# tar xvf unixODBC*.tar
# cd unixODBC*
# ./configure
# make
# make install
- ./configure —prefix=/usr/local -sysconfdir=/etc —disable-gui # Seems to work better. (Avoids QT header problems)
FreeTDS:
# wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
# tar zvxf freetds-stable.tgz
# cd freetds*/
# ./configure --with-unixodbc=/usr/local
# make
# make install
# vi /etc/freetds/freetds.conf
Red Hat Edit Line 4
- ./configure —with-odbc=/usr/local/lib
NOTE: (RHEL4 – possibly others) You need to have unixODBC-devel (up2date unixODBC-devel) installed in order to use —with-unixodbc. Otherwise libtdsodbc.so will be missing, the above “Red Hat Edit Line 4” did not work for me (it built, but no libtdsodbc.so)
NOTE: You can also FreeTDS from apt (athough you will not get tsql, but you can get “sqsh” from apt and it works the same way, why compile?)
- apt-get install freetds-dev
Add a section to /etc/freetds/freetds.conf:
[YOUR_DB_DEFINITION_NAME]
host = 192.168.1.101 (change this to the right one for you)
port = 1433
tds version = 4.2 (might be different, check "here":http://www.freetds.org/userguide/choosingtdsprotocol.htm.)
# tsql -S YOUR_DB_DEFINITION_NAME -U USERNAME -P PASSWORD
or if you are using sqsh…
- sqsh -S YOUR_DB_DEFINITION_NAME -U USERNAME -P PASSWORD
You should see and do the following to make sure all went well:
locale is "en_US.ISO-8859-15"
locale charset is "ISO-8859-15"
1> use YOUR_ACTUAL_DB_NAME
2> go
1> select * from users (do this for a table that exists in your db)
2> go
id login
1 user1
3 user2 (make sure you get correct output)
1> quit
If everything went well, you can go back in to freetds.conf and clean it up if you wish, then move on…
Create DB Definition
/etc/odbc.ini:
[YOUR_DB_DEFINITION_NAME]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = YOUR_DB_DEFINITION_NAME
Database = YOUR_ACTUAL_DB_NAME
/etc/odbcinst.ini:
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1
Depending on your OS/distro, your unixODBC configuration files may be in a different location. On Gentoo (and others?) they are located in /etc/unixODBC/.
Try it out:
- isql YOUR_DB_DEFINITION_NAME USERNAME PASSWORD
———————————————————-
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
———————————————————-
SQL> select * from users; (again, do this for a table that exists in your db)
—————————————————————-+
| id | login |
—————————————————————-+
| 1 | user1 |
| 3 | user2 |
—————————————————————-+
2 rows affected
2 rows returned
SQL> quit
NOTE: If you have problems connecting read my notice at the end of this page
NOTE: For Ubuntu this tidbit helped me out. Basically, if you do the test above with isql and you get “[ISQL]ERROR: Could not SQLPrepare”, don’t worry about it. It will still work in Rails. — OpenOcean 4-29-2008
If all went well, move on…
Ruby ODBC:
Before building ruby-odbc, make sure that you have odbc-devel installed as this is a requirement to build ruby-odbc. I used yum to install odbc-devel.
Also, note that if you are running Ubuntu “Breezey Badger,” you probably don’t have make installed, and won’t be able to run the code below. Install make by running Synaptic and searching for “make.” [David R]
# cd /usr/local/src
# wget <a href="http://www.ch-werner.de/rubyodbc/ruby-odbc-0.996.tar.gz">http://www.ch-werner.de/rubyodbc/ruby-odbc-0.996.tar.gz</a>
# tar zvxf ruby-odbc-0.996.tar.gz
# cd ruby-odbc-0.996/
# ruby extconf.rb
# make
# make install
Ruby DBI ODBC driver:
# cd /usr/local/src
# wget <a href="http://rubyforge.org/frs/download.php/655/ruby-dbi-all-0.0.23.tar.gz">http://rubyforge.org/frs/download.php/655/ruby-dbi-all-0.0.23.tar.gz</a>
# tar zvxf ruby-dbi-all-0.0.23.tar.gz
# cd ruby-dbi-all
# ruby setup.rb config --with=dbi,dbd_odbc
# ruby setup.rb setup
# ruby setup.rb install
Test everything:
Heres a quick way to see if ruby can talk to SQL Server provided by Mark Imbriaco:
# irb1.8
irb(main):001:0> require "dbi"
=> true
irb(main):004:0> dbh = DBI.connect('dbi:ODBC:YOUR_DB_DEFINITION_NAME', 'USERNAME', 'PASSWORD')
=> #<DBI::DatabaseHandle:0xb7d28688 @trace_output=#<IO:0xb7d79064>, @trace_mode=2,
@handle=#<DBI::DBD::ODBC::Database:0xb7d28480 @attr={},
@handle=#<ODBC::Database:0xb7d284a8>>>
irb(main):005:0> quit
If everything went well, go make it work in Rails:
Setup Rails
database.yml:
development:
adapter: sqlserver
mode: odbc
dsn: YOUR_DB_DEFINITION_NAME
username: USERNAME
password: PASSWORD
Add other sections in the same manner.
SQL Server working with Rails on Linux!
Great thanks to Mark Imbriaco for his work on adding ODBC support, and the others involved in the SQL Server updates.
Troubleshooting
If you get an error along the lines of ‘[iODBC] [Driver Manager]Specified driver could not be loaded’, it might help to set the Driver value in /etc/odbc.ini to the path of libtdsodbc.so.
—Thom Lawrence
NOTICE about unixODBC configuration: I downloaded the tarball and compiled on \SuSE 9.2. The odbc.ini and odbcinst.ini files in /etc/ got ignored. If the user had no ~/.odbc.ini and ~/.odbcinst.ini files they were created automatically (being empty they led to connection failure). The right way to do it (for me) was to put the ini files in /usr/local/etc, then link them from the home directory of the user. Please make sure that you use the leading dots for the file names in the home directory.
—Created 07-03-2005 by Joe Noon
The previous problem seems to be due to the fact the default configuration of the unixODBC source looks in /etc/unixODBC/ and not /etc/ for the unixODBC configuration files. If the note after the configuration section is enough to correct this problem, then these two notes should probably go away.
—llasram (2006-04-05)
My odbc and \FreeTd files were not accessable/readable by user in neither /etc nor /usr/local/etc After setting appropriate permsions everything started to work smoothly.
— GregoryBluvshteyn (2006-6-21)
In order to get this to work on a RHEL4 box, the ruby-odbc driver was looking for headers that did not exist. I installed unixODBC-2.2.8-2.3.0.2.i386.rpm and unixODBC-devel-2.2.8-2.3.0.2.i386.rpm in order to compile ruby-odbc. This configuration worked fine with SQL Server 2005. Note that mssql2k5 uses TDS 8.0, the same as sql2k.
— Jeremie Blais (2006-7-18)
My ActiveRecord objects where not being loaded with valid ids. Checking SELECT statements with tsql, I saw the row counts as correct, but some recordsets where blank. I re-compiled FreeTDS with:
# ./configure --with-unixodbc=/usr/local/lib --with-tdsver=8.0 --with-msdblib.
Downloaded ruby-odbc 0.9991. Installed with (also ran the tests…):
- ruby extconf.rb —with-dlopen
- make
- ruby test.rb DSN [uid] [pwd]
- make install
tsql and my Rails app started to show good data. RHEL4 (x86_64), Apache2, and MS SqlServer 2000.
—TerryLorber (2006-09-01)
NOTE:
In freetds-0.63 at least the freetds.conf needs to be in /usr/local/etc/freetds.conf NOT /etc/freetds/freetds.conf:
To compile freetds in UBUNTU (in order to have tsql) apart from unixodbc the package unixodbc-dev is also necessary.
— Eduardo Aldaz (2006-10-11)
When using Debian packages (unstable amd64) I was able to get nearly everything working without compiling up to where the ruby libs needed to be compiled, I did this and it worked fine. There are issues with the default packages where I was receiving this message when trying to use dbi: [RubyODBC]Cannot allocate SQLHENV
apt-get install libiodbc2
— Adam Meehan (2006-11-07)
On Ubuntu you need tdsodbc package as well.
Try installing ‘libiodbc2’ package. I got the same error and this fix it.
— Juan Matias Repetti (2007-05-16)
— John Tjanaka (2007-01-08) \m/(^o^)\m/
Note for Gentoo users:
Remember to USE mssql and odbc, because … that is the whole point ^_^;;;
- echo “dev-db/freetds mssql odbc” >> /etc/portage/package.use
- echo “dev-ruby/ruby-dbi odbc” >> /etc/portage/package.use
- echo “dev-ruby/ruby-odbc ~x86” >> /etc/portage/package.keywords
- emerge -av freetds
- emerge -av ruby-dbi
- emerge -av ruby-odbc
freetds.conf is in /etc.
The
odbc.ini and odbcinst.ini is in /etc/unixODBC, so you must remember to:
export ODBCINI=/etc/unixODBC/odbc.ini export ODBCSYSINI=/etc/unixODBC
While the
libtdsodbc.so and libtdsS.so is in /usr/lib, so in your odbcinst.ini:
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/libtdsodbc.so
Setup = /usr/lib/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1
Tips, it’s possible to connect to SQL Server through SSH Tunneling (Port Forwarding):
- ssh -L localhost_port:sqlserver_ip_or_name:1433 username@gateway_ip_or_name
Need to add a line in
/etc/ssh/ssh_config to keep connection alive:
ServerAliveInterval 300
Assuming your sqlserver port is using 1433.
In your
freetds.conf:
[YOUR_CONNECTION_DEFINITION_NAME]
host = localhost
port = localhost_port # port you used above.
tds version = 8.0 # assuming you're using SQL Server 200x.
SQL Server 2005 and Ubuntu
SQL Server 2005 requires FreeTDS 0.64 (see http://lists.ibiblio.org/pipermail/freetds/2006q4/020738.html)
Ubuntu comes with FreeTDS 0.63 so you can’t apt-get it, you have to install it manually
category: Howto
* Has anyone been able to use this with MSSQl Sevrer 2005 on x86_64 ? * Yes it works for me just fine, as long as you remember to tel Sqlserver to allow untrusted connections.Writing UTF-8 to MS Sql Server
I have been able to write to the Sql Server with the freetds and unixodbc setup as above, but when I tried to write chinese words in UTF-8 it became a row of ??.
The same thing applies to data writes in php, but a solution exists in the form of adding a N before the data in the sql query, for example
insert into mytable values(N’unicodedata’)
The N prefix ensures that Sql Server treats the data as unicode – based on information on other forums.
Does any solution exist for ROR, since we are not working directly with SQL queries? Will using the win32ole setup prevent this problem? But my impression is that win32ole only exist on windows based systems.
Could not SQLConnect
If you get these errors when using the isql-command:
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[ISQL]ERROR: Could not SQLConnect
Try to change the server = YOUR_DB_DEFINITION_NAME
to:
server = IP_OF_SERVER
in odbc.ini. Then try to issue the isql-command again.
——
Had a lot of trouble connecting via ruby even though there was no problem with sqsh etc. Kept giving me this output:
DBI::DatabaseError: 01000 (0) [unixODBC][Driver Manager]Can't open lib '/usr/lib/libtdsodbc.so' : /usr/lib/libtdsodbc.so: cannot open shared object file: No such file or directory
from /usr/local/lib/site_ruby/1.8/DBD/ODBC/ODBC.rb:95:in `connect'
from /usr/local/lib/site_ruby/1.8/dbi/dbi.rb:584:in `connect'
from /usr/local/lib/site_ruby/1.8/dbi/dbi.rb:384:in `connect'
from (irb):2
Finally found out that the linked file in /usr/lib was missing. One can check by executing this:
ls --context /usr/lib/libtdsodbc.so
Found the solution here:
https://bugs.launchpad.net/ubuntu/source/freetds/bug/68239
Basically, install the tdsodbc package, delete the symbolic link /usr/lib/libtdsodbc.so, then re-link with /usr/lib/odbc/libtdsodbc.so and re-test.
—Red
——-
On my debian etch box ruby-odbc segfault with unixodbc, windows 2003 server and SQL Server 2005.
I have switched to iodbc:
aptitude install libiodbc2-dev iodbc
compile freetds from sources (0.64)
./configure --with-tdsver=8.0 --with-iodbc
make
make install
freetds.conf is in /usr/local/etc/freetds.conf
now everything works like a charm
Furthermore, I write my database.yml like this:
development: adapter: sqlserver mode: odbc dsn: "ODBC;Driver={FreeTDS};Server=my_server;Database=my_base;Uid=my_login;Pwd=my_password;Language=English"
thus it’s not needed to bother with odbc.ini, and on MS-Windows I just need to put “SQL Native Client” instead of “FreeTDS” (I think we can also have something more dynamic using an environment variable for this)
“Language=English” resolves problems with datetime format on a French MS-Windows Server
—Laurent
——
I’ve noticed some funny behaviour of tsql during installation/test. If I try the following line in my ubuntu machine:
#tsql -H database_server_ip -p port -U user
but it works perfectly when using the same parameters in the freetds.config file, calling
#tsql -S YOUR_DB_DEFINITION_NAME -U user
don’t ask me Why.This has made me lose more than an hour, hope it helps anyone.
—Carlos