Overview

This article explains how to use the RunDynamicSQL() and GetDynamicSQLStatus() functions. These functions allow a crimson application to execute SQL statements dynamically during runtime and monitor the execution status of the database transaction.

Examples of use:

Insert tag values into a SQL table
Update an existing SQL record
Delete records
Build an SQL statement using Crimson tag values
Execute a stored procedure
Monitor whether a dynamically generated SQL statement completed successfully

APPLICABLE PRODUCTS

Crimson 3.2 Devices (build 3.2.1030 and later)
Crimson Control
SQL Query functionality

PRE-REQUISITES

Crimson 3.2 build 3.2.1030 and later
- Note: RunDynamicSQL() and GetDynamicSQLStatus() were introduced in Crimson 3.2.1030 build.
Must have established connection to SQL Query

Syntax - RunDynamicSQL()

RunDynamicSQL(statement, trackedFlag)

Parameters

statement = The SQL statement to execute

trackedFlag = Determines whether the query can be monitored using a returned handle (1 or 0)

When trackedFlag is 0, the function returns a handle that can be used with GetDynamicSQLStauts()

When trackedFlag is 1, the function will run but will not return a handle for tracking. SQLHandle will show as -1

Example:

SQLHandle = RunDynamicSQL(EXEC dbo.InsertLog1 '2026-09-12 13:45:00', 100,200,300, 0);

The returned value should be stored in an integer tag or variable

Syntax - GetDynamicSQLStatus()

GetDynamicSQLStatus(SQLHandle)

The handle returned by RunDynamicSQL() is supplied to GetDynamicSQLStatus()

Please Note:

*The SQL handle is used to track an outstanding dynamic SQL request. Once the SQL operation has completed and a non-zero status has been returned, the handle no longer provides useful status information. Therefore, calling: GetDynamicSQLStatus() again after the handle has already completed can result in a -1. This can make an otherwise successful SQL transaction appear to have failed if the application does not preserve the first non-zero result.

Example:

SQLStatus = DetGynamicSQLStatus(SQLHandle);

if (SQLHandle == 0)
{
  SQLHandle = RunDynamicSQL("EXEC dbo.InsertLog1 '2026-09-23 11:54:00',15,16,17", 0);
}
else
{
  SQLStatus = GetDynamicSQLStatus(SQLHandle);
  
  if (SQLStatus != 0)
  {
    SQLResult = SQLStatus;
    SQLHandle = 0;
  }
}

The function reports the current/completed status of the SQL operation associated with the handle

General Status Behavior

(1) = Success
(-1) = Comms Failure (can be expected when SQL trackedFlag is = 0)
(-2) = Query Pending
(-3) = SQL Error

Crimson Configuration

Steps

Step 1 - Configure SQL Queries
Step 2 - Create Program
Step 3 - Initiate Program
Step 4 - Test Functionality

Step 1 - Configure SQL Queries

Navigate to SQL Queries
Enable SQL Queries = Yes
Enter SQL Server Connection properties
TCP Port is typically 1433
For more information on configuring SQL Queries, please click the link below
Crimson 3.x: SQL Queries

Step 2 - Create Program

Navigate to Programs
Right click Programs icon and select New Program
Or Click New Program Button
Create program using RunDynamicSQL() and GetDynamicSQLStatus()
Example:
For this example, we are running the program in the background. Set Execution task to Background 1. Set External Date = Read when referenced.
Translate program when finished constructing. Right click on the program in Navigation Pane and select Translate

Step 3 - Initiate Program

For this example, I have added the program to an action button on a display page
This button is configured to run the program whenever released.
You can also add the program to the display page Actions properties. Right click on a blank space on any display page

Step 4 - Test Functionality

Load Crimson database to device using link options function
Once database is on device, use the HMI display or the Web Server to run the program
View results on your web display. For this example, we added the SQLStatus tag and the SQLStatusText tags to my display page as well as the tag1, tag2, and tag3 data being sent to the database.
Once you have confirmed success from the SQLStatus check your database to confirm the program ran properly and the appropriate tables have been updated according to your program.

Troubleshooting Tips

SQL Status = 1 (Success)

The SQL statement completed successfully.
Verify the expected records were inserted, updated, or deleted in the database.
Confirm the correct database and table were targeted.

SQL Status = -1 (Communication Failure)

Possible Causes

SQL Server is unreachable.
Incorrect server IP address or hostname.
Incorrect TCP port (typically 1433).
Firewall blocking database communications.

Recommended Checks

Verify SQL Query connection settings.
Confirm the SQL Server is running and accessible.
Test network connectivity between the Crimson device and SQL Server.

SQL Status = -2 (Query Pending)

Possible Causes

The SQL operation is still being processed.

Recommended Checks

Continue monitoring the status until a final result is returned.
Verify the SQL server is responding normally and is not heavily loaded.

SQL Status = -3 (SQL Error)

Possible Causes

Invalid SQL syntax.
Incorrect table, column, or stored procedure name.
Missing parameters or data type mismatch.
Insufficient database permissions.

Recommended Checks

Execute the generated SQL statement directly in SQL Server Management Studio (SSMS).
Verify all table names, column names, and stored procedure parameters.
Confirm the SQL user account has appropriate permissions.

Status Changes to -1 After a Previous Success

Once a SQL request has completed, the associated handle should no longer be queried. Continuing to call GetDynamicSQLStatus() on a completed handle may return -1, even though the transaction was successful.

Recommendation: Save the first non-zero status and reset the handle after processing the result.