SQL SERVER SESSION TIMEOUT SETTING - Front End Form Corruption Problem

Posted on 2007-07-23
Last Modified: 2008-01-23

We have a legacy financial application that was ported a few years back from Access to SQL Server 2000.  Users still access the server through a MS Access form front end.  We have an ongoing problem in which the user  access form continually gets corrupted and users have to redownload this form from the server.  I suspect but don't have any real proof that form is getting corrupted because the majority of our users who access the system (directly from their desktop or via terminal server) never logout and usually leave the application up and running for many hours at a time.  

I am looking for a way to automatically log the users out and terminate the connection to the database if the connection has been idle for  a certain period.  

If there are any other suggestions as to why this issue maybe occurring it would be greatly appreciated.

 Thanks in advance.
Question by:jfexchange
    1 Comment
    LVL 3

    Accepted Solution

    As far as I know you can not configure a "Connection timeout" server side.
    But I have used a script like below in the past to get rid of Access connections.

    print 'Begin checking kill sequence...'
    declare @DelayInMinutes int, @spid int, @ApplicationMask varchar(50), @sql varchar(15)
    select @DelayInMinutes = 10, @ApplicationMask = '%Access%'
    while 1 =1
          while exists(
          select * from master..sysprocesses
          where status = 'sleeping'
            and spid > 50
            and last_batch < dateadd(minute, -@DelayInMinutes, current_timestamp)
            and [program_name] like @ApplicationMask
                select top 1 @spid = spid
                from master..sysprocesses
                where status = 'sleeping' and spid > 50
                  and last_batch < dateadd(minute, -@DelayInMinutes, current_timestamp)
                and [program_name] like @ApplicationMask
                print 'Killing spid ' + convert(varchar(10), @spid) + '...'
                select @sql = 'kill ' + convert(varchar(10), @spid) + ''
                exec (@sql)
          print 'Waiting for 5 minutes'
          waitfor delay '00:00:05'


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now