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

We currently have a Database that is running on Windows 2003 Server that runs on a VMWare Server (most recent version of VMWare).
I have VS 2005 with .NET Framework 3.0 installed running on a XP Professional Dell Laptop w/ SP2 installed.
I have a front-end (vb.net) application I've created that connects to the DB. I'm running into an issue where I have to reboot the DB once a day to get my application to connect, query the db and return all the data from the tables to the ListViewItem control that displays all the data and a couple of other ComboBox controls that get populated.
I've got the sqlStr.ConnectionTimeout = 300 and the sqlCommand.CommandTimeout = 300 so that the application has enough time to connect, retrieve and load data to the app. It usually takes the application (on a good connection, i.e. right after a db reboot) about 2 minutes to load data and appear in front of the user. On a bad connection, it takes around 3 - 4 minutes for it to load. So, when it gets past the 2 minute mark, I know the db has to be rebooted.
What I can't figure out is, why won't the DB return my data to populate it in the application OR what in the application prevents the DB from returning the Data? As far as I can tell, everything works fine until after a day or who knows, several hours.

Thank you in advance,
Wally
wally_davisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jacieslaCommented:
Are you manually handling the connection state? If so maybe you left a connection open ?
0
wally_davisAuthor Commented:
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

Open in new window

0
jacieslaCommented:

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

 
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

wally_davisAuthor Commented:
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!
0
wally_davisAuthor Commented:
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
0
jacieslaCommented:
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
0
wally_davisAuthor Commented:
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
0
jacieslaCommented:
Hello Wally,
I think the problem might lie in the driver versions between the machines. The error

"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"

indicates that you might have mismatched or out of date MDACs. Can you please make sure you have MSXML 6.0 and OLEDB provider 9.0 installed ?
Thanks,
James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wally_davisAuthor Commented:
Let me do some research. I'll get back to you on this...
0
wally_davisAuthor Commented:
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?
0
wally_davisAuthor Commented:
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?
0
wally_davisAuthor Commented:
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
0
wally_davisAuthor Commented:
I'm going to install MSXML and  9.0 OLE DB Provider on our test database just to make sure there's no issues.
0
jacieslaCommented:
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
0
wally_davisAuthor Commented:
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.
0
jacieslaCommented:
Wally,
Can you try your app from a different machine ? This might be the quickest way to confirm the MDAC problem.
0
wally_davisAuthor Commented:
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.
0
jacieslaCommented:
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
0
wally_davisAuthor Commented:
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
0
wally_davisAuthor Commented:
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?
0
wally_davisAuthor Commented:
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
0
jacieslaCommented:
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.

0
wally_davisAuthor Commented:
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?
0
wally_davisAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.