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

IN THIS ARTICLE 

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

1. Step 1 - Configure SQL Queries
2. Step 2 - Create Program
3. Step 3 - Initiate Program
4. 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.

• 

ADDITIONAL INFO 

Crimson 3.x: SQL Queries

Crimson 3.x: SQL Synchronization – HMS Support Portal

How To: Connect Crimson to Microsoft SQL Server – HMS Support Portal

How To: Connect Crimson to Microsoft Azure SQL Database – HMS Support Portal

For further support, please click the following link and create a support ticket:

HMS Support Portal

© HMS Networks AB 2026

© HMS Networks AB 2026