-
Notifications
You must be signed in to change notification settings - Fork 0
Using TinyTDS
Stop worrying about RubyODBC/unixODBC/iODBC/AnyODBC and do things the easy way!
UPDATE: FreeTDS 0.91 is required for 3.1 and higher versions of the adapter! Find out why!
Detailed information about TinyTDS can be found at the project’s README on github. Pay special attention to the FreeTDS Compatibility & Configuration section for common question on configuring FreeTDS.
Start off by installing the gem. If you are on Windows, we precompile binaries for Ruby 1.8.7 and 1.9.2 for x86-mingw32. The precompiled gem includes the latest static version of FreeTDS too, so no compilation is needed. If you are not on Windows, installing the TinyTDS gem will require that you have FreeTDS installed and available in your PATH.
$ gem install tiny_tds
$ gem install activerecord-sqlserver-adapter
If you are using bundler, just add these to your Gemfile and bundle install.
gem 'tiny_tds'
gem 'activerecord-sqlserver-adapter'
At this time you must manually create a database and assign permissions for a user to connect to that database. These steps are past the scope of this page. We do recommend that you give the user a “db_owner” role for the database with a default schema of “dbo”. This ensures proper permissions for schema changes via migrations. Need help setting up users in SQL Server, try this page :)
Now configure your database.yml file to use the SQL Server adapter. The adapter’s default connection mode is “dblib” and the default port is 1433. So below is a minimal configuration that should meet most needs. If not, move on to the next section, Advanced TinyTDS.
development:
adapter: sqlserver
host: mydb.net
database: myapp_development
username: sa
password: secret
Here are all the configuration options available in database.yml.
development:
adapter: sqlserver
mode: dblib
dataserver: sql2008ds # Name from freetds.conf, host or instance 'localhost\SQLEXPRESS'
host: mydb.net # Used if dataserver is blank.
port: 1433 # Used if host present. Default is 1433.
database: myapp_dev
username: sa
password: secret
timeout: 5000
azure: true
Using SQL Azure requires that FreeTDS be compiled against OpenSSL. Currently the Windows precompiled gems do not include OpenSSL, though we plan on doing so in the future. If you are not on Windows, make sure FreeTDS 0.91 is used as it contains DBLIB patches that allow azure connections to work with TinyTDS. Read our Using Azure page for other details.
Because the SQL Server adapter is used on both Windows and Unix platforms, the legacy rake tasks are likely broken and have no practical way to address tasks common to other databases. The most notable examples are “db:create”, “db:test:clone_structure” and “db:test:purge”. I have no idea how Windows users should address this issue, but here are some solutions from a Unix perspective in the Rails DB Rake Tasks page.
Be sure that the TCP/IP protocol is enabled for the server you’re trying to connect to. If you are using a named instance like “localhost\SQLEXPRESS”, you must use the “dataserver” configuration. However some instances of SQL Server will use dynamically allocated ports (as opposed to statically allocated) meaning connections over port 1433 will not work(unless by chance that happens to be the port that was allocated). To fix this you have 2 options: either change the server’s settings to use static ports(you can manually configure it to use 1433) or find out which port is being used.
To change the server’s settings see this page. There are multiple ways to figure out the port the server is listening on. The easiest is to check the following registry entry:
If this value is not set, double check that TCP/IP is enabled for the server. Once you have the port number you can connect to it with :host and :port options.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP
local = { :username => "username", :password => "password", :host => 'localhost', :port => your_port_number }
Also note that if you’re still having issues, set an environment variable called ‘TDSDUMP’(if it’s not set) to a file on your machine, this will direct the FreeTDS debug logs to that file and you can see what’s going on.
- Make sure you have FreeTDS compiled with iconv support or you will have issue using other encodings.
- If you want to use SQL Azure, make sure to compile with OpenSSL and use 0.91 or higher. Read our Using Azure page for other details.
- If you receive DBDATEREC compile errors, it is because your FreeTDS is compiled with the —enable-msdblib which is a very old option that only specifies a default client build setting. No worries though, the 0.4.2 and up of TinyTDS defines its own client settings and will now always compile correctly no matter what options FreeTDS was configured with.
If you are using TinyTDS 0.4.5 and FreeTDS 0.91, the freetds.conf file can be totally ignored! However, if you are using FreeTDS’s conf file, you should be aware of a few things.
- The freetds.conf file default location can be found by using “tsql -C”. FreeTDS also looks for a conf file in your home directory and in /etc. The precompiled windows binaries have the conf dir set to “C:\Sites”. The placement for this file can be customized using the FREETDS environment variable. This page sums up all of your options.
- Set “tds version” to 7.1 in your 0.91 freetds.conf and 8.0 in your 0.82 freetds.conf file. Basically 7.1/8.0 are the same TDS version but 7.1 is the proper name which changed in the latest 0.91 of FreeTDS. If you think you need 7.1/9.0 of TDS version for proper 2005 and up data types, YOU ARE WRONG! See the “FreeTDS Compatibility & Configuration” section of the TinyTDS README for details. If you do not do this, it is very likely that all your primary key will be wrong! In most case the id column will be assigned a value of 19 for every insert. It is also likely many other things will not work and your god will kill a kitten.
- In a production environment, it is common to run your application process under another user like www or apache. This user will need to find your freetds.conf. Either make sure to put it in a standard place mentioned above or configure a custom environment variable.
- Remove any “client charset” stuff from your freetds.conf file, even if it lines up with the one you want to use. We have reports it can cause issues.
- Take the timeout option out of freetds.conf file. We have found it causes issues there.
- Be sure to specify a :database option. Especially if your user does not have a default database.
- TinyTDS is the first connection mode that correctly uses the :timeout option in database.yml. Double check and make sure it is what you want. Also remember 5000 == 5 seconds. TinyTDS only supports whole number timeouts and does the conversion for you. Remember to take the timeout option out of freetds.conf file. We have found it causes issues there.