A recent post was asking how to set up a two-way port with an orchestration to create acknowledgements.
- What we did is in the configuration tool, we set the properties for the MSH 3.1 to the following:
so that the pipeline component will only auto generate a NAK in case of a bad message.
- We did not want a CA generated stated that we got the message (with or without data errors), we just wanted to know if it was valid or not, hence MSH15=NE.
- The MSH 16=ER, we only wanted it to create an NAK if there were errors.
- Then in the orchestration we have a filter only looking for BTAHL7Schemas.ParseError==false (so only good HL7 messages trigger the orchestration).
- Then in the orchestration we create our positive ACK (setting MSA 2 to AA) and send it back; completing our two-way port in the orchestration.
- We also have a Dump send port that is filtered on BTAHL7Schemas.ParseError==true and the pipeline is set to DefaultPipelines.PassThruTransmit (since the HL7 DASM could not successfully parse it, it still resides in its text blob form), where the original HL7 message could be looked at futher for troubleshooting purposes and not have a suspended message in the message box.
- We also promoted the MSA 2 and created a filter where we could capture the autogenerated NAK (MSA_2!=”AA”). This allowed us to look at both the bad message and a copy of the NAK that was sent back to the originating system.
This fulfills our requirements to send back an ack, but then the ack gets generated either by the pipeline component (NAK) or by our orchestration (ACK).
I want to thank my friends in Portugal for encouraging me to clarify this blog entry.
Comments Off
A request came to me asking how to create a stored procedure call with no arguments.
Here is the steps for the following example:
Run the following script against the database:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[example]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[example]
GO
CREATE PROCEDURE dbo.example
AS
BEGIN
SELECT ’Hello’ [Result]
FOR XML RAW,xmldata
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Create the new BizTalk Project
Generate the stored procedure schema (make sure that you add xmldata for example ‘for xml raw, xmldata’)
Right click and generate the xml document, it should look like this:
In a text editor remove the CRLF and replace the quotes with ‘ \” ‘
Create a new variable (I called it tempXML) and choose a .NET class and choose XML Document
In your message assigment, use the following code:
That is it, you have completed the creation of a blank stored procedure call.
Comments Off
We had a power outage and our working BizTalk machines seemed to be unaffected. However when I tried to deploy any solution, I got the following error: ‘Failed to load msxmlsql.dll’
The only article I found was this.
I applied the fix to my BizTalk machine, which did not fix the issue. Before calling support, I decided to reinstall everything, thinking that the power outage corrupted the file. I reinstalled everything, including the Operating System. Going through the install, I got to the Group component on the configuration, it would fail.
After a couple of days on the phone with MS Support, the SQL Support directed me that the msxmlsql.dll and msxmlsql.rll needed to be located in the SQL Server machine, not on the BizTalk Server. (The dll and rll doesn’t need to be located in the particular instances, but just the default installation of SQL Server).
After placing the file in the correct location, the installation seemed to be working correctly. I had to pull the files from SP4 install folders and place them in the correct location as per the KB article.
Hope that others don’t have to spend their time beating their head against the wall like I did.
(Which reminds me: Banging your head against a wall uses 150 calories an hour.)
Comments Off
This is a gotcha that is hard to figure out, especially if you are trying to install and don’t have the documentation in front of you, because it is trying to install it!
In the documentation, it states:
Log on using an account that is a member of the BizTalk Server Administrators group.
Note This user account must also be a member of the Administrators group on the SQL Server in which you will store BTAHL7 data.
This is normally an issue when you install it and it states: ‘The Specified account “###/###” is
invalid. Error code: Logon Failure: unknown user name or bad password.’
Comments Off