Not all info written to database

We have a beer analyzer that is automatically writting to FSQL via the UCON kepware driver. Every once in a while, it misses a sample. The only thing I can find is the following error in my FSQL group about the same time my sample went missing. Any ideas?

Hmm, troubleshooting these random timeouts can be a bit difficult. In fact, I typed in “ODBC S” into google and the first suggestion was “ODBC SQL SERVER Driver timeout expired”, which probably isn’t a good sign.

First off, how often does this occur? Does it seem to happen around a certain time? This particular error happened around 8pm, is there any chance the database was undergoing some sort of maintenance during that time (backup, archiving)?

When the group is running well, how long does it take to execute? The status panel should show the last execution, but you can get more information by going to Connections->System Status->Statistics, and then finding the group under the statistics monitors area. If the average is quick, it could indicate something on the DB side, like the maintenance actions mentioned above, or stored procedures or big queries that might lock the table as well.

In regards to the ODBC configuration, I would verify that connection pooling is turned on for the driver (go to the ODBC administrator, then the “connection pooling” tab, and double click on the sql server driver).

Also, I think that under the dsn configuration you can check the current timeout. I would guess it’s probably already much larger than should be necessary, but you may want to confirm and increase it if necessary.

In my experience, things like this are most often caused by things on the database side. The SQL Server performance monitor can be useful in watching what’s going on (though I personally don’t have much experience using it).

Hope this helps,

Thanks for the tips. The timing of when it happens seems to be pretty random and I don’t see any jobs running on SQL Server when these events have occured. The average execution time is 33 ms for writes. 0 for the group avg Exec Cost and only .03 executions per second, which seems okay to me. The ODBC pooling is turned on. I didn’t find a current timeout under dsn, but I’ll ask someone. Like you said, it difficult to find an issue for random timeouts. So far, it has not been often enough to cause any great issues, but I am hoping it does not get worse. Maybe if it does, we will be able to isolate it.

Thanks again,
Ron