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;

1. Use "Simple" model for a SQL database to limit transaction information logging to transaction log file.

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.