Executing a Stored Procedure (Inbound)

You can optionally configure your inbound processors to execute a SQL Stored Procedure AFTER it successfully imports each HL7 message into the schema database. You might find this method easier to implement than using database triggers (NOT recommended by HermeTech). Using this method the Schema Engine will never execute the stored procedure until each HL7 message is completely and safely loaded into your Schema Data Tables.

Example: If you have a stored procedure called ProcessMyHL7Message in your schema database which extracts data from your UltraPort SQL Schema you can configure it here. If enabled your processor will behave like so:

•Your INBOUND processor will detect a new HL7 message and import it into the database.

•THEN the processor will execute your stored procedure passing it the [MessageID] value from the <prefix>_HL7Data Table. Using that Key value your stored procedure then does whatever you need to extract the HL7 data from the segment tables.

•IMPORTANT: IF your stored procedure generates a SQL Server Error that error message will be entered into the [StatusMessage] column of your <prefix>_HL7Data table and the [Processed] column value will be set to 999.

Rules for your Stored Procedure: There are a few rules regarding your stored procedure, they are:

•The stored procedure must exist in your schema database

•The procedure must take ONE parameter (of type Varchar or equivalent). The parameter passed will be the [MessageID] column value from your <prefix>_HL7Data table.

•IMPORTANT NOTE: When executing your stored procedure the SQL Schema Engine will use a "Command Timeout" equal to FIVE (5) times the value of your Connection Timeout setting in your Schema Profile. This means that if your connection timeout property is set to 30 seconds your command timeout is 150 seconds. Your stored procedure MUST complete processing within that period or a timeout error will be returned to the Schema Engine.

•Remember that the SQL or Domain user account used by the UltraPort MS SQL Schema Engine to connect to the database (see Database Schemas) must have the proper rights to EXECUTE the stored procedure (whether running locally or as a MS Windows Service).

Recommended Practices for your Stored Procedure:

•If your stored procedure is the final/only process which will be processing the HL7 messages in the database we STRONGLY recommend that your procedure update the [Processed] column in your <prefix>_HL7Data table to a NON-ZERO value. There are many Schema Engine processes (like database maintenance) which use the value in the [Processed] column to determine if the client (that's you) has completed their HL7 mission with each message.

•There's a column in your <prefix>_HL7Data table called [StatusMessage].  You might find that field handy and have your stored procedure update that field with a "Procedure Completed Successfully" message (or similar) or Error messages, etc.

Remember that the user account running the UltraPort MS SQL Schema Engine must have the proper rights to EXECUTE the stored procedure (whether running locally or as a MS Windows Service).