Description of Result Sets

Result Sets allow you to add real-time messages to model-based custom SQL stored procedures and to Plant Applications' variable stored procedure calculations. These result sets will allow you to safely update the Plant Applications database and notify the Plant Applications client of the changes made.

Note: Run the spServer_CmnShowResultSets stored procedure to see what fields are in the result sets.

Plant Applications employs a messaging system and result sets allow you to use this messaging system. The Proficy Router service (called Message Bus in earlier versions) acts as the postal service. The Plant Applications Client and Proficy Server both send and receive messages. The Database Manager service handles all database updates. Instead of directly updating the Plant Applications database with SQL Insert and Update statements in a custom stored procedure, result sets use the Plant Applications messaging infrastructure to make the database changes and allows the clients that are currently running to see these database changes in real time. This method of issuing a message is necessary to notify the client that a change has been made to the database.

The following table lists the result set types available and the appropriate number to use when invoking a result set. All result sets require that the first column is one of the following values in the Type column.

Type	Name	Description
1	Production Events	Create, update, and delete event records.
2	Variable Values	Create, update, and delete Variable data records.
3	Product Change	Create, update, and delete Product Changes.
5	Downtime Events	Create, update, and delete Downtime records.
7	Sheet Columns	Create, update, and delete Sheet Column records.
8	User Defined Events	Create, update, and delete User Defined Event records.
9	Waste Events	Create, update, and delete Waste records.
10	Production Event Details	Create, update, and delete Production Event Detail records.
11	Production Event Components	Create, update, and delete Production Event Component records.
12	Genealogy Input Events	Create, update, and delete Genealogy Input Events.
13	Defect Details	Create, update, and delete Defect Detail records.
14	Historian Write
15	Production Plan
16	Production Setup
17	Production Plan Starts
18	Production Execution Path Unit Starts
19	Production Stats
20	HistorianRead Result Set
21	Non-Productive Time	Create, update and delete non-productive time records.
50	Output File Data Export	Create output files for exporting data.
51	Return Parameters	Allows special parameters to be returned from spLocals

All Generic Models that used to trigger a Stored Procedure that can be used for creating Result Sets start with the same 2 parameters as outputs at the beginning of the stored procedure. The first parameter is the Status indicating if the Result Sets should be executed. If this is set to a 1, Result Sets are executed by the Event Manager Service and the Message Output is logged in the Event Manager log file. If no Message Output is desired, set the message parameter to a blank value. If the first parameter is set to 0, result sets are not performed and the Message Output is logged in the Event Manager log file.

General Methodology used for Result Sets:

Gather data for the Result Set.
Check to see if the record already exists.
Select the Result Set data.
Set the Return Status Value to a 1 to execute Result Sets or set the Return Status Value to a 0 to ignore Result Sets and to report Errors to the Event Manager Service for Model based Result Sets.

Techniques used in creating Result Set Stored Procedures:

Use SQL temporary tables to hold data.
Use the right number of columns for the Result Set.
Set NoCount On to turn off count messages.
Use Date formatting
Use a Custom Plant Applications User to aid in troubleshooting problems.
WARNING: Avoid calling nested spLocal stored procedures where each respective stored procedure implements Result Set messages of the same Message Type. Implementations of this complexity create opportunities for SQL deadlocking and improper sequence of execution resulting in incorrect data being written to the database. Be sure to perform thorough performance testing, validation and implement error handling.

Testing Result Set Stored Procedures

No Stored Procedure should be attached to a model or variable without first running it manually to see if anything comes to the screen unexpectedly. Anything that comes to the screen will appear to be a result set to the caller.
Test by running the Stored Procedure manually from SQL Server Management Studio. Supply a set of parameter values that match the stored procedure input parameters. Then use the SQL Execute statement to run the stored procedure.
It is important that you don't forget the @ symbols in front of variables within your stored procedures
An example would be, Select X = 7 instead of, Select @X = 7. This looks like a result set to the calling code in the Event Manager or Calculation Manager. This Select X = 7 the calling code would think it has a SheetColumn result set coming and would start accessing columns that are not present.
Confirm the output in the Management Studio is correct. Make sure there are no extra messages being sent out that may be interpreted as result sets. Sometimes extra Select messages may be used to check on specific values while troubleshooting a Result Set Stored Procedure such as:
Select @X
By testing it, you may notice that there are Select messages that still need to be removed prior to using this Stored Procedure in the production environment.
When first activating the Stored Procedure in the production environment, check the Event Manager log file for messages if this is a Result Set Stored Procedure driven by a model. If it is driven by a Plant Applications Variable, then check the Calculation Manager log file for messages.
When first activating the Stored Procedure in the production environment you can put the Database Manager service in Debug Mode and check the log file for additional error messages.