Using a SQL Procedure to "Preprocess" your data

<< Click to Display Table of Contents >>

Navigation:  Exporting HL7 >

Using a SQL Procedure to "Preprocess" your data

*Screenshots are from the UltraPort SQL Schema Engine version 5.2.3

 

 

Outbound Processor Properties Window

Outbound Processor Properties Window

 

Adding a SQL Stored Procedure to an Outbound Processor

Adding a SQL Stored Procedure to an Outbound Processor

 

Executing a Stored Procedure (Outbound)

You can optionally configure your outbound processors to execute a SQL Stored Procedure before it scans the <prefix>_HL7Data table in your schema for outbound messages to export. If this feature is enabled it will act in concert with the database polling interval you select when creating your outbound processor profile.

Example: If you have a stored procedure called CreateMyHL7Messages in your schema database which extracts data from YOUR MS SQL Tables and then populates HL7 message data in your UltraPort SQL Schema you can configure it here. If you also selected a database polling interval of 10 seconds your processor will behave like so:

1.Every 10 seconds the processor will remotely execute the CreateMyHL7Messages (note that you can name it anything you like) stored procedure. If an error occurs it is logged. 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.

2.THEN the outbound processor will scan the <prefix>_HL7Data table for outbound HL7 messages which are unprocessed and need to be assembled.

3.IF outbound messages are detected the processor will process them to completion by assembling and exporting the messages. It will then IMMEDIATELY and continuously repeat the process (return to item 1 above) until NO outbound messages are detected.

4.IF NO outbound messages are detected the processor will wait 10 seconds and then begin again.

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 and can be named whatever you like.

The procedure must take NO parameters.

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).

 

Click to ExpandExample of a Stored Procedure (Click to Expand)