Resolving FSQL Multiple Recordsets for ControlLogix

On the operator display, the failure looks like a partial order: some strings and integers update, rows disappear, or data from one result set lands in fields intended for another. Start here: the PLC transfer is not the first fault. The mismatch sits between a stored procedure that emits five variable-length recordsets and an interface that needs one deterministic data contract.

Stop trying the wrong fixes

• Do not concatenate every value into one column. That removes the boundaries between columns, rows, and recordsets. You then need another parser, delimiter escaping, null handling, and type conversion before ControlLogix can use the data.
• Do not add display objects to RSView. More string and integer objects may accommodate today’s order, but they preserve the opaque VBA dependency and fail when row counts change.
• Do not wrap the procedure blindly. A wrapper helps only when it produces a result shape that FSQL can consume. Calling the existing procedure from another procedure does not automatically turn five recordsets into one.
• Do not move the same browsing logic into a SQL CLR function merely to change where it runs. That relocates the custom parser from the client to SQL Server without simplifying the interface contract.
• Do not change PLC tag mapping first. A tag map cannot correct missing recordset identity, incompatible column types, or variable row boundaries.

Identify the real interface fault

The major stored procedure runs every 10 seconds and returns five recordsets with different row counts. RSView/VBA works because procedural code can advance through each result set, iterate its rows, inspect columns, and write individual values to display objects.

An SQL-to-PLC interface works best with a stable table: known columns, known types, explicit record identity, and a bounded row count. ControlLogix tags also have fixed definitions. They cannot infer which recordset a row came from or expand automatically when a query returns additional rows.

Choose a contract FSQL can expose

Make the architectural choice before writing more logic.

• Use one combined resultset when the five recordsets have identical or compatible columns. Add a recordset discriminator and, where row order matters, a row index. Convert columns to compatible SQL types deliberately; do not rely on implicit conversion.
• Use five separate interface procedures or queries when the schemas represent different entities. This is usually clearer than forcing unrelated order headers, details, operations, or attributes into one sparse table. The existing standard procedure can remain unchanged if the database permits new interface objects.
• Keep procedural middleware when the existing procedure cannot be changed or wrapped, its outputs are heterogeneous, and FSQL cannot address every result independently. RSView/VBA is one implementation, but the lasting requirement is a maintained component that understands all five resultsets.

Recordset position alone is a weak contract. Preserve a source identifier, business key, row index, row count, and validity state wherever the receiving tag model needs them. Define maximum PLC array lengths from the largest approved order, not from the most recent test result.

Build the wrapper that actually works

1. Document all five outputs from the existing stored procedure. Record each column’s name, SQL type, nullability, business key, and maximum expected row count.
2. Compare the schemas. If every row can share one column layout without losing meaning, select the combined-resultset design. If not, split the interface into separate result shapes.
3. Create a new interface procedure rather than altering the locked procedure, provided database change control permits a wrapper. The wrapper must reshape the data, not merely call the original procedure.
4. For a combined design, append columns such as a recordset discriminator and row index. Use explicit conversions for compatible fields. Reject or separately expose columns whose meanings or types conflict.
5. Test the SQL capture mechanism against the real five-result output. Temporary-table approaches require compatible target schemas. Nested procedure execution can also be restricted by the capture method already used inside the called procedure; test that path before committing the design.
6. Return one final, ordered resultset to FSQL. Sort by the discriminator, business key, and row index so execution-plan changes cannot reorder PLC destinations.
7. Add transfer metadata to the interface result: an order identifier, the count for each logical group, and a validity or completion indicator. Generate the completion indication only after the complete order payload is ready.

If the database team rejects new interface procedures, stop changing the PLC. The remaining practical design is middleware that consumes all recordsets and publishes a fixed PLC-facing structure.

Map variable rows into fixed CLX tags

Create a bounded PLC structure for each logical group. Store the valid row count separately from the array. Reject an order when its returned count exceeds the allocated capacity; never truncate silently.

Stage the incoming values before making them active. Write the order identifier, counts, strings, integers, and row data into staging tags. Clear unused array elements so values from a longer previous order cannot survive. Mark the staged order complete only after every required group has transferred, then let PLC logic copy or accept it as one transaction.

Use stable business keys when later data must refer to earlier rows. Array position is acceptable only when the wrapper supplies deterministic ordering and the row index is part of the agreed contract.

Verify the complete transfer

1. Run the stored procedure directly and record the row count from each of its five recordsets.
2. Run the wrapper or interface queries for the same order. Reconcile counts, keys, nulls, and converted values with the original output.
3. Trigger one FSQL cycle and confirm that the order identifier and all group counts reach their staging tags.
4. Check the first and last valid row in every group. Then inspect the first unused array element for stale data.
5. Test zero-row, one-row, and maximum-approved-row cases for each group. Test an over-capacity result and verify that the PLC rejects it instead of accepting a partial order.
6. Repeat across consecutive 10-second calls. Confirm that an unchanged order is not duplicated and that a new order cannot become active before its complete payload arrives.

Watch SQL execution duration as well as transfer status. A procedure that approaches or exceeds the 10-second polling interval can overlap the next request or expose stale state, depending on the scheduler. Measure the real execution time and inspect the interface logs before changing timeouts.

FAQ