TinyTDS Error - unicode, ntext

[dev2]

I just installed TinyTDS 0.5.1 in my rails 3.2.6 app.  Ruby 1.9.3, SQL Server 2008 R2

I'm getting the following error:

ActiveRecord::StatementInvalid (TinyTds::Error: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.: EXEC sp_executesql N'SELECT TOP (1) ....

Do I need to change the datatype of all ntext columns?  Is there a known solution/workaround for this?

[Ken Collins]

I've never see this before. It should not matter, what data type are they now?

 - Ken

[dev2]

Thank you for the lightning fast reply.

The current type is nvarchar(MAX) 

When I changed it to varchar(MAX) the error went away.

I was using rubyodbc before I setup TinyTDS.  I expected to see a performance improvement (I think was correct in thinking TinyTDS should improve performance of database calls) when I took out rubyodbc and added in tiny_tds, however performance is virtually identical, so perhaps I have something misconfigured?

[Ken Collins]

> The current type is nvarchar(MAX) When I changed it to varchar(MAX) the error went away.

Crazy, that should not matter at all. What version of FreeTDS are you using and how (if any) is your freetds.conf file setup? BTW, I have test for all these datatypes and I have never seen this in testing or production use. Especially one with mixed data types.

https://github.com/rails-sqlserver/tiny_tds/blob/master/test/schema/sqlserver_2008.sql

> I was using rubyodbc before I setup TinyTDS. I expected to see a performance improvement (I think was correct in thinking TinyTDS should improve performance of database calls) when I took out rubyodbc and added in tiny_tds, however performance is virtually identical, so perhaps I have something misconfigured?

Depends on how you are measuring, the speed improvement is really love level and is significant on char types last time I measured.

https://github.com/rails-sqlserver/tiny_tds/tree/master/test/benchmark


- Ken

[dev2]

It was my understanding that TinyTDS should handle all datatypes, which is part of what makes me thing I have done something incorrectly.  I actually removed freetds.conf to make sure it wasn't being used. 

Here is the output of tsql -C

Compile-time settings (established with the "configure" script)
                            Version: freetds v0.82
             freetds.conf directory: /etc/freetds
     MS db-lib source compatibility: no
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 4.2
                              iODBC: no
                           unixodbc: yes

I benchmarked at a high level which was to have a script load a specific, database heavy page 100 times and get the average time for a request to complete.




Thank you so much for your assistance!

[Ken Collins]

Version: freetds v0.82

You need to change that, well over 5 years old. It is not supported with the latest adapter techniques. 

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter#whats-new

I would use 0.9.1, if you want, you can build your own native TinyTDS gem with FreeTDS bundled into the gem using our project. Details here.

https://github.com/rails-sqlserver/tiny_tds#using-miniportile

 - Ken

[dev2]

Thank you!

Those two steps did the trick.  Thanks so much for your help.