No Memory Error on Windows ODBC with nvarchar(max)

[rails-sqlserver]

No Memory Error on Windows ODBC with nvarchar(max)

[metaskills]

Well I'm not running ruby on windows so I need your help. Do you have a solution? Can you configure the windows ODBC driver to have limits for these types? Please investigate this along with running unit tests and let us know.

[Stueck]

I have exactly the same problem.
Running Windows and adapter doesen't work. Server shutsdown with message
"NoMemoryError in AsdfController#new
failed to allocate memory"
Read about possible solution: Set text size = 64512
But how to do that in Rails?

[metaskills]

This might be a bug with ODBC on windows or with SQL Server itself. For instance. I remember a co-worker having this same problem. I think we traced it down to views?

[Stueck]

How can I solve this problem?
Tried ADO, but this doesnt show position after "." in a decimal or money field and utf8 characters.
So I hoped ODBC could solve this.

[metaskills]

Post the full stack trace.

[Stueck]

This is the SQL which causes the error:
Employee Load (271.0ms) SELECT * FROM [tbl_personnel_Employees] WHERE ([tbl_personnel_Employees].[EmployeeNumber] = N'MA0063')

This is the stack trace

NoMemoryError (failed to allocate memory):
c:/ruby/lib/ruby/gems/1.8/gems/dbd-odbc-0.2.4/lib/dbd/odbc/statement.rb:41:in `fetch'
c:/ruby/lib/ruby/gems/1.8/gems/dbd-odbc-0.2.4/lib/dbd/odbc/statement.rb:41:in `fetch'
c:/ruby/lib/ruby/gems/1.8/gems/dbi-0.4.1/lib/dbi/handles/statement.rb:211:in `fetch'
c:/ruby/lib/ruby/gems/1.8/gems/dbi-0.4.1/lib/dbi/handles/statement.rb:240:in `each'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/lib/active_record/connection_adapters/sqlserver_adapter.rb:879:in `inject'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/lib/active_record/connection_adapters/sqlserver_adapter.rb:879:in `handle_as_array'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/lib/active_record/connection_adapters/sqlserver_adapter.rb:865:in `raw_select'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/lib/active_record/connection_adapters/sqlserver_adapter.rb:816:in `select'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all_without_query_cache'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract/query_cache.rb:60:in `select_all'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract/query_cache.rb:81:in `cache_sql'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract/query_cache.rb:60:in `select_all'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/base.rb:661:in `find_by_sql'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/base.rb:1548:in `find_every'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/base.rb:1583:in `find_one'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/base.rb:1569:in `find_from_ids'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/base.rb:616:in `find'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/associations/belongs_to_association.rb:49:in `send'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/associations/belongs_to_association.rb:49:in `find_target'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/associations/association_proxy.rb:240:in `load_target'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/associations/association_proxy.rb:112:in `reload'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/associations.rb:1219:in `employee'
C:/Dokumente und Einstellungen/Stueck/Eigene Dateien/NetBeansProjects/timerecording/app/controllers/time_records_controller.rb:349:in `initialize_departmenthead'
c:/ruby/lib/ruby/gems/1.8/gems/activesupport-2.3.3/lib/active_support/callbacks.rb:178:in `send'
c:/ruby/lib/ruby/gems/1.8/gems/activesupport-2.3.3/lib/active_support/callbacks.rb:178:in `evaluate_method'
c:/ruby/lib/ruby/gems/1.8/gems/activesupport-2.3.3/lib/active_support/callbacks.rb:166:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/filters.rb:225:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/filters.rb:629:in `run_before_filters'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/filters.rb:615:in `call_filters'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/filters.rb:610:in `perform_action_without_benchmark'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/benchmarking.rb:68:in `perform_action_without_rescue'
c:/ruby/lib/ruby/gems/1.8/gems/activesupport-2.3.3/lib/active_support/core_ext/benchmark.rb:17:in `ms'
c:/ruby/lib/ruby/gems/1.8/gems/activesupport-2.3.3/lib/active_support/core_ext/benchmark.rb:10:in `realtime'
c:/ruby/lib/ruby/gems/1.8/gems/activesupport-2.3.3/lib/active_support/core_ext/benchmark.rb:17:in `ms'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/benchmarking.rb:68:in `perform_action_without_rescue'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/rescue.rb:160:in `perform_action_without_flash'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/flash.rb:146:in `perform_action'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/base.rb:527:in `send'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/base.rb:527:in `process_without_filters'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/filters.rb:606:in `process'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/base.rb:391:in `process'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/base.rb:386:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/routing/route_set.rb:434:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/dispatcher.rb:88:in `dispatch'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/dispatcher.rb:111:in `_call'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/dispatcher.rb:82:in `initialize'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/query_cache.rb:29:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/query_cache.rb:29:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract/query_cache.rb:34:in `cache'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/query_cache.rb:9:in `cache'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/query_cache.rb:28:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:361:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/rack-1.0.0/lib/rack/head.rb:9:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/rack-1.0.0/lib/rack/methodoverride.rb:24:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/params_parser.rb:15:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/session/cookie_store.rb:93:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/reloader.rb:29:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/failsafe.rb:26:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/rack-1.0.0/lib/rack/lock.rb:11:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/rack-1.0.0/lib/rack/lock.rb:11:in `synchronize'
c:/ruby/lib/ruby/gems/1.8/gems/rack-1.0.0/lib/rack/lock.rb:11:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.3/lib/action_controller/dispatcher.rb:106:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/rails-2.3.3/lib/rails/rack/static.rb:31:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/rack-1.0.0/lib/rack/urlmap.rb:46:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/rack-1.0.0/lib/rack/urlmap.rb:40:in `each'
c:/ruby/lib/ruby/gems/1.8/gems/rack-1.0.0/lib/rack/urlmap.rb:40:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/rails-2.3.3/lib/rails/rack/log_tailer.rb:17:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/rack-1.0.0/lib/rack/content_length.rb:13:in `call'
c:/ruby/lib/ruby/gems/1.8/gems/rack-1.0.0/lib/rack/handler/webrick.rb:46:in `service'
c:/ruby/lib/ruby/1.8/webrick/httpserver.rb:104:in `service'
c:/ruby/lib/ruby/1.8/webrick/httpserver.rb:65:in `run'
c:/ruby/lib/ruby/1.8/webrick/server.rb:173:in `start_thread'
c:/ruby/lib/ruby/1.8/webrick/server.rb:162:in `start'
c:/ruby/lib/ruby/1.8/webrick/server.rb:162:in `start_thread'
c:/ruby/lib/ruby/1.8/webrick/server.rb:95:in `start'
c:/ruby/lib/ruby/1.8/webrick/server.rb:92:in `each'
c:/ruby/lib/ruby/1.8/webrick/server.rb:92:in `start'
c:/ruby/lib/ruby/1.8/webrick/server.rb:23:in `start'
c:/ruby/lib/ruby/1.8/webrick/server.rb:82:in `start'
c:/ruby/lib/ruby/gems/1.8/gems/rack-1.0.0/lib/rack/handler/webrick.rb:13:in `run'
c:/ruby/lib/ruby/gems/1.8/gems/rails-2.3.3/lib/commands/server.rb:111
c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `require'
script\server:3
c:/ruby/lib/ruby/gems/1.8/gems/ruby-debug-ide-0.3.1/lib/ruby-debug.rb:96:in `debug_load'
c:/ruby/lib/ruby/gems/1.8/gems/ruby-debug-ide-0.3.1/lib/ruby-debug.rb:96:in `main'
c:/ruby/lib/ruby/gems/1.8/gems/ruby-debug-ide-0.3.1/bin/rdebug-ide:76
C:/ruby/bin/rdebug-ide:19:in `load'
C:/ruby/bin/rdebug-ide:19

[metaskills]

Yup... this is an issue with DBI's DBD::ODBC or Microsoft's ODBC drivers. I'd start googling around for solutions in those areas and/or bring this up with the authors of DBI. I would suggest that you start looking for things in SQL Server that may be able to be toggled. I may be able to help you more next week... but SQL Server and the MS stuff is huge and I'm sure there is a solution to your problem, but it is not within the adapter at this time.

[Stueck]

Hm okay, thank you.
Whats with "set text size xxxxx"? May this help? Have no idea how to set and try it.
I will google a bit more.
Would be very happy for further help and suggestions.

[metaskills]

Well most people that use this adapter, like myself, are on UNIX bases systems and hence use FreeTDS behind ODBC to get the SQL Server. There is a conf file for FreeTDS and it has this in it.

# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.  
# Try setting 'text size' to a more reasonable limit 
text size = 64512

If that is any help, I'm not sure. But I'd start getting abstract with your debugging. I've found some very obscure settings when dealing with SQL Server and I'm sure there is either a Windows ODBC or SQL Server "server" configuration issue that can solve your problem. For instance, have you tried googling and reading MSDN's and/or MS's books online on ODBC related issues/configurations?

[Anonzmous]

Have exactly the same problem.
Is there ANY solution to get an ODBC connection on windows to a ms sql server database?

[metaskills]

My coworker had this issue even in Rails 1.2.6 with the old SQL Server adapter. It has something to do with windows and nothing with the adapter or the DBD::ODBC that I know if. Someone from the Windows user base needs to step up and debug this as it is OLD OLD OLD issue.

[phoenix]

I try this and pass it(SQL2005):

class Test1 < ActiveRecord::Base
end

Test1.find(1)
=>NoMemoryError: failed to allocate memory

Test1.connection.execute('SET TEXTSIZE 102400');
Test1.find(1)
=>Test1 Load (31.0ms)

and there is nvarchar(MAX),ntext,text in my test table.
The nvarchar(MAX) column can not display right,it is 'abc',but show me "\000_\206" in the rails console.

[metaskills]

Somebody on windows will have to step up and contribute a patch?

[KDGundermann]

@phoenix: Confirmed:

connection.execute('SET TEXTSIZE 102400') solved the problem
Tested On: Ruby 1.9.1 on Rails 2.3.5 on Windows XP
Using

• activerecord-sqlserver-adapter v. 2.3.4
• ruby-odbc v. 0.9999

[jon4isu]

so this is a hack and not a real solution but if you are in a pinch and need this to work...

at line 1036 of sqlserver_adapter.rb, add SET TEXTSIZE 102400

  sql = %{
      SET TEXTSIZE 102400
      SELECT
      columns.TABLE_NAME as table_name,
      .......