TN Hist212 Insert and Updating Wonderware Historian Values from a Stored Procedure

 

Description

One of the strongest features of the Wonderware Historian is the tight integration with Microsoft SQL Server.  The Wonderware Historian allows users to send Transact-SQL statements to the Runtime database, it executes these statements using the various Historian sub-systems.  The below stored procedure provides an example of this. 

 

• Author: Michael Walker
• Published: 04/21/2015
• Applies to:
  • Wonderware Historian
  • ​Microsoft SQL Server

 

 

Details

The stored procedure below performs the following functions within the Wonderware Hsitorian. 

 

1. Stored procedure accepts StartDate, EndDate, Tagname and Value Parameters.
2. Based on the parameters, the Stored Procedure checks if any values exist in the Runtime database.
3. If no records exist, the stored procedure inserts data into the Historian.
4. I records do exist, the stored procedure updates data in the Historian.

 

In general the stored procedure provides an example of the following SQL Server concepts.

 

1. Declaring variables and setting variable values.
2. IF…ELSE Transact-SQL
3. BEGIN… END Transact-SQL
4. Insert / Update Transact-SQL
5. Building and Executing Dynamic Transact-SQL

 

Below is a picture of the stored procedure. 

Below is the stored procedure in text form.

 

ALTER PROCEDURE [dbo].[sp_SubmitHistorianDataEntry]

  @startdate as datetime,
  @enddate as datetime,
  @tagname as nvarchar(50) ,
  @value as nvarchar(50)

AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @HistorianCount AS INT
    DECLARE @sql AS NVARCHAR(MAX)

      --CHECK IF RECORDS EXIST WITHIN THE HISTORIAN

      SET @HistorianCount = (SELECT COUNT(*) FROM Runtime.dbo.History WHERE TagName = @tagname AND DateTime >= @startdate AND DateTime <= @enddate AND wwRetrievalMode = 'Delta' AND Value IS NOT NULL)

     --IF NO RECORD COUNT, INSERT INTO THE HISTORIAN. 

     IF @HistorianCount = 0
        BEGIN
        
            INSERT Runtime.dbo.History (DateTime, TagName, Value, QualityDetail, wwVersion) 
            VALUES (@startdate, @tagname, @value, 192, 'LATEST') ,
                   (@enddate, @tagname, NULL, 192, 'LATEST')                
        END

    --IF RECORD COUNT, UPDATE THE HISTORIAN.
    ELSE IF @HistorianCount > 0
        BEGIN
            SET @sql = 'SELECT * FROM OPENQUERY(INSQL, ''UPDATE History ' +
                                    ' SET Value = ' + @Value + ', QualityDetail = 192 ' +
                                    'WHERE TagName = "' + @tagname + '" ' + 
                                    'AND DateTime > "' + CONVERT(VARCHAR(50), @startdate, 120) + '" ' +
                                    'AND DateTime < "' + CONVERT(VARCHAR(50), @enddate, 120) +'"  ' + '''' + ')'  
            EXECUTE(@sql)
        END    
END