Q4014 Issues with Citect SQL Interfaces via ODBC

I had configured a SQL device to interface
a database of SQL 2000 server via ODBC. This device was used to log
alarms. When the project was first commissioned, the logging went
very well. But a couple of weeks later, I started receiving
hardware alarm "SQL interface Error" and the alarm logging
discontinued. What may cause this problem?

Solution:

Intersolv SQL library used in CitectSCADA
is the third-party product (owned by Q+E) and built in 1995
(released with Citect V3.00). There are a number of defects in this
product and they cannot be fixed as the product was already out of
support a few years ago and the owner was out of business. As we
are not given the source code, we will not be able to fix anything.

There are many issues with this product that are documented in
our KB. Our position is to just provide a workaround and some
advice to achieve better results. Recently, SQL server has been
increasingly adopted as data storage for CitectSCADA applications.
But our outdated library cannot handle such a large scale SQL data
transaction. If you still want to use this library with SQL server
via ODBC, you should have to consider following;

2. Use the fixed size of transaction log to restrict the log
file growing.

3. Back up the database regularly to keep the transaction log
size under control. When SQL Server finishes backing up a database
or its transaction log, it automatically truncates the inactive
portion of the transaction log. If the transaction log file is
full, your database transactions will cease.

4. Keep working tables as smaller as possible. When Citect SQL
adds or appends a new row to data table, it uses SELECT * first to
get column information. If there are hundred and thousand of
records in the table, this action will certainly hinder the
performance and may cause lock ups as these SQL Cicode functions
are block functions.

5. Use SQL Server trigger to remove records from the working
tables to the permanent tables. In this way, sizes of the working
tables that are directly interfaced with Citect are not growing
unrestricted.

6. Do NOT run SQL Cicode functions on a critical Citect machine.
As all SQL Cicode functions are block functions and running in the
main thread, #COM or trend miss samples are likely to occur when
SQL functions are executed on IO or trend. Similarly, an alarm may
miss a scan or a report may miss a trigger if the functions are
running on an alarm or report server.

7. Do NOT execute SQL, which queries an ODBC server,
from a CitectSCADA client that is hosting the same ODBC
server. As all SQL Cicode functions are blocking and are running in
the main thread, this could result in a deadlock condition and will
likely hang the client.

The issue addressed in summary is probably caused by the size of a
destination table. When the project is commissioned, the table size
is smaller. As it is growing and more alarms are queued up,
eventually the SQL connection is timeout and the SQL handle is
broken. This will lead to runtime error "SQL Interface Error". The
workaround is to use a temporary SQL table to accommodate logging
and then use SQL trigger to move the records to a permanent table.
To ensure optimal performance, you should back up your databases on
a regular basis.

This guideline also applies to other databases via ODBC
interfaces, such as MS Access Database (.mdb). To ensure optimal
performance of your mdb databases, you should compact and repair
your MS Access files on a regular basis to remove inactive
transaction information. Failing to do so, your mdb database will
grow unrestricted until it reaches the maximum size (2G bytes). If
you are in this situation, you will received error messages
something like "-1102 [Microsoft][ODBC Microsoft Access Driver]
Could not update; currently locked by user 'admin' on machine
...".

An alternative to the Intersolv library is the ADO
method. There are two ways to implement this:
1. Restore the PLUSADOCon.ctz attached to this article. The
available functions are documented within the project; or
2. Use the CSV_DB cicode functions (eg CSV_DB_Execute()) from the
CSV_Include project. These functions are documented in the Citect
Help.