ConnectR Interface Troubleshooting

Debug Procedure

1. Ensure Error Message Definitions are loaded into ConnectR database. If Error Message Definitions are not loaded into ConnectR database there is a database script available to load.
2. Copy command from Target Message Grid into SQL Query window


3. Remove beginning and end parenthesis
4. Edit/Find ‘ -> Replace with ‘’(single quote with double single quote)
Note that SQL does not process double quotation marks. Find Single Quotation Mark and replace with two Single Quotations Marks.

5. Insert a comma after the name of the stored procedure

6. Add a single quote before the first value being passed to the stored procedure and add a single quote after the last value

7. Run sp_idxgenspcall, sp_name (for example, FileApppointment_CMS), with the remaining SQLCommand encapsulated in single quotes (both at the beginning and end). Also, remember to insert a comma after the name of the stored procedure. Before you execute this query, make sure the results are set to 'Results to Text'.
8. Us the sp_idxgenspcall query at the beginning of the text.
9. Query Options / select Text under Results.
10. Copy text results & paste into new query window


11. Add begin tran (at the beginning of the script) / rollback tran (to the end of the script) to query. Execute query and utilize this information to root cause the error.
12. If 0 is not returned, then continue to query the stored procedure call by Highlight the Stored Procedure / Right Click Scripts Stored Procedure as / Create to / New Query Editor Window, this will allow you to review the call procedure and what the expected value is for each field, you can also search the call for the error codes.

Example

In the example shown below, required fields were present in the Stored Procedure called by the mapping, but were not present in the current Interface mapping. Thus the outcome was that the target message had to be changed to include the additional fields.

Hint

Cycle systems after changing a mapping

Notes

If you are encountering SQL formatting, syntax, or other errors when trying to run the queries check the following:

1. There should be a comma after the name of the stored procedure. For example, the beginning of the query should read, "sp_idxgenspcall FileAppoinment_CMS,"
2. The text immediately following this comma should be enclosed in a single quotation, and the corresponding single closing quotation should be the last character input as part of the query.
3. Make sure the results of the query in step 5 are sent to text, as this information is needed for step #7.