"Attempt to initiate a new Adaptive Server operation with results pending" when calling stored procedure

[omagid-crp]

Environment

Operating System

Distributor ID: Ubuntu
Description:    Ubuntu 14.04.5 LTS
Release:    14.04

TinyTDS Version and Information The ttds-tsql command is not found, even though we're using TinyTds v.2.1.2. tsql -C outputs:

[TinyTds][v2.1.2][tsql]: /usr/local/bin/tsql
Compile-time settings (established with the "configure" script)
                            Version: freetds v1.1.21
             freetds.conf directory: /usr/local/etc
     MS db-lib source compatibility: no
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 7.4
                              iODBC: no
                           unixodbc: no
              SSPI "trusted" logins: no
                           Kerberos: no
                            OpenSSL: yes
                             GnuTLS: no
                               MARS: yes

FreeTDS Version Version: freetds v1.1.21

Description

We use TinyTds to call stored procedures from Rails. Lately, we've been getting a lot of these errors: "Attempt to initiate a new Adaptive Server operation with results pending." I've looked through past issues and scoured the internet, but I can't seem to diagnose the problem. Our implementation of Passenger isn't multi-threaded, so that shouldn't be an issue.

My latest best guess is that our procedure_results method starts by checking if the client is either nil or bad, and only if one or the other is the case does it open a new connection. But that would seem to allow for the possibility that the client is not nil, and not bad, but occupied waiting on pending results. Here's how the method starts:

if @client.nil? or !@client.active?
    if @client
       @client.close
     end

    @client = new_connection
end

Am I right that this is problematic? If not, any other ideas?

Here are our "new_connection" and "procedure_results" methods in their entirety:

def self.new_connection(opts = {})
    host     = opts.fetch(:host, ENV.fetch("MS_SQL_SERVER_HOST"))
    port     = opts.fetch(:port, ENV.fetch("MS_SQL_SERVER_PORT"))
    username = opts.fetch(:username, ENV.fetch("MS_SQL_SERVER_USERNAME"))
    password = opts.fetch(:password, ENV.fetch("MS_SQL_SERVER_PASSWORD"))
    database = opts.fetch(:database, ENV.fetch("MS_SQL_SERVER_DATABASE"))

    begin
      return TinyTds::Client.new(
        username: username,
        password: password,
        host: host,
        port: port,
        database: database,
        timeout: 10,
        tds_version: "7.4"
      )
    rescue
      return TinyTds::Client.new(
        username: username,
        password: password,
        host: host,
        port: port,
        database: database,
        timeout: 10,
        tds_version: "7.4"
      )
    end
  end

def self.procedure_results(procedure, options = { format: nil })
    # open a new connection
    if @client.nil? or !@client.active?
      if @client
        @client.close
      end

      @client = new_connection
    end

    procedure = procedure.encode('UTF-8', 'binary', invalid: :replace, undef: :replace, replace: '')
    # get results
    result = @client.execute("EXEC #{procedure}")
    case options[:format]
    when :resultset
      return result
    when :hash
      results = result.each
    when :csv
      require 'csv'

      results = CSV.generate(headers: true) do |csv|

        csv << result.fields
        result.each do |row|
          csv << result.fields.map{ |field| row[field] }
        end
      end
    else
      results = result.map { |row| OpenStruct.new(row) }
    end
    result.cancel

    # close connection
    @client.close

    return results
  end

[aharpervc]

In the case of options[:format] == :resultset, you're not explicitly iterating the result object like you are with the other conditions. That is possibly one source of the problem. Try this

    case options[:format]
    when :resultset
+      # or, return result.do
+      return result.to_a
-      return result
    when :hash
      results = result.each

[omagid-crp]

Thanks for your comment. We actually don't use that option anywhere in our codebase. I probably should have mentioned that or just left it out. 99% of the calls to this method use the option :hash and the others don't pass any option at all.