Click here to Skip to main content
15,867,704 members
Please Sign up or sign in to vote.
5.00/5 (3 votes)
See more:
Good afternoon,

We have previously developed an app in-house using a local PC within the warehouse as a SQL server.

In the past week our corporate IT spun up an instance of Windows Server 2008 R2 for us, and we moved our SQL Server Express instance to that box. The physical location is no longer maintained on the network within our own building, but in our corporate HQ 5 miles up the road.

Two or three times a day we are seeing network disruptions, which are causing our SQL queries to hang. We're receiving no errors within our VB code... we updated our error traps to be wide open, but it's catching nothing... there's just no results being returned.

Does anybody know how to handle these types of errors, so we can recover naturally? It's strange that when we were on our internal network, any internal disruption did fire off our error traps, and we could recover programmatically, but on the off-site server we get nothing...

The "core" SQL process is detailed below...

Thank you very much for your help !

-Bert

VB
Public Function GetSqlServer(cmd As String, Timeout As Integer) As Object
    Try

        Dim da As New SqlClient.SqlDataAdapter
        Dim dc As New SqlClient.SqlCommand
        Dim dc2 As New SqlClient.SqlConnection

        Dim s As Object = Commandstring
        Dim s2 As String = s
        dc2.ConnectionString = s2
        dc.CommandText = cmd
        dc.Connection = dc2
        dc.CommandTimeout = Timeout
        da.SelectCommand = dc
        Dim ds As New DataSet
        da.Fill(ds)
        dc2.Close()
        Return ds
    Catch ex As Exception
        Return ex
    End Try
End Function
Posted
Comments
Maciej Los 26-Feb-14 17:55pm    
One of the reason could be too long timeout. Second - SELECT * for large portion of data.
Please, be more specific and provide more details about your issue, for example: the speed of connection brought to you by internet provider, the oftens reason of hangs, etc.
Bert Mitton 27-Feb-14 6:12am    
I am unsure of the connection speed, but it is a high-speed, not dialup.

It happens on random queries, and we've seen it "catch" on queries which normally take milliseconds, so it shouldn't be a time-out issue.

Also, looking at the server resources, we're not even close to red-lining, so it's not a load issue.
Bernhard Hiller 27-Feb-14 2:52am    
When you debug the application, does the "hang" occur at "da.Fill(ds)"? Do I understand correctly that at the time of application hang, the network conncetion is not functional, i.e. e.g. you cannot ping the SQL Server?
Bert Mitton 27-Feb-14 6:10am    
da.Fill(ds) is where we are getting the hang. The interruption happens 2 or 3 times a day, and for short durations, so I don't have time to ping the server.

We do see it manifest itself elsewhere... print jobs from other software sent over the network at that time will be delayed, Remote Desktop sessions will "hang", but recover once the disruption clears.

Our IT team is satisfied as long as the print jobs eventually pop through, and the Remote Desktops recover. This leaves me in a position where I would have to get my own software to recover, but without an error to trap, I don't know how.

Also, in these instances it seems the timeout isn't doing anything either. The process just never returns.

Thanks

Bernhard Hiller 27-Feb-14 10:43am    
Try executing the function in a background thread, and monitor the timeout yourself. But that requires some advanced knowledge of the framework.

1 solution

There could be several reasons of losing connection to SQL Server over internet...


  1. Internet provider: speed transfer limit over the internet and/or connection breaks
  2. As you had mentioned: "The physical location is no longer maintained on the network within our own building, but in our corporate HQ 5 miles up the road." This means that you . It means that your company uses Internet connection and this connection could be broken at any time.

  3. Large data transfer using SELECT *
  4. Never use:
    SQL
    SELECT *
    FROM TableName

    Better use:
    SQL
    SELECT Field1, Field2, Field3
    FROM TableName


  5. Windows firewall settings
  6. Windows firewall could kill connection after a specified period of time and need to be re-configured.
    Configure the Windows Firewall to Allow SQL Server Access[^]

  7. SQL Server Settings
  8. SQL server resets connection after specified timeout and the client is not notified by the database driver (SqlClient).
    Troubleshooting: Timeout Expired[^]

  9. and finally: bad code design - database connection has been never closed
  10. In my opinion, when the connection is opened, you should close it ASAP. It could be the reason why firewall periodically resets connection.

 
Share this answer
 
v2
Comments
phil.o 27-Feb-14 11:06am    
My 5!
I can also think of a routing problem between both sites, if they are on different subnets. I remember a case where we had a MTU size problem, we had to force a specific value for MTU on the router. Hard to tell if it applies to current situation, though.
Maciej Los 27-Feb-14 13:47pm    
It's quite possible, i think.

Thank you, Phil ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900