Incorrect syntax near '-'.: EXEC sp_helptext sd-portal-person #73

Closed
petRUShka opened this Issue Dec 29, 2010 · 2 comments

2 participants

@petRUShka

I have helpdesk_person model (SQL server table):

class HelpdeskPerson < ActiveRecord::Base
  set_table_name "sd-portal-person"
end

I tried to execute HelpdeskPerson.first.

If I use activerecord-sqlserver-adapter version 1.0.0 it is ok. Gem generates such SQL code:

 SELECT 
   cols.COLUMN_NAME as ColName, 
   cols.COLUMN_DEFAULT as DefaultValue,
   cols.NUMERIC_SCALE as numeric_scale,
   cols.NUMERIC_PRECISION as numeric_precision, 
   cols.DATA_TYPE as ColType, 
   cols.IS_NULLABLE As IsNullable, 
   COL_LENGTH(cols.TABLE_NAME, cols.COLUMN_NAME) as Length, 
   COLUMNPROPERTY(OBJECT_ID(cols.TABLE_NAME), cols.COLUMN_NAME, 'IsIdentity') as IsIdentity, 
   cols.NUMERIC_SCALE as Scale 
   FROM INFORMATION_SCHEMA.COLUMNS cols 
   WHERE cols.TABLE_NAME = 'sd-portal-person' 
 SELECT TOP 1 * FROM [sd-portal-person] 

But if I try to upgrade to activerecord-sqlserver-adapter version 2.3.13 it fails:

ruby-1.8.7-p302 :002 > HelpdeskPerson.first

ActiveRecord::StatementInvalid: ODBC::Error: 37000 (102) [FreeTDS][SQL Server]Incorrect syntax near '-'.: EXEC sp_helptext sd-portal-person
from /home/user/.rvm/gems/ruby-1.8.7-p302/gems/activerecord-2.3.10/lib/active_record/connection_adapters/abstract_adapter.rb:227:in log'
from /home/user/.rvm/gems/ruby-1.8.7-p302/gems/activerecord-sqlserver-adapter-2.3.13/lib/active_record/connection_adapters/sqlserver_adapter.rb:982:in
raw_select'
from /home/user/.rvm/gems/ruby-1.8.7-p302/gems/activerecord-sqlserver-adapter-2.3.13/lib/active_record/connection_adapters/sqlserver_adapter.rb:395:in select_rows'
from /home/user/.rvm/gems/ruby-1.8.7-p302/gems/activerecord-2.3.10/lib/active_record/connection_adapters/abstract/database_statements.rb:27:in
select_values'
from /home/user/.rvm/gems/ruby-1.8.7-p302/gems/activerecord-sqlserver-adapter-2.3.13/lib/active_record/connection_adapters/sqlserver_adapter.rb:596:in view_information'
from /home/user/.rvm/gems/ruby-1.8.7-p302/gems/activerecord-sqlserver-adapter-2.3.13/lib/active_record/connection_adapters/sqlserver_adapter.rb:972:in
info_schema_query'
from /home/user/.rvm/gems/ruby-1.8.7-p302/gems/activerecord-2.3.10/lib/active_record/base.rb:1482:in silence'
from /home/user/.rvm/gems/ruby-1.8.7-p302/gems/activerecord-sqlserver-adapter-2.3.13/lib/active_record/connection_adapters/sqlserver_adapter.rb:972:in
info_schema_query'
from /home/user/.rvm/gems/ruby-1.8.7-p302/gems/activerecord-sqlserver-adapter-2.3.13/lib/active_record/connection_adapters/sqlserver_adapter.rb:596:in view_information'
from /home/user/.rvm/gems/ruby-1.8.7-p302/gems/activerecord-sqlserver-adapter-2.3.13/lib/active_record/connection_adapters/sqlserver_adapter.rb:604:in
view_table_name'
from /home/user/.rvm/gems/ruby-1.8.7-p302/gems/activerecord-sqlserver-adapter-2.3.13/lib/active_record/connection_adapters/sqlserver_adapter.rb:1193:in table_name_or_views_table_name'
from /home/user/.rvm/gems/ruby-1.8.7-p302/gems/activerecord-sqlserver-adapter-2.3.13/lib/active_record/connection_adapters/sqlserver_adapter.rb:1335:in
column_definitions'
from /home/user/.rvm/gems/ruby-1.8.7-p302/gems/activerecord-sqlserver-adapter-2.3.13/lib/active_record/connection_adapters/sqlserver_adapter.rb:1322:in collect'
from /home/user/.rvm/gems/ruby-1.8.7-p302/gems/activerecord-sqlserver-adapter-2.3.13/lib/active_record/connection_adapters/sqlserver_adapter.rb:1322:in
column_definitions'
from /home/user/.rvm/gems/ruby-1.8.7-p302/gems/activerecord-sqlserver-adapter-2.3.13/lib/active_record/connection_adapters/sqlserver_adapter.rb:634:in columns'
from /home/user/.rvm/gems/ruby-1.8.7-p302/gems/activerecord-2.3.10/lib/active_record/base.rb:1305:in
columns'
from /home/user/.rvm/gems/ruby-1.8.7-p302/gems/activerecord-2.3.10/lib/active_record/base.rb:1318:in column_names'
from /home/user/.rvm/gems/ruby-1.8.7-p302/gems/activerecord-2.3.10/lib/active_record/base.rb:2899:in
inspect'
from /home/user/.rvm/rubies/ruby-1.8.7-p302/lib/ruby/1.8/irb.rb:310:in output_value'
from /home/user/.rvm/rubies/ruby-1.8.7-p302/lib/ruby/1.8/irb.rb:159:in
eval_input'
from /home/user/.rvm/rubies/ruby-1.8.7-p302/lib/ruby/1.8/irb.rb:271:in signal_status'
from /home/user/.rvm/rubies/ruby-1.8.7-p302/lib/ruby/1.8/irb.rb:155:in
eval_input'
from /home/user/.rvm/rubies/ruby-1.8.7-p302/lib/ruby/1.8/irb.rb:154:in eval_input'
from /home/user/.rvm/rubies/ruby-1.8.7-p302/lib/ruby/1.8/irb.rb:71:in
start'
from /home/user/.rvm/rubies/ruby-1.8.7-p302/lib/ruby/1.8/irb.rb:70:in catch'
from /home/user/.rvm/rubies/ruby-1.8.7-p302/lib/ruby/1.8/irb.rb:70:in
start'
from /home/user/.rvm/rubies/ruby-1.8.7-p302/bin/irb:17ruby-1.8.7-p302 :003 >

Ruby 1.8.7, Rails 2.3.10, SQL server 2005.

@metaskills
Collaborator

I committed this in both 2.x/3.x branches.
a466244

I believe your table is a view and even though this will fix your direct problem, you will run into more. I suggest altering the view and making the table name have _'s vs -'s so that you can side step further issues.

I'll release updated gems shortly with this change.

@petRUShka

Yes, you are right -- it is view.

Unfortunately I can't change name of view.

I tried to use sqlserver-adapter from git with your patch. That fix old error and create new one:

ODBC::Error: 37000 (15197) [FreeTDS][SQL Server]There is no text for object 'sd-portal-person'.: EXEC sp_helptext [sd-portal-person]

P.S. I created #75 for this error.

@mlwyatt mlwyatt pushed a commit to mlwyatt/activerecord-sqlserver-adapter that referenced this issue May 27, 2016
@metaskills metaskills Properly quote table names when reflecting on views. Issue #73 a466244
@mlwyatt mlwyatt pushed a commit to mlwyatt/activerecord-sqlserver-adapter that referenced this issue May 27, 2016
@metaskills metaskills Warn of possible permission problems if "EXEC sp_helptext..." does no…
…t work view. Fixes #73.
6b84a1f
This issue was closed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment