TinyTds::Error: Procedure expects parameter

Question

Given the following ruby:

    sql2 = 'SELECT (CASE WHEN freq = 0 THEN \'pass\' ELSE \'fail\' END) FROM ( 
SELECT COUNT(*) freq FROM Grant_Award WHERE Grant_Award.org_ticker IS NULL )x;'
    pp ActiveRecord::Base.connection.select_all(sql2)

    sql1 = "SELECT (CASE WHEN freq = 0 THEN 'pass' ELSE 'fail' END) FROM ( SELECT COUNT(*) freq FROM Grant_Award WHERE Grant_Award.org_ticker IS NULL )x;"
    pp ActiveRecord::Base.connection.select_all(sql1)

    sql0 = File.readlines(File.join(BASE,'db','scripts','test','pass_or_fail.sql'))
    sql = (sql0.map {|l| l.strip}).join(' ')
    pp sql
    pp ActiveRecord::Base.connection.select_all(sql)

The above produces the following results:

#<ActiveRecord::Result:0x38b0ce0
 @column_types={},
 @columns=[""],
 @hash_rows=nil,
 @rows=[["pass"]]>

#<ActiveRecord::Result:0x38b5ae0
 @column_types={},
 @columns=[""],
 @hash_rows=nil,
 @rows=[["pass"]]>

"SELECT (CASE WHEN freq = 0 THEN 'pass' ELSE 'fail' END) FROM ( SELECT COUNT(*) freq FROM Grant_Award WHERE Grant_Award.org_ticker IS NULL )x;"

C:/Ruby193/lib/ruby/gems/1.9.1/gems/activerecord-sqlserver-adapter-4.2.2/lib/active_record/connection_adapters/sqlserver/database_statements.rb:336:in `each': TinyTds::Error: Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.: EXEC sp_executesql 'SELECT (CASE WHEN freq = 0 THEN ''pass'' ELSE ''fail'' END) FROM ( SELECT COUNT(*) freq FROM Grant_Award WHERE Grant_Award.org_ticker IS NULL )x;' (ActiveRecord::StatementInvalid)

The first two attempts at executing the sql return just fine. The third one, which is read from a file into an array and then joined, bombs. I know this must have something to do with the treatment of strings. But I cannot figure this out.

What am I doing wrong? Why does it (tinytds or activerecord?) think this is a stored procedure?

What I want to do is have a whole bunch of sql scripts in a folder and then execute them one by one.

Details: ruby 1.9.3, activerecord (4.2.0), and tiny_tds (0.6.2-x86-mingw32)

Answer 1

I woke up this morning and realized it must have to do with the character set of the file on disk. The following is a solution works [without changing the query]:

ActiveRecord::Base.connection.select_all(sql0.join(" ").encode("UTF-8"))

I forced the character-set to utf-8 when submitting it, and now everything executes fine.

Proof:

#<ActiveRecord::Result:0x3bede68
 @column_types={},
 @columns=[""],
 @hash_rows=nil,
 @rows=[["pass"]]>

#<ActiveRecord::Result:0x3bf2c68
 @column_types={},
 @columns=[""],
 @hash_rows=nil,
 @rows=[["pass"]]>

"SELECT (CASE WHEN freq = 0 THEN 'pass' ELSE 'fail' END) FROM ( SELECT COUNT(*) freq FROM Grant_Award WHERE Grant_Award.org_ticker IS NULL )x;"

#<ActiveRecord::Result:0x3bf5410
 @column_types={},
 @columns=[""],
 @hash_rows=nil,
 @rows=[["pass"]]>