Move from Spaces.live.com broke all specific blog links. I will attempt to recreate any internal pointer links. Though the “transfer process” was supposed to keep “ALL” links intact. Apparently they didn’t test this.
Share on FacebookMonthly Archives: September 2010
Updated AutoDeploy
Added Daniel Turnell and Brian Dineen as a developer/coordinator.
Updated with Exception handling and convertered some static class methods to extension methods.
http://autodeploy.codeplex.com (still pre-release)
How to Retrieve Batch information by Agreement Name
–Not completed, but shows filter values.
declare
@AgreementName varchar(255)
select
@AgreementName = N’Acme To EDIGUIDANCE’
select
–a.*,
cast(batchdescription.FilterBytes as VARCHAR(MAX)) ,oa.*,
batchDescription
.*
from
[tpm].[BatchDescription] batchDescription left outer join
tpm
.OnewayAgreement oa on oa.Id = batchDescription.OnewayAgreementId
left outer join tpm.agreement a on oa.SenderId = a.SenderProfileId –and a.ReceiverProfileId = oa.ReceiverId
–where a.Name = @AgreementName
Our Binary hidden Filter properties …WALLAH SOA EDI
Getting back on Autodeploy late October since its so close to done.
I was really close to finishing it. It busted in x64 bit testing.
Here are the screenshots from when I left it in May. This was a runthrough of the ESB 2.0 config. I built it so I could hit a site and let their IT staff install everything needed with guided help. Its really helpful if you have WAY to much stuff to install.
Shows the user all the commands
autodeploy.codeplex.com
Possible Commands:
///Command – doesRegistryEntryExist – to see if a registry entry exists (example:SOFTWARE/Microsoft/UDDI)
///Command – isInstalled – to see if they have the proper installations (example:SQL Server)
///Command – isServiceInstalled – to see if they have the proper service installed (example:IISADMIN)
///Command – Message
///Command – runCommandLine (EXE, COM, PS1, EXE, MSI, ZIP/UNZIP, Copy Move, Mappings can all be done here.)
///Command – runSQLCommand (Run Command From XML Config)
///Command – runSQLScript (SQL in file)
///Command – runIfServiceExists
///Command – runIfProgramInstalled
///Command – runIfRegistryExists
///Command – addXElement
///Command – replaceOrAddXElementIfMissing
///Command – replaceOrAddXAttributeIfMissing
///Command – doesRegistryEntryExist – to see if a registry entry exists (example:SOFTWARE/Microsoft/UDDI)
///Command – isInstalled – to see if they have the proper installations (example:SQL Server)
///Command – isServiceInstalled – to see if they have the proper service installed (example:IISADMIN)
///Command – Message
///Command – runCommandLine (EXE, COM, PS1, EXE, MSI, ZIP/UNZIP, Copy Move, Mappings can all be done here.)
///Command – runSQLCommand (Run Command From XML Config)
///Command – runSQLScript (SQL in file)
///Command – runIfServiceExists
///Command – runIfProgramInstalled
///Command – runIfRegistryExists
///Command – addXElement
///Command – replaceOrAddXElementIfMissing
///Command – replaceOrAddXAttributeIfMissing
…. Ill post more on it next month.
Learned a different kind of EDI pattern today.
(Jim Dawson) was available and showed a really quick and easy way to streamline some of the things we are taught as BT developers.
Centralized repository for all the messages to be sent to a VAN. Rather than use EDIRECIEVE to turn it back into a MSXML and route based on party (since there could be 200 differing parties in the repository that all need to go to the same van). Send the files by funneling them through using PassThru rather than EDI then just use AS2 Send rather than AS2EDI send and base context routing on the recieve port rather than party.
He is re-wrting this up. Ill host the link when it up.
Thanks Jim! Great learning experience.
Sandcastle with .Net 4.0
This Builds Help documentation for you. (Based on your Commenting)
I have been asked a few times about the old one, this tool is way easier to use so I figured I’d take out a few minutes to show an example.
How to enhance your documentation via XML documentation over the standard commenting.
http://www.ewoodruff.us/shfbdocs/Index.aspx?topic=html/b772e00e-1705-4062-adb6-774826ce6700.htm(Seems to work fine up to 3.0 and 3.5 SP1. However it error-ed on my EF4 library and anything that references .net 4.0. I think this may be just as easy as adding them to the Documentation Sources as a new source.)
Step 1:
Download
http://sandcastle.codeplex.com/releases/view/13873 (sandcastle main) I had this installed. Though I am unsure if you need it to run the Sandcastle Help File Builder.Download
http://shfb.codeplex.com/releases/view/29710 (sandcastle external tool)
Direct Link:
Sandcastle Help File Builder Installer<http://shfb.codeplex.com/releases/view/29710#DownloadId=99676>
Start the File Builder (From Program Files)
Select New Project:
This will bring open your documents directory and just name it something.
From the Project explorer window on the tool:
Then Add the EXE’s or DLL’s
I used to have to add the .net 4 directory, but now there is a selection for it.
Once loaded you will see them here:
States output is here:
Compiling c:UsersMarkDocumentsHelpWorkingOutputDocumentation.chm
But was found here:
Compiling c:UsersMarkDocumentsHelpDocumentation.chm
Running:
Final Output:
EDIGUIDANCE the hunt for all the settings.
Ack info should be able to be polled (or pulled) from
tpm.x12protocolsettings .
BT2010 EDI TPM Deep Dive
I was given a challenge. In the EDIGUIDANCE for ESB 2.1 we need to be able to grab, EDI , EDIFACT, AS2 Information outside of a pipeline based on some attributes from something like a PO or EligiblilitySchema before we actually put them into the MIcrosoft EDI schemas. I was told this wasn’t possible and we needed to store the data in 2 locations. This made me want to see if I could somehow read out the binary data so we could keep all the settings in the TPM rather than in 2 places.
So I went ahead and created a couple of parties:
I wanted to set data on specific parts of forms in order to see where this data landed.
I needed to set up some new profile information (This is much better than previous versions, to use Jim Dawsons’s words: Biztalk shouldn’t be the center of the universe in an EDI scenario. In the pats you would have right clicked on a party and selected EDI PROPERTIES or AS2 PROPERTIES.
After I setup my two parties
ACME CORPORATION and EDIGUIDANCE I had to create 2 sets of Agreements, one for x12 and the other for AS2
Since you can have multiple protocols on each party, you need to select these out (2010 allows a 1 party to many protocols situation, allowing much more freedom when configuring the server.) I had used the default protocol names for Both EDIGUDIANCE and ACME CORPORATION. Now it would make you think that both protocols are the same since they have the same name X12_Settings_1, but this is incorrect. The protocol set belongs to the party and the default name is X12_Settings_1, these are essentially completely seperated configurations that just both happen to have a lazy developer who used the default name for both. 
I stepped through trying to find where I set the ISA5, ISA6, ISA7 and ISA8, since these were the values I needed to be able to reach into the database and grab.
After setting the values in both directions I needed to capture what I had so I knew what to look for.
After I went into what I thought was where teh EDI data was stored to find almost all those Databases empty.
Q:I am a little concerned since these tables arent used anymore why could they be there?
A:My best guestimate is that these remained for BT EDI party Upgrades from older systems.
So I started some pretty wild queries to see if anything had the name ISA5 in it. THere were a few in the EDI setup above, but also I found them in the
Tables that started with TPM.
I found where I had set my 850 override:
Th partnership I had setup:
This allowed some Joins against the TPM.PARTNER database. This is when I realized I had really normalized standard tables to deal with. Unlike the past. I had access I wasn’t able to have before.
Low and behold THe puzzle started to come together.
Which brought me to the TPM.BusinessIdentityTable (Jackpot!)
How to retrieve EDI Partner Information for context based EDI routing Part 2
Add to BizTalkMGMTDB
BASED ON AGREEMENT NAME
STORED PROC : For SINGLE X12 and SINGLE AS2 Per Party->Party
— =============================================
— Author: <Mark Rowe>
— Create date: <September 17, 2010>
— Description: <EDIGUIDANCE Addin>
— =============================================
CREATE
PROCEDURE [GetAS2AndEDIInformationByAgreementName]
(
@AgreementName
varchar(255)
)
AS
BEGIN
select 1 as tag,
null as parent,
IsNull(agreement.Name,”) as ‘EDIHEADER!1!AgreementName’,
IsNull(sender.Name,”) as ‘EDIHEADER!1!SenderName’, IsNull(receiver.Name,”) as ‘EDIHEADER!1!ReceiverName’,
IsNull(businessIdentitySender.Qualifier,”) as ‘EDIHEADER!1!ISA5’,IsNull(businessIdentitySender.Value,”) as ‘EDIHEADER!1!ISA6’,
IsNull(businessIdentityReceiver.Qualifier,”) as ‘EDIHEADER!1!ISA7’, IsNull(businessIdentityReceiver.Value,”) as ‘EDIHEADER!1!ISA8’,
IsNull(AS2.AS2From,”) as ‘EDIHEADER!1!AS2From’, IsNull(AS2.AS2To,”) as ‘EDIHEADER!1!AS2To’
from
[BizTalkMgmtDb]
.[tpm].[Partner] sender inner join
[BizTalkMgmtDb]
.[tpm].[Agreement] agreement on sender.PartnerId = agreement.SenderProfileId inner join
[BizTalkMgmtDb]
.[tpm].[BusinessIdentity] businessIdentitySender on businessIdentitySender.ProfileId = sender.PartnerId inner join
[BizTalkMgmtDb]
.[tpm].[Partner] receiver on receiver.PartnerId = agreement.ReceiverProfileId inner join
[BizTalkMgmtDb]
.[tpm].[BusinessIdentity] businessIdentityReceiver on businessIdentityReceiver.ProfileId = receiver.PartnerId left outer join
(
select
businessIdentitySender
.Value as AS2From,
businessIdentityReceiver
.Value as AS2To,
sender
.Name as SenderName, receiver.Name as ReceiverName
from
[BizTalkMgmtDb]
.[tpm].[Partner] sender inner join
[BizTalkMgmtDb]
.[tpm].[Agreement] agreement on sender.PartnerId = agreement.SenderProfileId inner join
[BizTalkMgmtDb]
.[tpm].[BusinessIdentity] businessIdentitySender on businessIdentitySender.ProfileId = sender.PartnerId inner join
[BizTalkMgmtDb]
.[tpm].[Partner] receiver on receiver.PartnerId = agreement.ReceiverProfileId inner join
[BizTalkMgmtDb]
.[tpm].[BusinessIdentity] businessIdentityReceiver on businessIdentityReceiver.ProfileId = receiver.PartnerId
WHERE
agreement
.Name = @AgreementName and
businessIdentityReceiver
.Qualifier = ‘AS2Identity’ and businessIdentitySender.Qualifier = ‘AS2Identity’
) as AS2 on AS2.SenderName = sender.Name and AS2.ReceiverName = receiver.Name
WHERE
agreement
.Name = @AgreementName and
businessIdentityReceiver
.Qualifier <> ‘AS2Identity’ and businessIdentitySender.Qualifier <> ‘AS2Identity’
FOR XML EXPLICIT
END
GO
TEST:
— USE
[BizTalkMgmtDb]
GO
DECLARE
@return_value int
EXEC
@return_value = [dbo].[GetAS2AndEDIInformationByAgreementName]
@AgreementName = N’Acme To EDIGUIDANCE’
SELECT
‘Return Value’ = @return_value
GO
RESULT
<
EDIHEADER AgreementName="Acme To EDIGUIDANCE" SenderName="ACME CORPORATION" ReceiverName="EDIGUIDANCE" ISA5="ZZ" ISA6="ACME" ISA7="ZZ" ISA8="EDIGUIDANCE" AS2From="" AS2To="" />
STORED PROC : For Multiple X12 Agreements from Party->Party
— USE
[BizTalkMgmtDb]
GO
/****** Object: StoredProcedure [dbo].[GetAS2AndEDIInformationByAgreementName] Script Date: 09/18/2010 01:14:01 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: <Mark Rowe>
— Create date: <September 17, 2010>
— Description: <EDIGUIDANCE Addin>
— =============================================
ALTER
PROCEDURE [dbo].[GetEDIInformationByAgreementName]
(
@AgreementName
varchar(255)
)
AS
BEGIN
select
1 as tag,
null as parent,
IsNull(agreement.Name,”) as ‘EDIHEADER!1!AgreementName’,
IsNull(sender.Name,”) as ‘EDIHEADER!1!SenderName’, IsNull(receiver.Name,”) as ‘EDIHEADER!1!ReceiverName’,
IsNull(businessIdentitySender.Qualifier,”) as ‘EDIHEADER!1!ISA5’,IsNull(businessIdentitySender.Value,”) as ‘EDIHEADER!1!ISA6’,
IsNull(businessIdentityReceiver.Qualifier,”) as ‘EDIHEADER!1!ISA7’, IsNull(businessIdentityReceiver.Value,”) as ‘EDIHEADER!1!ISA8’
from
[BizTalkMgmtDb]
.[tpm].[Partner] sender inner join
[BizTalkMgmtDb]
.[tpm].[Agreement] agreement on sender.PartnerId = agreement.SenderProfileId inner join
[BizTalkMgmtDb]
.[tpm].[BusinessIdentity] businessIdentitySender on businessIdentitySender.ProfileId = sender.PartnerId inner join
[BizTalkMgmtDb]
.[tpm].[Partner] receiver on receiver.PartnerId = agreement.ReceiverProfileId inner join
[BizTalkMgmtDb]
.[tpm].[BusinessIdentity] businessIdentityReceiver on businessIdentityReceiver.ProfileId = receiver.PartnerId
WHERE
agreement
.Name = @AgreementName and
agreement
.Protocol = ‘x12’ and businessIdentityReceiver.Qualifier <> ‘AS2Identity’ and businessIdentitySender.Qualifier <>‘AS2Identity’
FOR XML EXPLICIT
END
GO
OUTPUT:
<
EDIHEADER AgreementName="Acme To EDIGUIDANCE" SenderName="ACME CORPORATION" ReceiverName="EDIGUIDANCE" ISA5="ZZ" ISA6="ACME" ISA7="ZZ" ISA8="EDIGUIDANCE" />
STORED PROC : For Multiple AS2 Agreements from Party->Party
— USE
[BizTalkMgmtDb]
GO
/****** Object: StoredProcedure [dbo].[GetAS2AndEDIInformationByAgreementName] Script Date: 09/18/2010 01:14:01 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: <Mark Rowe>
— Create date: <September 17, 2010>
— Description: <EDIGUIDANCE Addin>
— =============================================
ALTER
PROCEDURE [dbo].[GetAS2InformationByAgreementName]
(
@AgreementName
varchar(255)
)
AS
BEGIN
select 1 as tag,
null as parent,
agreement
.Name ‘EDIHEADER!1!AgreementName’,
sender
.Name as ‘EDIHEADER!1!SenderName’, receiver.Name as ‘EDIHEADER!1!ReceiverName’,
businessIdentitySender
.Value as ‘EDIHEADER!1!AS2From’,
businessIdentityReceiver
.Value as‘EDIHEADER!1!AS2To’
from
[BizTalkMgmtDb]
.[tpm].[Partner] sender inner join
[BizTalkMgmtDb]
.[tpm].[Agreement] agreement on sender.PartnerId = agreement.SenderProfileId inner join
[BizTalkMgmtDb]
.[tpm].[BusinessIdentity] businessIdentitySender on businessIdentitySender.ProfileId = sender.PartnerId inner join
[BizTalkMgmtDb]
.[tpm].[Partner] receiver on receiver.PartnerId = agreement.ReceiverProfileId inner join
[BizTalkMgmtDb]
.[tpm].[BusinessIdentity] businessIdentityReceiver on businessIdentityReceiver.ProfileId = receiver.PartnerId
WHERE
agreement
.Name = @AgreementName and
agreement
.Protocol = ‘as2’ and
businessIdentityReceiver
.Qualifier = ‘AS2Identity’ and businessIdentitySender.Qualifier = ‘AS2Identity’
FOR XML EXPLICIT
END
GO
OUTPUT:
<
EDIHEADER AgreementName="ACME to EDIGUIDANCE AS2" SenderName="ACME CORPORATION" ReceiverName="EDIGUIDANCE" AS2From="ACME" AS2To="EDIGUIDANCE" />
How to retrieve EDI Partner Information for context based EDI routing Part 1
Based on Sender and Receiver Party Names
Retrieve ISA SEGMENTS for EDI Context Based routing.
Something we needed for EDIGUIDANCE.
NOTE: THIS WILL BE CHANGING AS I LEARN TO SEPERATE OUT THE EDI FROM EDIFACT. IF YOU HAVE NO EDIFACT THIS SHOULD WORK FINE.
I dug around the databases after creating a party.
How to grab the AS2 and EDI based on Party to and From Names
STORED PROC : For Single X12 and AS2 Agreements from Party->Party
— USE
[BizTalkMgmtDb]
GO
/****** Object: StoredProcedure [dbo].[GetAS2AndEDIInformationBySenderAndReceiverName] Script Date: 09/18/2010 01:07:29 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: <Mark Rowe>
— Create date: <September 17, 2010>
— Description: <EDIGUIDANCE Addin>
— =============================================
CREATE PROCEDURE [dbo].[GetAS2AndEDIInformationBySenderAndReceiverName]
(
@Sender
varchar(255),
@Receiver
varchar(255)
)
AS
BEGIN
select 1 as tag,
null as parent,
IsNull(agreement.Name,”) as ‘EDIHEADER!1!AgreementName’,
IsNull(sender.Name,”) as ‘EDIHEADER!1!SenderName’, IsNull(receiver.Name,”) as ‘EDIHEADER!1!ReceiverName’,
IsNull(businessIdentitySender.Qualifier,”) as ‘EDIHEADER!1!ISA5’,IsNull(businessIdentitySender.Value,”) as ‘EDIHEADER!1!ISA6’,
IsNull(businessIdentityReceiver.Qualifier,”) as ‘EDIHEADER!1!ISA7’, IsNull(businessIdentityReceiver.Value,”) as ‘EDIHEADER!1!ISA8’,
IsNull(AS2.AS2From,”) as ‘EDIHEADER!1!AS2From’, IsNull(AS2.AS2To,”) as ‘EDIHEADER!1!AS2To’
from
[BizTalkMgmtDb]
.[tpm].[Partner] sender inner join
[BizTalkMgmtDb]
.[tpm].[Agreement] agreement on sender.PartnerId = agreement.SenderProfileId inner join
[BizTalkMgmtDb]
.[tpm].[BusinessIdentity] businessIdentitySender on businessIdentitySender.ProfileId = sender.PartnerId inner join
[BizTalkMgmtDb]
.[tpm].[Partner] receiver on receiver.PartnerId = agreement.ReceiverProfileId inner join
[BizTalkMgmtDb]
.[tpm].[BusinessIdentity] businessIdentityReceiver on businessIdentityReceiver.ProfileId = receiver.PartnerId left outer join
(
select
businessIdentitySender
.Value as AS2From,
businessIdentityReceiver
.Value as AS2To,
sender
.Name as SenderName, receiver.Name as ReceiverName
from
[BizTalkMgmtDb]
.[tpm].[Partner] sender inner join
[BizTalkMgmtDb]
.[tpm].[Agreement] agreement on sender.PartnerId = agreement.SenderProfileId inner join
[BizTalkMgmtDb]
.[tpm].[BusinessIdentity] businessIdentitySender on businessIdentitySender.ProfileId = sender.PartnerId inner join
[BizTalkMgmtDb]
.[tpm].[Partner] receiver on receiver.PartnerId = agreement.ReceiverProfileId inner join
[BizTalkMgmtDb]
.[tpm].[BusinessIdentity] businessIdentityReceiver on businessIdentityReceiver.ProfileId = receiver.PartnerId
WHERE
sender
.Name = @Sender and receiver.Name = @Receiver and
agreement
.Protocol = ‘as2’ and
businessIdentityReceiver
.Qualifier = ‘AS2Identity’ and businessIdentitySender.Qualifier = ‘AS2Identity’
) as AS2 on AS2.SenderName = sender.Name and AS2.ReceiverName = receiver.Name
WHERE
sender
.Name = @Sender and receiver.Name = @Receiver and
agreement
.Protocol = ‘x12’
and businessIdentityReceiver.Qualifier <> ‘AS2Identity’ and businessIdentitySender.Qualifier <>‘AS2Identity’
FOR XML EXPLICIT
END
GO
__________________________________
The Test:
— USE
[BizTalkMgmtDb]
GO
DECLARE
@return_value int
EXEC
@return_value = [dbo].[GetAS2AndEDIInformationBySenderAndReceiverName]
@Sender = N’ACME CORPORATION’,
@Receiver
= N’EDIGUIDANCE’
SELECT
‘Return Value’ = @return_value
GO
OUTPUT
<EDIHEADER AgreementName="Acme To EDIGUIDANCE" SenderName="ACME CORPORATION" ReceiverName="EDIGUIDANCE" ISA5="ZZ" ISA6="ACME" ISA7="ZZ" ISA8="EDIGUIDANCE" AS2From="ACME" AS2To="EDIGUIDANCE" />
STORED PROC : For Multiple X12 Agreements from Party->Party
— USE [BizTalkMgmtDb]
GO
/****** Object: StoredProcedure [dbo].[GetEDIInformationBySenderAndReceiverName] Script Date: 09/18/2010 01:51:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: <Mark Rowe>
— Create date: <September 17, 2010>
— Description: <EDIGUIDANCE Addin>
— =============================================
CREATE PROCEDURE [dbo].[GetEDIInformationBySenderAndReceiverName]
(
@Sender varchar(255),
@Receiver varchar(255)
)
AS
BEGIN
select 1 as tag,
null as parent,
IsNull(agreement.Name,”) as ‘EDIHEADER!1!AgreementName’,
IsNull(sender.Name,”) as ‘EDIHEADER!1!SenderName’, IsNull(receiver.Name,”) as ‘EDIHEADER!1!ReceiverName’,
IsNull(businessIdentitySender.Qualifier,”) as ‘EDIHEADER!1!ISA5’,IsNull(businessIdentitySender.Value,”) as ‘EDIHEADER!1!ISA6’,
IsNull(businessIdentityReceiver.Qualifier,”) as ‘EDIHEADER!1!ISA7’, IsNull(businessIdentityReceiver.Value,”) as ‘EDIHEADER!1!ISA8’
from
[BizTalkMgmtDb].[tpm].[Partner] sender inner join
[BizTalkMgmtDb].[tpm].[Agreement] agreement on sender.PartnerId = agreement.SenderProfileId inner join
[BizTalkMgmtDb].[tpm].[BusinessIdentity] businessIdentitySender on businessIdentitySender.ProfileId = sender.PartnerId inner join
[BizTalkMgmtDb].[tpm].[Partner] receiver on receiver.PartnerId = agreement.ReceiverProfileId inner join
[BizTalkMgmtDb].[tpm].[BusinessIdentity] businessIdentityReceiver on businessIdentityReceiver.ProfileId = receiver.PartnerId
WHERE
sender.Name = @Sender and receiver.Name = @Receiver and
agreement.Protocol = ‘x12’
and businessIdentityReceiver.Qualifier <> ‘AS2Identity’ and businessIdentitySender.Qualifier <>‘AS2Identity’
FOR XML EXPLICIT
END
GO
OUTPUT
<EDIHEADER AgreementName="ACME to EDIGUIDANCE AS2" SenderName="ACME CORPORATION" ReceiverName="EDIGUIDANCE" AS2From="ACME" AS2To="EDIGUIDANCE" />
STORED PROC : For Multiple AS2 Agreements from Party->Party based on party names
— USE [BizTalkMgmtDb]
GO
/****** Object: StoredProcedure [dbo].[GetAS2InformationBySenderAndReceiverName] Script Date: 09/18/2010 01:52:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: <Mark Rowe>
— Create date: <September 17, 2010>
— Description: <EDIGUIDANCE Addin>
— =============================================
CREATE PROCEDURE [dbo].[GetAS2InformationBySenderAndReceiverName]
(
@Sender varchar(255),
@Receiver varchar(255)
)
AS
BEGIN
select 1 as tag,
null as parent,
agreement.Name ‘EDIHEADER!1!AgreementName’,
sender.Name as ‘EDIHEADER!1!SenderName’, receiver.Name as ‘EDIHEADER!1!ReceiverName’,
businessIdentitySender.Value as ‘EDIHEADER!1!AS2From’,
businessIdentityReceiver.Value as‘EDIHEADER!1!AS2To’
from
[BizTalkMgmtDb].[tpm].[Partner] sender inner join
[BizTalkMgmtDb].[tpm].[Agreement] agreement on sender.PartnerId = agreement.SenderProfileId inner join
[BizTalkMgmtDb].[tpm].[BusinessIdentity] businessIdentitySender on businessIdentitySender.ProfileId = sender.PartnerId inner join
[BizTalkMgmtDb].[tpm].[Partner] receiver on receiver.PartnerId = agreement.ReceiverProfileId inner join
[BizTalkMgmtDb].[tpm].[BusinessIdentity] businessIdentityReceiver on businessIdentityReceiver.ProfileId = receiver.PartnerId
WHERE
sender.Name = @Sender and receiver.Name = @Receiver and
agreement.Protocol = ‘as2’ and
businessIdentityReceiver.Qualifier = ‘AS2Identity’ and businessIdentitySender.Qualifier = ‘AS2Identity’
FOR XML EXPLICIT
END
GO
OUTPUT
<EDIHEADER AgreementName="ACME to EDIGUIDANCE AS2" SenderName="ACME CORPORATION" ReceiverName="EDIGUIDANCE" AS2From="ACME" AS2To="EDIGUIDANCE" />