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