Link to home
Start Free TrialLog in
Avatar of cpx_Support
cpx_Support

asked on

SQL Time out only from one specific computer client

I have a program made with VB.Net that connects via Internet to an SQL Server.
This program is running without problem in about 20-30 computers.
From one specific computer it gets a TimeOut error. Once that Time Out ocurs this computer don't get connection again to the SQL, while others computers can work without problem.
When I reboot the SQL server, the problematic computer can connect again during some time, and then another time it gets the TimeOut error again.
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Since the issue is only with a specific computer, I wouldn't restart the SQL Server since this will affect all client computers.
My guess is that specific client has network issues and when it looses connection it won't be able to reconnect. You should try to fix the problem in the client computer but your application also should be changed to be able to reconnect to SQL Server when it get this kind of failure.
Avatar of cpx_Support
cpx_Support

ASKER

Thanks Vitor,

How can I try to reconnect?

This is my code:

           Dim strConexio64 as String
           strConexio64="Data Source=" & "0.0.0.0" & "\sql_64,49264" & ";Initial Catalog=gravacio;Persist Security Info=True;" &  "User ID=___;Password=____"
           Dim tempEmpresesTableadapter As GravacioDataSetTableAdapters.IntesBd_EmpresesTableAdapter
            tempEmpresesTableadapter = New GravacioDataSetTableAdapters.IntesBd_EmpresesTableAdapter
            tempEmpresesTableadapter.Connection.ConnectionString = strConexio64

            Dim taula As GravacioDataSet.IntesBd_EmpresesDataTable
            Try
                taula = tempEmpresesTableadapter.GetDataBy_CodLogic(strCodiLogic)
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Procés: " & Reflection.MethodInfo.GetCurrentMethod.ReflectedType.FullName & " - " & Reflection.MethodInfo.GetCurrentMethod.Name)
                               Exit Sub
            End Try
I think you should use an error handling to check the kind of error has been thrown and reconnect if that's the case.
I'm using error handling, but at the moment it is showing an error message.
If I change the error handling and make a goto to the "GetDataBy"  again, is the same that closeing the program and starting it again, isn't it?
Do you know what's the error code for the time out?
If so, handle that error and try to reconnect and send the code back again to retry the last operation. You should also limit the number of retries so it won't be there forever.
I did a lot of test and at the moment I am at:

This problem occurs in two clients, the problem is something related with the connection.
The program runs well on all other clients, I went at client premises and tried to conect to the program with my computer via therir network and the problem rised again.

The full exception is:

TestPorts.Form1 - Button2_Click - Se agotó el tiempo de espera de ejecución. El período de tiempo de espera transcurrió antes de la finalización de la operación o el servidor no responde.-System.ComponentModel.Win32Exception (0x80004005): Tiempo de espera de la operación de espera agotado

I googled that error and it happen sometimes, and some people saty the same, that a reboot of the computer solved temporary the problem.

Any idea?

This happen when I do a SQL query, but I can write to his table.
The error alwais raises at 36 seconds, even when I changed the timeout. via:

Data Source=0.0.0.0\sql_64,49264;Initial Catalog=gravacio;Persist Security Info=True;User ID=user;Password=pwd;Connection Timeout=120
I don't think is the connection timeout what you should chance but the command timeout.
Also, is this always during the same operation? If so, it might mean that the query you're executing is causing locks and if is that the case then you should try to improve the query.
No, I connect to two different instances, one is 32 bit and the other is 64 bit, the problem occurs at both instances.

And I'm pretty sure is not a query problem. The query is so simple and the number of rows is very small about 100.

What I can't understand is:

The same computer (It has internet conection, email, google...)
-When uses the internet conection of his Company Ethernet, my program can write on my SQL DDBB but can't make a SELECT query.
-If I connect the same computer on a USB Internet Stick, It can perform the normal procedures of the program write and read.

-Then i have tho think that is not a query problem, and not a computer problem, but a connection problem.

But what can prevent to make a Select query and permit to do a write query.
-When uses the internet conection of his Company Ethernet, my program can write on my SQL DDBB but can't make a SELECT query.
 -If I connect the same computer on a USB Internet Stick, It can perform the normal procedures of the program write and read.
Perhaps a firewall rule?
From this distance I'm not sure if we can help you better than give you suggestions. Maybe have somebody next to you to help on the troubleshooting.
Is there any way that a firewall can permit to do a write query and not a Select query. If the SQL port is closed is closed for all, isn't it?
Is there any way that a firewall can permit to do a write query and not a Select query
Right. The firewall doesn't have any effect in the SQL Server permissions.

If the SQL port is closed is closed for all, isn't it?
No. You can close the connections only for a particular IP or a range of IPs.

But rethinking on your issue, are you using a Windows Authentication login or a SQL Server login? Because with the USB stick you shouldn't be able to connect to the Windows Domain, right?
I'm usig SQL Server login, neither on the client or my Company uses Windows Domain.
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok thanks for your Support.