Generic Model 602-Interval Triggered
Generic Models 600, 601, 602, and 603 are used to trigger the calling of a custom SQL Stored Procedure. The custom Stored Procedure allows the flexibility to use Result Sets to create, update, and delete data in the Plant Applications database. See the section on Result Sets for further explanation of their features.
Model 602 Description
Model 602 is triggered by the timing interval defined in the model parameters. When the interval elapses, the Model calls the defined Stored Procedure. 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. Result Set queries are created in SQL and the Event Manager Service handles these Result Sets as real time messages in Plant Applications. Result Sets are defined further in another section.
Model 602 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. |
TINT:Interval (Minutes) |
The timing interval used to trigger the calling of the Stored Procedure. |
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 |
|
Reserved |
|
Reserved |
|
Local SP Name |
Stored Procedure called when the model is triggered. |
Model 602 Stored Procedure Parameters
The following parameters are required at the beginning of the Stored Procedure that is called by Model 602. The stored procedure must have the same input and output parameters as defined and the parameter variables can be named whatever is desired but the order must be maintained. Up to 50 Historian Tag values can be passed into the Stored Procedure.
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 |
@EC_Id int, |
Input to the Stored Procedure indicating the EC_Id of the model from the Event_Configuration table. |
Sample Code
if exists (select * from sysobjects where id = object_id(N'[dbo].[spLocal_TomCalcDependRedo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spLocal_TomCalcDependRedo]
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
create procedure dbo.spLocal_TomCalcDependRedo
@status int output,
@errormsg varchar(255) output,
@ecid int
as
declare
@var_id int
select @status = 1
select @errormsg = ''
select @var_id = null
select @var_id = min(var_id) from tomtest
if (@var_id) is null
return
select 2,@var_id,0,entry_by,0,result,convert(varchar(30),result_on),1,1
from tests where (var_id = @var_id) and (result_on between '10/27/00 10:00' and '10/27/00 16:00') and (result is not null) and (canceled = 0)
select @var_id = null
select @var_id = min(var_id) from tomtest
if (@var_id) is null
return
select 2,@var_id,0,entry_by,0,result,convert(varchar(30),result_on),1,1
from tests where (var_id = @var_id) and (result_on between '10/27/00 10:00' and '10/27/00 16:00') and (result is not null) and (canceled = 0)
select @var_id = null
select @var_id = min(var_id) from tomtest
if (@var_id) is null
return
select 2,@var_id,0,entry_by,0,result,convert(varchar(30),result_on),1,1
from tests where (var_id = @var_id) and (result_on between '10/27/00 10:00' and '10/27/00 16:00') and (result is not null) and (canceled = 0)
select @var_id = null
select @var_id = min(var_id) from tomtest
if (@var_id) is null
return
select 2,@var_id,0,entry_by,0,result,convert(varchar(30),result_on),1,1
from tests where (var_id = @var_id) and (result_on between '10/27/00 10:00' and '10/27/00 16:00') and (result is not null) and (canceled = 0)
select @var_id = null
select @var_id = min(var_id) from tomtest
if (@var_id) is null
return
select 2,@var_id,0,entry_by,0,result,convert(varchar(30),result_on),1,1
from tests where (var_id = @var_id) and (result_on between '10/27/00 10:00' and '10/27/00 16:00') and (result is not null) and (canceled = 0)
select @var_id = null
select @var_id = min(var_id) from tomtest
if (@var_id) is null
return
select 2,@var_id,0,entry_by,0,result,convert(varchar(30),result_on),1,1
from tests where (var_id = @var_id) and (result_on between '10/27/00 10:00' and '10/27/00 16:00') and (result is not null) and (canceled = 0)
select @var_id = null
select @var_id = min(var_id) from tomtest
if (@var_id) is null
return
select 2,@var_id,0,entry_by,0,result,convert(varchar(30),result_on),1,1
from tests where (var_id = @var_id) and (result_on between '10/27/00 10:00' and '10/27/00 16:00') and (result is not null) and (canceled = 0)
select @var_id = null
select @var_id = min(var_id) from tomtest
if (@var_id) is null
return
select 2,@var_id,0,entry_by,0,result,convert(varchar(30),result_on),1,1
from tests where (var_id = @var_id) and (result_on between '10/27/00 10:00' and '10/27/00 16:00') and (result is not null) and (canceled = 0)
select @var_id = null
select @var_id = min(var_id) from tomtest
if (@var_id) is null
return
select 2,@var_id,0,entry_by,0,result,convert(varchar(30),result_on),1,1
from tests where (var_id = @var_id) and (result_on between '10/27/00 10:00' and '10/27/00 16:00') and (result is not null) and (canceled = 0)
select @var_id = null
select @var_id = min(var_id) from tomtest
if (@var_id) is null
return
select 2,@var_id,0,entry_by,0,result,convert(varchar(30),result_on),1,1
from tests where (var_id = @var_id) and (result_on between '10/27/00 10:00' and '10/27/00 16:00') and (result is not null) and (canceled = 0)
delete from tomtest where var_id = @var_id
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
GRANT EXECUTE ON [dbo].[spLocal_TomCalcDependRedo] TO [ComXClient]
GO
See Also