SQL Server with FreeTDS Unicode Data Error

Update (Aug. 28, 2011): You may want to check out tiny_tds which obviates many of the below settings and gives you a richer and more scalable connection than ODBC. If you get

when connecting to SQL Server from Linux via FreeTDS, you don't set the FreeTDS version correctly. To use SQL Server from Rails, I usually just create shared DSNs in /etc/odbc.ini (Ubuntu) with connection information that looks like so:

Note the syntax: It must be Server, NOT Servername Update: this only applies to the odbc.ini setup used here, if you need to read client charset = UTF-8 from freetds.conf, it has to be Servername, see update below and TDS_Version has an underscore. The standard ODBCConfig visual tool will usually set “Servername” instead of “Server” and this will not work. The above settings should just work (provided FreeTDS is installed correctly) with the ActiveRecord SQL Server adapter and these settings in database.yml:

Also be sure to read general instruction on how to get Linux clients talk to SQL Server, as well as the rails setup and connectivity for different clients. Update: Turns out I still got errors such as

when using the “odbc.ini”-only setup outlined above. It may be necessary to have the ODBC driver read the

from the /etc/freetds/freetds.conf file. And it can be set only there, not in the odbc.ini definition. SO for example if you have this definition in your freetds.conf:

the odbc.ini above has to be changed to:

Note that Server = has to be changed to Servername =. Hope it helps.

Ken Collins: Switching to a dblib connection mode which uses TinyTDS for the SQL Server Adapter alleviates this problem since it can do all the correct encoding.

dirk: @Ken: Thanks for your comment. I've updated the post after finding that tiny_tds is clearly better than ODBC.

Jones sabo well all done latter generations: I conceive this website holds some rattling good information for everyone. "The fewer the words, the better the prayer." by Martin Luther.

Posted: 18 September 2010

comments powered by Disqus