Hi InTouch Experts, I am in BIG trouble PLEASE HELP...

I have MS-SQL Server 2000 STD & In-SQL 8.0 installed in a server to be used as Plant Historian. And Wonderware InTouch 8.0 installed at many workstations from where I am using AlmDBViewer Control to display the historical data from the above said SQL Server. It was working fine for the last three years, currently it is showing the following error message while fetching the data:
Microsoft OLE DB Provider for SQL Server: Timeout expired.

What does this mean? Can abybody please help me in this matter?

I already changed the connection timeout value in the database properties popup.
I heard that there is a "Command Timeout" property for the database object. If it is there can anybody please tell me how to use it in InTouch.

I am in an urgent situtation, PLEASE HELP.

 

I'm not an Intouch expert, so I can't help you with any problems at that end. However, this message is a time-out error message from the MS SQL Server database. I think it is also connected with using the DB via ODBC. There are a variety of time-outs in the database, and I believe that some of them are hard coded and can't be changed.

It's hard to say what is causing this, as there are many potential causes. One problem can be that your queries are trying to pull up too much data at once. It could also be a problem with the indexes or some sort of data corruption.

Another problem is that Microsoft's database locking method was at one time very poor, meaning that when you had multiple clients accessing the same database there could be quite a lot of contention for the same records. I think they've fixed that, but I don't know in what version (the version you are using is I think a couple of versions old).

You did mention that the database is located on a server that is separate from the clients. Another possible cause therefore could be that the network has slowed down. "Slow" doesn't just refer to network transfer speeds. It also refers to things like the database client doing a DNS look up to find the database server. If something has changed in your network configuration, this could cause the problem to start appearing. If your IT department is running this network, then it could be triggered by any of a number of different changes they may have done.

Try a Google search for the message you mentioned. I think you will find out that it is in fact a fairly common problem with MS SQL Server. There may be some patches available from Microsoft to help with the problem, but I would talk to Intouch support first before installing them as I don't know what side effects there could be.

 

Thanks a lot for your reply and trying to help me.

Yesterday I tried to get the same volume of SQL data using Visual Basic, imported in an excel file and it is working fine. In the beginning VB also showed me the same error, but when I set the "Command Timeout" variable to 0 (never timeout) it worked.

So I think this particular problem is with my InTouch 8.0 client side only and unfortunately I don't have enough information to set the Command Timeout Property in InTouch 8.0.

If you have any information (or if you get some) please pass to me.

Thanks again...

Zorif

 

I think the way MS SQL Server works, the time-out is in a library or client process that is supplied by Microsoft, and is not part of InTouch. InTouch may or may not be able to change this value, but as I am not an InTouch expert I couldn't say for sure. I suspect that you can set the same time-out variable via the MS Windows ODBC "control panel" (assuming that InTouch is using ODBC as the message seems to indicate).

The "never time-out" setting is good for diagnosing the problem. It isn't however something that you would want in production use as a database problem may cause the clients to wait forever instead of reporting a problem if the database really does go down.

A quick Google search for the error message you reported turns up the following bug report at Microsoft: http://support.microsoft.com/kb/300420

They state the problem is: "The MDAC 2.6 version of the SQL Server Network Library, Dbnetlib.dll, attempts to determine the host name of the IP address using a reverse lookup. If the client computer has a slow Domain Naming Service (DNS) server, or no DNS server, and the local HOSTS or LMHOSTS file on the computer does not contain the host name for the IP address of the SQL Server, the reverse lookup will time out after five seconds.

Note that this is an internal timeout inside of the SQL Server driver code only, and it may or may not generate a timeout error in the client application. The connection attempt can succeed, but every new connection made by the application will encounter the same five-second delay."

So, what Microsoft seems to be saying is that the problem is in the MS SQL Server driver, and different clients may or may not report it. That means that what MS VB might be doing when you set the time-out to "0" is just ignore the error the MS SQL Server driver is reporting.

The same link gives several different ways of patching over the problem. I would suggest confirming the problem by putting the server host name in the "HOSTS" file and seeing if the problem goes away. If it does, you could try one of the more permanent fixes (which involve installing a software patch).

If this is the problem, then it seems that the root cause is improper handling of DNS in the MS SQL Server driver. Networking in MS Windows is a Frankenstein creation with Microsoft's old proprietary network more or less duct taped on top of TCP/IP. The result is something which is lot more complicated and error prone than it needs to be. This is one reason for suggesting that this is a likely area to look for problems.

P.S. The above information was readily found using Google. If you are looking for information about problems with MS Windows, you are a lot better off using Google than using Microsoft's own search engine (which often doesn't work very well).

 

From my experience, the first thing that comes to my mind is that the query is timing out. May be the database size has increased too much. To confirm this is the problem or not, you may want to backup and delete active data from database and check. Another possibility would be to use indexing.

Vipul Shah
http://www.egenietech.com

 

Thanks Michael Griffin for your valuable information and help. Actually for the last few days I was out of station that's the reason I couldn't reply. I will check the DNS, HOSTS, LMHOSTS today and come back to you with the results.

Thanks again.

Zorif

 

Thanks Vipul.

Yes you are correct the datbase size was too large and when I deleted a large amount of data from the database and then it started working fine again. But I need to keep atleast one year of data in the current database which may be 1.5 GB in size. Indexing is helping but still too slow and sometimes times out.

Regards,

Zorif