TinyTDS syntax error (where infact there is no error in its generated SQL syntax)

[ata-n]

I am using TinyTDS to connect to a remote SQL Server (2005), everything works fine except when I do an UPDATE.
Actually the UPDATE works fine too, but it raises an exception regardless. I can see the updated field when I view the record, but right after the update is finished I get the following exception :

ActiveRecord::StatementInvalid (TinyTds::Error: Incorrect syntax near the keyword 'and'.: UPDATE [surveys] SET [Other CD Type] = N'' WHERE [surveys].[id] = 24):

TOP OF THE TRACE

activerecord (3.0.9) lib/active_record/connection_adapters/abstract_adapter.rb:207:in log' activerecord-sqlserver-adapter (3.0.15) lib/active_record/connection_adapters/sqlserver/database_statements.rb:228:indo_execute'
activerecord-sqlserver-adapter (3.0.15) lib/active_record/connection_adapters/sqlserver/database_statements.rb:19:in execute' activerecord-sqlserver-adapter (3.0.15) lib/active_record/connection_adapters/sqlserver/database_statements.rb:211:inupdate_sql'
activerecord (3.0.9) lib/active_record/connection_adapters/abstract/database_statements.rb:49:in update' activerecord (3.0.9) lib/active_record/connection_adapters/abstract/query_cache.rb:16:inupdate'
arel (2.0.10) lib/arel/crud.rb:20:in update' activerecord (3.0.9) lib/active_record/persistence.rb:259:inupdate'

I can easily verify that there is no syntax error, and in fact it does update the required field in the desired record; however this exception is thrown anyway. I tried disabling activerecord logging but no help. The exception is telling me about a keyword that doesn't even exist in the generated SQL statement.

Thanks very much in advance,

Specs

Platform: Mac OS X Snow Leopard
Ruby 1.8.7
Rails 3.0
freetds 0.8.2
SQL Server 2005 is remote on Windows 2003 Server

[metaskills]

What happens you run the statement in SMS, etc? Perhaps this is a trigger with a bad syntax?

[ata-n]

SMS works fine,..tried through isql and that was fine too...no triggers or audits on the database either.

[metaskills]

Try this in the rails console. This will just work with the TinyTDS client.

client = ActiveRecord::Base.connection.raw_connection
client.execute("UPDATE [surveys] SET [Other CD Type] = N'' WHERE [surveys].[id] = 24").do

[ata-n]

I get the same error :(

TinyTds::Error: Incorrect syntax near the keyword 'and'.
from (irb):2:in `do'
from (irb):2

[metaskills]

Well, that's good. This means we have a quicker route to debug and area of isolation. Can you rescue that error and give me some more information about it. The error object has these attributes.

https://github.com/rails-sqlserver/tiny_tds/blob/master/lib/tiny_tds/error.rb#L18

Can you show me their values?

[ata-n]

This is what I get:

e.source => "message"
e.severity => 15
e.db_error_number => 156
e.os_error_number => 1

[metaskills]

Are you using the latest 0.5.0 in master?

[metaskills]

Also, are you sure there is nothing else being executed as a result of that statement? The reason I ask about the 0.5.0, is that it has a new functionality to work with sybase and only raises an exception in our message handler if the severity is greater than 10. This exception you are getting is technically coming from the message handler and it has a severity level of 15, we have a test for this too.

should 'error gracefully with invalid syntax' do
  action = lambda { @client.execute('this will not work').each }
  assert_raise_tinytds_error(action) do |e|
    assert_match %r|incorrect syntax|i, e.message
    assert_equal 15, e.severity
    assert_equal 156, e.db_error_number
  end
  assert_followup_query
end

So in short, this technically really should not have anything to do with the 0.5.0 version but is just universal, a bad statement is a bad statement. But you have got something generating a statement in your batch. There is not a lot that can go wrong here, I am almost stumped with a big WTF?

So more info, are you crazy configs for the connection, tds version, encoding, anything out of the norm? What version of TinyTDS are you using? Are you using :dataserver config option along with a freetds.conf file?

[ata-n]

I managed to resolve this issue and it was completely my miss!! (Oops!)
Turns out the [surveys] object was actually a view in SQL Server which contained some not so friendly SQL code..
All works well now , terribly sorry for the waist of time!! feel pretty stupid !!

TinyTDS rocks and thank you for writing it!

[metaskills]

Right on!