Failed SQL Connection over time

J

joe_pool_is

Well-known member
Joined
Jan 18, 2004
Messages
451
Location
Texas
Ive built a small PC app that resides in the Task Bar Tray until one of our employees sends a signal to it with a handheld device.

When the PC app is activated, we connect to the SQL Server to retrieve records using Integrated Security on the PC:
Code: 
Private m_conn As SqlConnection
 
Public Sub New()
  InitializeComponent()
  m_conn = New SqlConnection("Data Source=ACTIVE1;Initial Catalog=Inventory;Integrated Security=SSPI;")
   more code that isnt relivant
End Sub

The problem is that after the PC app has sat around idle for a few hours, the employees get the generic SQL error message "Cannot generate SSPI context." Basically, it can not log in.

My routine that uses the SQL Server is all contained, so I dont know how to prevent this error.
Code: 
Private Sub ViewData()
  Dim cmd As New SqlCommand("SELECT * FROM PartsTable WHERE SN=@SERNO", m_conn)
  cmd.Parameters.AddWithValue("@SERNO", SN)
  Dim da As New SqlDataAdapter(cmd)
  Dim dt As New DataTable()
  Try
    da.Fill(dt)
    If (0 < dt.Rows.Count)) Then
       sometimes it gets here, but not if the app has been idle for a while
    End If
  Catch ex As Exception
    MessageBox.Show(ex.Message)
  Finally
    dt.Dispose()
    da.Dispose()
  End Try
End Sub
If I can connect 5-10 times straight, what would cause code like this to fail after it has been sitting idle for a while? When the PC app is activated, a Dialog Box is displayed that the Employee has to confirm the operation on. Only then is this routine called.
 
Hello, Sir! Welcome back.

I have seen the Microsoft KB article. It has a lot of information, and I went through it as much as I could. The rest of it (Kerbose -vs- named pipe, etc.) I passed on to our SQL administrator, who keeps telling me it isnt his server that is doing anything.

I am able to connect and interact with the server most of the time. It is only when the application has been idle for a while that this unique bug appears.

Typically, to fix it, we log the user off, then log them back on. Exiting and restarting my application does not fix the problem with connecting to SQL.
 
Sounds odd! Have you tried disabling connection pooling (add a Pooling=false to your connection string) - just in case something is happening there that is keeping broken connections around?
 
Back
Top