MS-SQL Server dropping connections


#1

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