TinyTds::Error: Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

[guai]

version is 3.1.3 code throwing exception:

ActiveRecord::Base.connection.select_value "DECLARE @EncGUID nvarchar(22); EXEC [dbo].[get_guid] @EncGUID = @EncGUID OUTPUT; SELECT @EncGUID;"

Fixed it for me with foolowing line after line #290 in database_statements.rb. But didnt test it.

statement = 'N'+statement unless statement.start_with? 'N'

[metaskills]

We should find the root problem. This line here should be quoting the statement with a N'...' prefix.

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/lib/active_record/connection_adapters/sqlserver/database_statements.rb#L270

It should get caught by this statement here.

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/lib/active_record/connection_adapters/sqlserver/quoting.rb#L16

Perhaps it is not a UTF-8 string or your ruby encoding is not set properly?

[bhunsaker]

Probably a different issue, but the same error. In this case the "EXEC sp_executesql 'UPDATE...' should have an "N" before the quote (IMHO).

The SQL Server "Server Collation" is "SQL_Latin1_General_CP1_CI_AS" The database collation is the same.

---

require 'rubygems'
require 'active_record'

ActiveRecord::Base.establish_connection(
    :adapter => 'sqlserver',
    :mode => 'dblib',
    :dataserver => 'localhost\SQLExpress',
    :username => 'sa',
    :password => 'password',
    :database => 'db_test',
    :timeout => 10000,
    :encoding => 'latin1'
    )

table_name = Time.now.strftime( "test_%H_%M_%S" )
class_name = table_name.classify
field_name = 'text'

ActiveRecord::Migration.class_eval do
    create_table table_name
    add_column table_name, field_name, 'string'
end

the_class = Object.const_set( class_name, Class.new( ActiveRecord::Base ) { set_table_name table_name } )
new_record = Object.const_get( class_name ).new

# Works

new_record[ field_name ] = 130.chr
new_record.save!

find_record = Object.const_get( class_name ).find( :first )
puts find_record.inspect

# Fails

find_record[ field_name ] = 131.chr
find_record.save!

exit

---

-- create_table("test_14_58_19")
   -> 1.5756s
-- add_column("test_14_58_19", "text", "string")
   -> 0.0000s
#<Test145819 id: 1, text: "\x82">
C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-sqlserver-adapter-3.1.3/lib/active_record/connection_adapters/sqlserver/database_statements.rb:353:in `each': TinyTds::Error: Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.: EXEC sp_executesql 'UPDATE [test_14_58_19] SET [text] = N''â'' WHERE [test_14_58_19].[id] = 1; SELECT @@ROWCOUNT AS AffectedRows'(ActiveRecord::StatementInvalid)
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-sqlserver-adapter-3.1.3/lib/active_record/connection_adapters/sqlserver/database_statements.rb:353:in `handle_to_names_and_values_dblib'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-sqlserver-adapter-3.1.3/lib/active_record/connection_adapters/sqlserver/database_statements.rb:342:in `handle_to_names_and_values'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-sqlserver-adapter-3.1.3/lib/active_record/connection_adapters/sqlserver/database_statements.rb:313:in `block in raw_select'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/connection_adapters/abstract_adapter.rb:244:in `block in log'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activesupport-3.1.1/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/connection_adapters/abstract_adapter.rb:239:in `log'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-sqlserver-adapter-3.1.3/lib/active_record/connection_adapters/sqlserver/database_statements.rb:310:in `raw_select'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-sqlserver-adapter-3.1.3/lib/active_record/connection_adapters/sqlserver/database_statements.rb:293:in `do_exec_query'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-sqlserver-adapter-3.1.3/lib/active_record/connection_adapters/sqlserver/database_statements.rb:22:in `exec_query'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/connection_adapters/abstract/database_statements.rb:77:in `exec_update'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-sqlserver-adapter-3.1.3/lib/active_record/connection_adapters/sqlserver/database_statements.rb:37:in `exec_update'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/connection_adapters/abstract/database_statements.rb:96:in `update'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/connection_adapters/abstract/query_cache.rb:14:in `update'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/persistence.rb:305:in `update'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/locking/optimistic.rb:84:in `update'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/attribute_methods/dirty.rb:74:in `update'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/timestamp.rb:64:in `update'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/callbacks.rb:272:in `block in update'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activesupport-3.1.1/lib/active_support/callbacks.rb:390:in `_run_update_callbacks'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activesupport-3.1.1/lib/active_support/callbacks.rb:81:in `run_callbacks'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/callbacks.rb:272:in `update'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/persistence.rb:294:in `create_or_update'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/callbacks.rb:264:in `block in create_or_update'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activesupport-3.1.1/lib/active_support/callbacks.rb:390:in `_run_save_callbacks'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activesupport-3.1.1/lib/active_support/callbacks.rb:81:in `run_callbacks'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/callbacks.rb:264:in `create_or_update'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/persistence.rb:57:in `save!'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/validations.rb:56:in `save!'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/attribute_methods/dirty.rb:33:in `save!'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/transactions.rb:246:in `block in save!'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/transactions.rb:295:in `block in with_transaction_returning_status'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/connection_adapters/abstract/database_statements.rb:192:in `transaction'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/transactions.rb:208:in `transaction'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/transactions.rb:293:in `with_transaction_returning_status'
        from C:/RailsInstaller/Ruby1.9.2/lib/ruby/gems/1.9.1/gems/activerecord-3.1.1/lib/active_record/transactions.rb:246:in `save!'
        from test8bit.rb:36:in `<main>'

[metaskills]

This is most certainly related to your "ruby" encoding not being set correctly. Also in the 3.2 versions of the adapter, no matter what, we N'' prefix. So should be moot there too. But in general, this is a local ruby encoding issue. Google for how to set that.

[bhunsaker]

no matter what, we N'' prefix

Based upon the error text in my post, I would say that the adapter does not always prefix with "N". Somewhere in that call stack it generated the following:

EXEC sp_executesql 'UPDATE [test_14_58_19] SET [text] = N''â'' WHERE [test_14_58_19].[id] = 1; SELECT @@ROWCOUNT AS AffectedRows'

The SQL statement to execute has the "N", but the string passed to sp_executesql does not.

Adding a # encoding: UTF-8 to the start of my test script did not change the error.

[metaskills]

I do not believe that encoding declaration does what you think it does. Did you try the other methods?

[metaskills]

Remember, you are trying to pass this test. https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/lib/active_record/connection_adapters/sqlserver/quoting.rb#L16

Read James Edward Grey's encoding stuff. Maybe Yehuda Katz work too.