VB.NET App can't retreive data from SQL DB after several hours and/or 1 day or more

Are you manually handling the connection state? If so maybe you left a connection open ?

Forgive me for my lack of understanding with regards to terminology, but when you say, "Are you manually handling the connection state?" do you mean an External object to disconnect? I do have the connections being opened and closed (after data is read/populated into the controls) via the sql.Open and an sql.Close methods.

I'll attach my code for you.

Private Sub InitPackageNames()
        cmbPackages.Items.Add("Select a Package")
        cmbVersions.Items.Add("Select a Version")
        cmbBuilds.Items.Add("Select a Build")
 
 
        Try
            Dim sqlStr As New SqlConnectionStringBuilder()
            sqlStr.DataSource = db_config.server
            sqlStr.InitialCatalog = db_config.catalog
            sqlStr.ConnectTimeout = 300
            sqlStr.IntegratedSecurity = True
 
            Dim sqlCon As New SqlConnection(sqlStr.ConnectionString)
            sqlCon.Open()
 
            Dim sqlCommand As New SqlCommand
            sqlCommand.Connection = sqlCon
            sqlCommand.CommandText = "SELECT DISTINCT s.NAME FROM software s ORDER by s.name"
            sqlCommand.CommandTimeout = 300
            Dim sqlReader As SqlDataReader
            sqlReader = sqlCommand.ExecuteReader()
            While sqlReader.Read()
                cmbPackages.Items.Add(sqlReader("name").ToString)
            End While
            If cmbPackages.Items.Count > 0 Then cmbPackages.SelectedIndex = 0
            sqlReader.Close()
 
            sqlCommand.CommandText = "SELECT DISTINCT s.build FROM software s ORDER by s.build"
            sqlCommand.CommandTimeout = 300
            sqlReader = sqlCommand.ExecuteReader()
            While sqlReader.Read()
                cmbBuilds.Items.Add(sqlReader("build").ToString)
            End While
            If cmbBuilds.Items.Count > 0 Then cmbBuilds.SelectedIndex = 0
            sqlReader.Close()
 
            sqlCommand.CommandText = "SELECT DISTINCT s.Version FROM software s ORDER by s.version"
            sqlCommand.CommandTimeout = 300
            sqlReader = sqlCommand.ExecuteReader()
            While sqlReader.Read()
                cmbVersions.Items.Add(sqlReader("version").ToString)
            End While
            If cmbVersions.Items.Count > 0 Then cmbVersions.SelectedIndex = 0
            sqlReader.Close()
 
            sqlCon.Close()
 
        Catch e As SqlException
            MsgBox(e.Message, MsgBoxStyle.Critical, "MSSQL Exception")
        End Try
    End Sub

Select allOpen in new window

Try this instead of the sqlCon.Open command

If sqlCon.State = ConnectionState.Closed Then
sqlCon.open
End If

This will check to make sure the connection is actually closed before opening. I am not sure this would cause a problem but depending on the type of authentication you use it might.
 Are you using Windows Authentication or SQL Server logins?
Thanks,
James

 

I'm using Windows Authentication.  I'll give this a try and see how it works within a day and check back here for additional comments or provide you an update.
Thanks jaciesla!

I tried the method you suggested but wrote it like this, just in case the connection wasn't closed (and Should be), so I could close it and then open the connection. HOWEVER, this did not help. I came in this morning, and had to reboot the DB again. Once rebooted, my application could connect and retrieve the data. I don't think its an issue connecting but actually retrieving data. Even though I've set the sqlStr.ConnectionTimeout = 300 AND sqlCommand.CommandTimeout = 300, when the application opens after 4 minutes, I run the query and in 30 seconds, it fails and says it couldn't connect to the DB.

 Dim sqlCon As New SqlConnection(sqlStr.ConnectionString)
            If sqlCon.State = ConnectionState.Open Then
                sqlCon.Close()
            Else
                sqlCon.Open()
            End If

Ok, Next step would be to check the SQL Server error logs and the Windows logs of both the SQL Server and the client machine. We are looking for refused connections or timeouts that can help us pinpoint the problem in the SQL Server log.

Also, what type of firewall are you using on the VM?
Thanks,
James

Hi James,
I'm going to give you all the error's thare are unique but am also looking at the time I rebooted the DB yesterday (around 1:28 in the afternoon), so my application could work, until this morning around 7:30 this morning, when the application again wouldn't return any data.

The db is within the same Trusted Domain. I'm in Phoenix and the DB is in Dallas.

There were no "refused connections" that I can see. Or at least the description is not indicative of that.
This error repeated numerous times, from around 4:02 a.m. - 7:23 a.m.:
1/31/2008 07:17:39,spid357,Unknown,Error: 4014<c/> Severity: 20<c/> State: 2.,
01/31/2008 07:04:19,spid337,Unknown,A fatal error occurred while reading the input stream from the network. The session will be terminated.
01/31/2008 06:06:47,Logon,Unknown,Error: 17836<c/> Severity: 20<c/> State: 1.,
01/31/2008 06:02:29,Logon,Unknown,Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: 171.178.244.245],

OTHER ERRORS:
01/31/2008 06:02:29,Logon,Unknown,Error: 17836<c/> Severity: 20<c/> State: 1.,
01/31/2008 04:11:33,spid632,Unknown,Autogrow of file 'templog' in database 'tempdb' was cancelled by user or timed out after 1375 milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
01/30/2008 20:31:45,Logon,Unknown,Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: 165.217.144.245.

I'll be more than happy to send you the log files from both the Server and the Client.

The Cilent machine appears to be just fine as there were no errors reflecting a connection issue with the DB.

Thanks again,
Wally

Let me do some research. I'll get back to you on this...

I checked under ODBC Data Sources and I'm not seeing OLEDB Provider 9.0 installed. Where do I check for the installation of MSXML 6.0?

I downloaded the Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider MSI and the Microsoft Core XML Services (MSXML) 6.0. However, I think we've got the MSXML 6.0 installed. The OLEDB Provide 9.0 is not. I'll need to talk to my Manager to see about getting this added.
I'll get it loaded on my workstation in the meantime. Is there any special setup for OLEDB Provide 9.0 on either machine or do I just need to have the drivers loaded?

James,
Here's the current Information on our SQL 2005 Server:
Microsoft SQL Server Management Studio - 9.00.3042.00
Microsoft Analysis Services Client Tools - 2005.090.3042.00
Microsoft Data Access Components (MDAC) - 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML - 2.6 3.0 6.0
Microsoft Internet Explorer       - 6.0.3790.1830
Microsoft .NET Framework - 2.0.50727.832
Operating System - 5.2.3790

I'm going to install MSXML and  9.0 OLE DB Provider on our test database just to make sure there's no issues.

Wally,
I think the answer to this problem lies in the MDAC versions between the client and the server. I know the MDAC version you are using (2000.086.1830.00) is not the latest but what is more important is the version you are using on the client. The mismatch of MDAC's could very likely produce a 'does not play well together' condition that can be causing the type of problem we are having.

Once you get the MDAC's updated please verify there are no firewall rules that could be causing any connectivity problems. The fact that it connects initially then requires a reboot to work makes me think that it is not a firewall issue but it is best just to verify.

Any chance you can quickly send over the MDAC version of the client as well?

Thanks,
James

Here's the version I found under the following registry path:
WORKSTATION:
HKLM\Software\Microsoft\DataAccess = 2.81.1117.0
Workstation is running MSXML 4.0 SP2

SERVER CORE OS(not SQL):
HKLM\Software\Microsoft\DataAccess = 2.82.1830.0
I couldn't verify the exact MSXML Version being used on the Core OS. Not sure if you needed that.

Wally,
Can you try your app from a different machine ? This might be the quickest way to confirm the MDAC problem.

Well, being that all of our machines get software pushes to them, I might expect the same results. However, I can install the latest MDAC. Do you know what the most current/stable version of MDAC is today? I'm going to go and google it really quick.

Ok, that makes sense. Microsoft will have the latest MDAC download if you do a quick search. Please let me know the results of updating the MDAC on the client.
Thanks,
James

Apparently, my workstation has the lastest MDAC Version, 2.8. Now, I'm not sure if this is 2.8 or 2.8 w/ SP1. When I look on Microsoft's website, it shows MDAC 2.8 SP1 - 2.81.

MDAC (HKLM\Software\Microsoft\DataAccess ) on our 2003 Server shows this version = 2.82.1830.0

Apparently, my workstation has the lastest MDAC Version, 2.8. Now, I'm not sure if this is 2.8 or 2.8 w/ SP1. When I look on Microsoft's website, it shows MDAC 2.8 SP1 - 2.81.

MDAC (HKLM\Software\Microsoft\DataAccess ) on our 2003 Server shows this version = 2.82.1830.0.

It looks like they're almost the same but I'm not sure if there is a different version of MDAC for a workstation and a server. I believe they worked the same on both if my memory (barely) serves me right.
I'm going to install the MDAC 2.81 on my PC to see if that brings it up and to see if it will fix the problem. Do I still need to install the 9.0 OLE DB Provider and if so, what does installing this really do?

It looks like the MDAC 2.8 is compatible with these OS's:
Supported Operating Systems: Windows 2000; Windows 98; Windows ME; Windows NT; Windows XP

and 2.8 SP1 is only compatible with these OS's:
Supported Operating Systems: Windows 2000; Windows 98; Windows ME; Windows NT

OLEDB is a main component when accessing a remote DB.
An application using OLE DB would use this request sequence:

Initialize OLE.
Connect to a data source.
Issue a command.
Process the results.
Release the data source object and uninitialize OLE.

So I cannot give you a specific answer as to what is different between OLEDB 9.0 and the version you have but I have encountered problems in the past trying to get datacubes working when they have outdated OLEDB drivers.

I've installed OLEDB 9.0 and restarted the SQL Engine/Agent services. I don't believe there's anything to configure. I looked under the Data Sources(ODBC) for the latest list of drivers and I don't see it there. Is there anywhere else I can verify it or is there no need?

James, I rebooted the Server. OLEDB 9.0 and a higher version of MSXML than 6.0 was already installed. I went to run the install of MSXML and it said a higher version was already instdalled. I'm going to run my application on Saturday and Sunday to see how it's working and will verify the logs. When I come in Monday, I'll provide you with a Status. Regardless of the status, you've provided me with a lot of good information and helped me in more ways than I can begin to thank you. I'll assign you the points on Monday with a status, regardless of the outcome.

Wally