Generic Model 603-Multiple Tags, Archive Value Triggered
Model 603 Description
Model 603 is triggered in two different ways depending on the setting of the Value Change Only property. If the Value Change Only is false (this is the default) then Model 603 is triggered by new historian tag archive values for any of the tags defined. When a new historian tag value is available in the archive for any tag, the model calls the defined stored procedure. The value does not have to change for the model to be triggered.
If the Value Change Only is true then Model 603 is triggered only by a change to consecutive historian tag archive values for any of the tags defined. When a value change is detected, the model calls the defined stored procedure. The value has to change for the model to be triggered.
The stored procedure has specific parameters that must be defined. The model passes data into those parameters and receives a message indicating success or failure. Only previous values and new values and their timestamps are passed to the stored procedure by Model 603. Historian values can also be passed into the stored procedure. Result set queries are created in SQL and the Event Manager service handles these result sets as real-time messages in Plant Applications. For more information on result sets, see Result Sets.
Model 603 Properties
The following model properties are set up using the Plant Applications Administrator:
|
Property |
Description |
|
Maximum Run Time (Seconds) |
optional: Type the number of seconds you want the model to run. The default is 0 (zero), which means the model will not time out. Typically, you will want to limit the run time of the model only if troubleshooting the model (for example, one of the stored procedures is in an infinite loop). |
|
Model Processing Group |
Used for multithreading. See the Multithreading topic for more information. |
|
Local SP Name |
Stored Procedure called when the model is triggered. |
|
Value Change Only |
True or False (stored as 1 or 0) |
|
Delay(Seconds) |
Type the number of seconds to add to the timestamp. Typically, this is used to adjust for data that falls outside the sampling window because Plant Applications cannot recognize millisecond resolution. |
|
Reserved |
|
|
Tag#1 (Optional) |
Tag#1 used to pass values into stored procedure. |
|
Tag# ... (Optional) |
Values can be retrieved for up to 96 Tags (Optional) |
Model 603 Stored Procedure Parameters
The following parameters are required at the beginning of the stored procedure that is called by Model 603. The parameter variables can be named whatever is desired, but the order must be maintained. If a string is passed in from an historian tag there is a 255 character limit on this string.
|
SQL Variable Name |
Parameter Description |
|
@ReturnStatus int OUTPUT |
Flag to indicate Success or Failure (1-Success, 0-Failure) |
|
@ReturnMessage varchar(255) |
OUTPUT Error Message to Write to Log File |
|
@JumpToTime Datetime OUTPUT |
This is used to allow you to control how far back the EventManager goes in time to look for tag changes after a reload or restart of the Event Manager. By default the Event Manager goes back 3 days and looks for tags changes. In your code you could find the latest event on your unit and set the JumpToTime to be a second after this and then have it start looking from there for new events. |
|
@EC_ID int |
Input to the Stored Procedure indicating the EC_Id of the model from the Event_Configuration table. |
|
@ReservedInput1 varchar(30) |
Reserved for future use. |
|
@ReservedInput2 varchar(30) |
Reserved for future use. |
|
@ReservedInput3 varchar(30) |
Reserved for future use. |
|
@ChangedTagNum int |
Indicates which tag of those configured for this model triggered the stored procedure to be called. The number is determined by the position of the tag in the Event Configuration wizard and ranges from 1 to n, n being the total number of tags configured for this model. For example for a model that has 10 tags configured, if the 8th tag changed causing this model to fire and this value would be set to 8. If multiple tags changed, this will contain the number of the first one in order entered in the event configuration. |
|
@ChangedPrevValue varchar(10) |
The previous value of the triggering tag. Again, the first one based on the order in the configuration if multiple tags fired. |
|
@ChangedNewValue varchar(10) |
The new value of the triggering tag. |
|
@ChangedPrevTime datetime |
The timestamp of the last value for this tag. |
|
@ChangedNewTime datetime |
The timestamp of the triggering value for this tag. |
|
Note that the following variables will be filled in the same order that the tags are listed in the model configuration. In addition, all tag values and timestamps are true archive points not interpolated values. |
|
|
@Tag1PrevValue varchar(30) |
The previous value of the first tag. |
|
@Tag1NewValue varchar(30) |
The current value the first tag. |
|
@Tag1PrevTime datetime |
The previous timestamp of the first tag. |
|
@Tag1NewTime datetime |
The current timestamp the first tag. |
|
@Tag2PrevValue varchar(30) |
The previous value of the second tag. |
|
@Tag2NewValue varchar(30) |
The current value the second tag. |
|
@Tag2PrevTime datetime |
The previous timestamp of the second tag. |
|
@Tag2NewTime datetime |
The current timestamp the second tag. |
|
@TagnPrevValue varchar(30) |
The previous value of the nth tag. |
|
@TagnNewValue varchar(30) |
The current value the nth tag. |
|
@TagnPrevTime datetime |
The previous timestamp of the nth tag. |
|
@TagnNewTime datetime |
The current timestamp the nth tag. |
Model 603 Stored Procedure Header
Here’s a simple example of a model 603 stored procedure that has 2 tags in the event configuration of this model.
if exists (select * from sysobjects where id = object_id(N'[dbo].[spLocal_Test603]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spLocal_Test603]
GO
SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[spLocal_Test603]
@ReturnStatus int OUTPUT,
@ReturnMessage varchar(255) OUTPUT,
@JumptoTime datetime OUTPUT,
@EC_Id int,
@Reserved1 varchar(30),
@Reserved2 varchar(30),
@Reserved3 varchar(30),
@ChangedTagNum int,
@ChangedPrevValue varchar(30),
@ChangedNewValue varchar(30),
@ChangedPrevTime datetime,
@ChangedNewTime datetime,
@Tag1PrevValue varchar(30),
@Tag1NewValue varchar(30),
@Tag1PrevTime datetime,
@Tag1NewTime datetime,
@Tag2PrevValue varchar(30),
@Tag2NewValue varchar(30),
@Tag2PrevTime datetime,
@Tag2NewTime datetime
AS
/*
--Use this to debug your stored procedure.
Insert into local_debug (time,msg)
values (getdate(),(convert(varchar(10),@ChangedTagNum) + ' ' + convert(varchar(30),@ChangedNewValue) + ' ' + convert(varchar(30),@Tag1NewValue) + ' ' + convert(varchar(30),@Tag2NewValue)))
*/
-- Initialize variables.
Select @ReturnStatus = 0 --Failure
Select @ReturnMessage = '' –-Return a failure message here
/*
--Interrogate the incoming tag values and decide what to do.
-- You can use result sets to send messages to create or
-- update events or any other message type.
-- For example:
If @ChangedNewValue = '3.0' and @ChangedTagNum = 1
Begin
...
end
*/
-- Set output values appropriately.
Select @ReturnStatus = 1 -–Success
Select @ReturnMessage = ''
Return
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
/*
--Make sure to do this so the stored procedure will run!
*/
GRANT EXECUTE ON [dbo].[spLocal_Test603] TO [comxclient]
GO
See Also