Failures In sp_executesql N'...' Kill The Connection

So I may have touched on this issue over the weekend. I noticed that failures in sp_executesql for the 3.1 adapter would not allow the connection to be reused under FreeTDS 0.82. I wrote this test https://github.com/rails-sqlserver/tiny_tds/blob/master/test/result_test.rb#L600 this weekend and it fails only under 0.82. Under 0.91 it works just fine.

should 'error gracefully with incorrect syntax in sp_executesql' do
  if @client.freetds_091_or_higer?
    action = lambda { @client.execute("EXEC sp_executesql N'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
  else
    skip 'FreeTDS 0.91 and higher can only pass this test.'
  end
end

I have also noticed that if I use 0.82 tsql command line utility, that it can cope, but tsql is a mixed bag of code that might not be under the same constraints of the DBLIB interface we are under.

1> EXEC sp_executesql N'this will not work'
2> GO
Msg 156, Level 15, State 1, Server SQLSERVER08, Line 1
Incorrect syntax near the keyword 'not'.
(return status = 156)
1> SELECT 1 AS [one]
2> GO
one
1

So, I'd like to play around with this a bit more to see if we can make TinyTDS cope a bit better. A few thoughts.

1) We do a lot of work to make sure a bad command batch calls both dbsqlok() and dbcancel() C functions. We do this in the exception handler because it is highly possible that no handle will be returned.

2) Can anyone confirm that this issue is related to any other, especially in regards to stored procedure failures? As of 8/18/11, FreeTDS 0.91 has been released. So this may just be what is needed, and I am cool with that.

rails-sqlserver / tiny_tds

Failures In sp_executesql N'...' Kill The Connection #41