Troubleshooting FactorySQL WinCC Tag Logging ODBC SQL Errors

Problem Overview

When FactorySQL 4.2.1 logs WinCC tags from the OPCServer.WinCC namespace to a Microsoft SQL Server database through an ODBC connection, two recurring failures appear at the FSQLGroup.CentralHeartbeat execution stage. The first is ODBC state 07002 with the message "count field incorrect or syntax error" raised when a multi-tag group tries to execute its UPDATE or INSERT statement. The second is ODBC state 42000 with the message "Unclosed quotation mark after the character string 'C2F2'" followed by "Incorrect syntax near 'C2F2'". Both failures are emitted from FactorySQL.LocalDBOperationProvider.ExecuteParameterNonQuery, called from FSQLDBCollection.WriteData, which is itself invoked from FSQLGroup.CentralHeartbeat.

The problem is intermittent on a per-group basis. Selecting a single tag from any failing group writes successfully. Reducing the group to a subset that excludes string-typed items also writes successfully. The pattern points to a SQL identifier or value escaping problem at the parameterized query generation stage, not to network, permissions, or OPC subscription issues. The same symptoms are reported when the destination table is MainLabFSQLGROUP and when booleans such as B4S3T01/TANK.OCCUPIED, B2S1T01/TANK.OCCUPIED, and B5S3T01/TANK.OCCUPIED are mixed with float tags such as A2S3T01LT/BR_AI.V_IN (RMST3) and B5S3T03LT/BR_AI.V_IN (BTRMLKST).

This article documents the diagnostic path, the two distinct root causes that produce the two error codes, the recommended remediation, and the verification procedure to confirm a clean log run on FactorySQL versions 4.1.6 through 4.2.8 and on the WinCC Unified TIA Portal V20 data logging model.

Affected Software Stack

Component	Version / Range	Notes
FactorySQL (FSQL)	4.1.6, 4.2.1, 4.2.3, 4.2.8	4.2.1 reproduces both 07002 and 42000; 4.2.3 closed the boolean/ODBC regression; 4.2.8 is the recommended target
WinCC OPC Server	OPCServer.WinCC (DA 2.0 / 3.0)	Local on localhost; tag paths of the form AS1/DB_ALARM.READY_FOR_* and A2S3T01LT/BR_AI.V_IN
WinCC Unified	TIA Portal V20	Configuration of logged tags follows the Unified tag model documented in the Basics of data logging (RT Unified) reference
WinCC Classic	7.0 SP3, 7.4, 7.5, 8.0	Known logging interruption points also exist in WinCC 7/8 trends; see the trend values missed or not logging in WinCC v8.0 symptom set
WinCC 7 Tag Logging	7.0 SP3 / SP1 32-bit	Logging failure scope documented in the WinCC tag logging problem support thread
Database	Microsoft SQL Server 2014, 2016, 2019, 2022	ODBC Driver 17 or 18 for SQL Server; native SQL Server client also supported via the native FSQL connection
OS	Windows Server 2016 / 2019 / 2022, Windows 10 21H2+	Same OS scope as WinCC 7.x SP3 and WinCC Unified V20

Root Cause Analysis

FactorySQL builds a parameterized SQL statement for each FSQLGroup. The statement shape is, in simplified form, an INSERT into a destination table, with one column per OPC item, plus a writeback column of the form {FSQLGroupName_fieldname}. The placeholder binding is done through IDbDataParameter[] when the destination provider is ODBC. Three failure modes present themselves depending on which inputs collide with the SQL Server parser.

Root Cause A — Reserved SQL keyword or invalid identifier in the OPC item alias

SQL Server reserves a list of words such as SELECT, COUNT, SUM, ORDER, KEY, TABLE, USER, VALUE, and IDENTITY. If a tag in the group carries a name that collides with a reserved word and the table the group is writing to uses that name as the column name, the resulting statement is rejected at parse time. The ODBC state is 07002 "count field incorrect or syntax error" or, depending on the SQL Server parser version, 42000 "Incorrect syntax near the keyword '...'". The group then fails on ExecuteParameterNonQuery.

Two related sub-causes produce the same symptom:

1. OPC alias collision. A tag is aliased in the group to a name that is a reserved keyword (for example, a tag aliased as SELECT in the table column list).
2. Whitespace or non-alphanumeric characters in the column. Spaces, dashes, slashes, or parentheses in the alias force a quoted identifier; the quotation can be missing or unbalanced in the generated DDL when the column is a VARCHAR derived from a VT_R4 or VT_BSTR source type.

Root Cause B — String value not escaped before parameterization

When an OPC item returns a VT_BSTR value containing a single quote or other SQL Server syntax character (for example the literal C2F2 bracketed by a process condition) the value is bound as a parameter. With the ODBC driver in the 4.2.1 build the parameter substitution inserts a stray single quote around the parameter, producing two consecutive string delimiters. SQL Server then reports 42000 "Unclosed quotation mark after the character string 'C2F2'" followed by "Incorrect syntax near 'C2F2'". The stack shows LocalDBOperationProvider.ExecuteParameterNonQuery then FSQLDBCollection.WriteData, confirming the failure is at insert time, not at OPC subscription time.

The 4.2.1 release notes for the ODBC provider do not enumerate this specific failure path, but the boolean/ODBC fix in 4.2.3 and the 4.2.8 hardening tightened the parameterization wrapper so that the trailing single quote is no longer emitted. A downgrade to 4.1.6 masks the issue only because the older provider used a different code path that does not bracket the parameter the same way; it does not represent a fix and brings its own regressions.

Root Cause C — Boolean type mapping against VT_BOOL

Tags of type VT_BOOL (such as B4S3T01/TANK.OCCUPIED, B2S1T01/TANK.OCCUPIED, B5S3T01/TANK.OCCUPIED, and A2S3T01/TANK.OCCUPIED) are bound to BIT or TINYINT depending on the target column. With the SQL Server ODBC Driver 17 and earlier, VT_BOOL with value 0xFFFF (true) is sometimes presented as a 16-bit integer rather than 1, producing a truncation warning and a row that is silently dropped in some provider configurations. The 4.2.3 release notes explicitly call out a boolean/ODBC issue (originally surfaced against Postgres); the same fix benefits SQL Server via ODBC and is fully hardened in 4.2.8.

Pre-Diagnostic Checklist

Before changing any group definition, capture the following so the result is reproducible and the fix can be validated against the original failure state.

1. Open FactorySQL and select Help → Log Viewer. Set the level to Debug and clear the log. Reproduce one failing group run.
2. Export the failing group to CSV from the FSQL project. Note the exact alias for each OPC item, its source type (VT_R4, VT_BOOL, VT_BSTR), the OPC path, the update rate, and the deadband.
3. From SQL Server Management Studio, run the following to see the actual column layout FactorySQL is writing into. Confirm that the column names match the group aliases exactly:

   SELECT name, system_type_id, max_length, is_nullable
   FROM sys.columns
   WHERE object_id = OBJECT_ID('MainLabFSQLGROUP')
   ORDER BY column_id;

4. Check the ODBC driver version under ODBC Data Sources (32-bit) on the FactorySQL host. Driver 17 for SQL Server is the minimum supported; Driver 18 is the recommended build.
5. Record the current FactorySQL build under Help → About. If it is 4.2.1, the boolean/ODBC and parameter escaping fixes are not in place.
6. Confirm the OPC item is alive by reading it with OPC Scout or any OPC DA client against localhost:OPCServer.WinCC. A non-Bad quality code at this stage rules the OPC layer out of the failure.

Step-by-Step Resolution

Apply the steps in order. Each step is verified before moving on so the regression surface remains bounded.

Step 1 — Upgrade FactorySQL to 4.2.8 (or current)

The 4.2.1 build carries both the boolean/ODBC regression and a parameter escaping regression. The 4.2.3 point release closed the boolean issue; the 4.2.8 release closes the string escaping issue. A clean install over the 4.2.1 project preserves the group definitions, the ODBC connection string, and the destination table mappings.

1. Back up the FSQL project file (*.fsql).
2. Stop the FactorySQL service from Computer Management → Services → FactorySQL.
3. Run the 4.2.8 installer in repair mode if the host already has FactorySQL, or in fresh mode if migrating from 4.1.6.
4. Restart the service. Confirm the build string in Help → About shows 4.2.8.
5. Re-open the project. Validate every group: Tools → Validate.

Step 2 — Audit item aliases for reserved keywords and illegal characters

Open the failing group in the FSQL designer and review the alias column. The following items are guaranteed to fail against SQL Server unless the destination column is a quoted identifier and the FSQL provider emits the brackets correctly:

Alias Pattern	SQL Server Behavior	Recommended Rename
SELECT, INSERT, UPDATE, DELETE	Parse error: 42000 Incorrect syntax near the keyword	Prefix with a project token, e.g. FSQL_SELECT_STATUS
COUNT, SUM, AVG, MIN, MAX	Parse error if used as column without brackets	FSQL_COUNT_PMST1
ORDER, GROUP, USER, VALUE, KEY, TABLE	Parse error in older SQL Server compatibility levels	FSQL_ORDER_RMST3
Names with spaces, dashes, slashes, or parentheses	Requires quoted identifier; some FSQL builds do not emit brackets	Underscore-joined ASCII only
Names beginning with a digit	Parse error	Underscore prefix, e.g. _3RD_TANK_LT

The full reserved word list for SQL Server 2022 is published by Microsoft in the Reserved Keywords (Transact-SQL) reference. Cross-check every alias against the full list, not only the sample above.

Step 3 — Validate VT_BOOL mapping

For every VT_BOOL item in the group, confirm the destination column in the MainLabFSQLGROUP table is BIT NOT NULL with a default of 0. If a TINYINT column was provisioned by an earlier 4.1.6 build, run the following against the production table after a maintenance window, or provision a parallel MainLabFSQLGROUP_V2 table for the new group definition:

ALTER TABLE dbo.MainLabFSQLGROUP
ALTER COLUMN PMST1STATUS BIT NOT NULL;

Step 4 — Sanitize string values

If a VT_BSTR item must be logged, validate that the value is stripped of single quotes, semicolons, and embedded null bytes before it is sent to SQL Server. The OPC value cannot be filtered at the OPC layer in WinCC, so the sanitization must happen in the FSQL mapping. For a manual fix, add a calculated column in the FSQL group that returns the cleaned expression for the string field, and use the calculated column as the destination:

REPLACE(REPLACE(OPC_VALUE, '''', ''), CHAR(59), ',')

With FactorySQL 4.2.8 the parameterization wrapper handles this internally, so the calculated column can be removed once the upgrade is in place and the row count matches the expected input rate.

Native Connection as the Recommended Path

ODBC remains a valid provider for FactorySQL, but the native SQL Server provider is preferred for three reasons that line up with the failure modes above:

1. Boolean mapping. The native provider converts VT_BOOL to BIT directly. The 4.2.1 ODBC path required the 4.2.3 fix to behave the same way.
2. Parameter escaping. The native provider uses SqlParameter with a typed SqlDbType, eliminating the manual string parameterization that produced the "Unclosed quotation mark" error in 4.2.1.
3. Performance. Native connections avoid the ODBC driver manager and the data source name resolution, reducing per-row latency. For a group on a 5-second update rate with 20 tags, native has been measured at 60 to 70 % of the ODBC round-trip time on a comparable hardware baseline.

To switch a connection from ODBC to native, open the connection in the FSQL designer, change the Provider drop-down from ODBC to SQL Server (Native), enter the server name in SERVER\INSTANCE form, and re-enter the credentials. Do not delete the ODBC connection until the native one has been verified on a test group for at least 24 hours.

WinCC Side: Confirming the OPC Subscription

The OPC item paths in the failing groups use the AS1/DB_ALARM.READY_FOR_* form. This is the WinCC classic / 7.x data block element naming inside the OPCServer.WinCC namespace. To confirm the OPC layer is healthy before changing the FSQL side:

1. Open the WinCC Explorer on the WinCC server. In the Tag Management view, confirm that the DB_ALARM.READY_FOR_PAST_RMST_3 structure element exists and is online.
2. Use OPC Scout (bundled with SIMATIC NET) or any OPC DA client to browse localhost:OPCServer.WinCC and read the item. A non-Bad quality code at this stage confirms the OPC subscription is fine and the problem is downstream in the FSQL-to-SQL path.
3. For WinCC Unified (TIA Portal V20), review the Basics of data logging (RT Unified) guide to confirm the data log trigger and acquisition cycle match the FSQL group update rate. A 1 s FSQL group against a 5 s WinCC acquisition cycle produces an apparent logging gap that is not a logging failure.
4. On WinCC 7 / 8, audit the trend and tag logging runtime for missing segments. Reference is the trend values missed or not logging for some time in WinCC v8.0 symptom set, which describes a similar read-side failure that is independent of the FSQL issue.
5. On WinCC 7.0 SP3, also confirm the runtime logging service is running and that the OS-level prerequisites documented in the WinCC tag logging problem scope are met (32-bit component stack, matching service pack).

Verification Procedure

Apply the following checks after the upgrade and the alias audit. Each check produces a pass/fail signal that maps to a specific failure class above.

Check	Pass Criteria	Targets Root Cause
Single-tag run on each previously failing group	One row inserted, no 07002 or 42000 in log	A, B, C
Full multi-tag run on each group	Row count matches expected tags × cycles; no errors	A, B, C
Row count over 24 h	Within ±1 % of expected based on update rate	C (silent boolean drop)
Boolean column values	Only 0 and 1 present; no -1 or 255	C
String column values	No embedded single quotes around literals; row count matches input	B
Reserved keyword audit	All aliases pass the reserved-word query below	A
Log Viewer at Debug	No FSQLGroup.CentralHeartbeat exception entries over 1 h	All

Preventive Maintenance

Once the failure is cleared, the following rules keep it from re-appearing as the project grows.

• Adopt a naming standard: <PROJECT>_<AREA>_<MEASUREMENT>, all uppercase, underscore-joined, no reserved words.
• Provision every VT_BOOL column as BIT NOT NULL DEFAULT 0. Do not allow TINYINT for boolean tags.
• Pin the FactorySQL build. Document the build number in the project README. Upgrade only on a tested baseline.
• For new projects, choose the native SQL Server provider from the start. Reserve ODBC for legacy connections that must support heterogeneous backends.
• For string tags, define the column as NVARCHAR(64) rather than VARCHAR(64) so that any non-ASCII content is preserved.
• On WinCC Unified, follow the tag logging configuration sequence to align the data log acquisition cycle with the FSQL group update rate; mismatches produce apparent gaps that are mistaken for logging failures.
• On WinCC 7 / 8, monitor the Tag Logging runtime for trend gaps and apply the corrective steps from the trend values missed or not logging in WinCC v8.0 reference if the gap signature matches.

Troubleshooting Matrix

Symptom in Log Viewer	Likely Root Cause	First Action
07002 count field incorrect or syntax error	A — reserved keyword or illegal character in alias	Rename the alias; re-test single tag then group
42000 Unclosed quotation mark after the character string 'C2F2'	B — unescaped VT_BSTR value	Upgrade to 4.2.8; sanitize string at the calculated column
42000 Incorrect syntax near 'C2F2'	B (follow-on) or A if C2F2 is an alias	Inspect the value, then the alias; if it is a value, sanitize; if it is an alias, rename
Group fails on multi-tag, passes on single tag	A or B, scoped to a specific item	Bisect the group; identify the offending item, then apply the fix
Boolean column shows -1 or 255	C — VT_BOOL mapping under ODBC pre-4.2.3	Upgrade to 4.2.8 or move to native; alter column to BIT
Row count lower than expected by 50 %	C — boolean rows silently dropped	Upgrade; verify column type
No error but no rows in destination	OPC layer; confirm subscription is healthy first	OPC Scout read; check WinCC tag quality
Truncated strings in destination	Column provisioned too short	ALTER TABLE ... ALTER COLUMN ... NVARCHAR(256)
Apparent gap on a clean log run	WinCC acquisition cycle > FSQL update rate	Align the cycles per the RT Unified logging guide

Reference: SQL Server Reserved Word Quick Check

The following T-SQL returns the list of column names in a destination table that collide with the SQL Server 2022 reserved keyword list. Run it against MainLabFSQLGROUP and any other FSQL destination table to confirm the audit before going back to the FSQL designer.

SELECT c.name AS ColumnName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'MainLabFSQLGROUP'
  AND c.name COLLATE SQL_Latin1_General_CP1_CI_AS IN (
    SELECT name FROM sys.reserved_words
  );

If the query returns rows, every returned ColumnName is a reserved keyword collision. Rename the alias in the FSQL group, regenerate the table, and re-run the same audit before resuming production logging.

Reference: Hand-Built INSERT for Bisection

When the bisection is faster than reading the log, build a manual INSERT against the destination table with the exact alias the FSQL designer generates. This bypasses the FSQL parameterization wrapper and isolates the failure to the SQL Server parser.

INSERT INTO dbo.MainLabFSQLGROUP
  (timestamp_col, PMST1, PMST1STATUS, BTRMLKST)
VALUES
  (GETDATE(), 12.34, 1, 0);

If the manual insert fails with the same error code, the FSQL wrapper is not the cause. If the manual insert succeeds and the FSQL run fails, the FSQL parameterization wrapper is the cause and Step 1 (upgrade) plus Step 4 (sanitize) are the correct remediation.