Rails - Apr 2006 - MS-SQL Server dropping connections

This isnt a rails specific issue, but the way I may try to fix it might 
be.

Im using ms-sql server on 1 windows server 2003 pc, connecting to 
apache/rails on another.
Intermittantly I get the following exception:

  Execute
    OLE error code:80004005 in Microsoft OLE DB Provider for SQL Server
      Connection failure
    HRESULT error code:0x80020009
      Exception occurred.: SELECT * FROM [real-data-removed]  ) 
C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.13.0/lib/active_record/connection_adapters/abstract_adapter.rb:67:in
`log''

Has anyone else experienced this? The network people tell me there is 
nothing happening from their point of view, but it is annoying many of 
the users.

Most times, the connection appears to get restored fairly quickly 
without any intervention on my behalf.

Im several places in the sqlserver adaptor there is code like
  @connection.execute(sql)

Im proposing that I wrap this in anthoer method, and use something like 
this ( untested so far):

def execute_with_rescue( sql )
    reconnect_attempts = 0
    retry_count=0
    begin

        @connection.eexecute(sql)
    rescue Win32OLEException => e  # or whatever the class is

        # send an email so I know its still happening

        if /Connection failure/.match( e.to_s )
            retry_count+=1
            retry if retry_count < 2
        end

        # if its still failing, drop the connection and reconnect
        if @connection.reconnect! == false
            # try again!
            reconnect_attempts +=1
            if reconnect_attempts < 2
               retry
            else
                # give up
                raise e
            end
        end

    end

end

Does this sound reasonable? Can some one point me to unit tests etc so I 
can submit it as a patch if it should cure my problem?

Thanks

Paul




end

-- 
Posted via http://www.ruby-forum.com/.