Error

When trying to deploy a Business Rule Engine Policy in BizTalk Server 2013, I was getting the error:
The database “Server:Instance” associated with the deployment driver does not match database “Server2:Instance2” of the during product configuration.

Solution

You can run a SQL script like the following (on my machine, there was only one row in the table, so no where clause was needed).

Update adm_group set RuleEngineDBServerName = 'VM3-NWALTERS'
select RuleEngineDBServerName, RuleEngineDBName, * from adm_group

where VM3-NWALTERS was my machine name. We used a SYSPREP procedure originally to copy a machine and rename it.

Suppose you want to find if a map is used on some ReceivePort. In BizTalk, one Receive Port can contain multiple maps. The one that is executed is based on the matching target namespace and root element.

select app.nvcName as ApplicationName, 
       rp.nvcName as ReceivePortName,
       it.Name as MapName, 
       bTwoWay 
  from bts_receiveport rp 
inner join bts_receiveport_transform rpt on rpt.nReceivePortID = rp.nID 
inner join bt_mapspec ms on ms.id = rpt.uidTransformGUID
inner join bts_item it on ms.itemid = it.id
inner join bts_application app on rp.nApplicationID = app.nID 
where it.Name like '%204%'
order by rp.nvcName, it.Name 

Now, here’s how to do the same for SendPorts.

select app.nvcName as ApplicationName, 
       '' as ReceivePortName, 
       sp.nvcName as SendPortName,
       it.Name as MapName, 
       bTwoWay 
  from bts_sendport sp 
inner join bts_sendport_transform spt on spt.nSendPortID = sp.nID 
inner join bt_mapspec ms on ms.id = spt.uidTransformGUID
inner join bts_item it on ms.itemid = it.id
inner join bts_application app on sp.nApplicationID = app.nID 
where IT.Name like '%204%'
--order by sp.nvcName, it.Name 
order by 1, 2, 3

Now we can get fancy and do both in one query using the “UNION” clause. This requires having the columns match, so I’ve added the dummy SendPort in the receive query, and the dummy ReceivePort in the SendPort Query. The sort has to then sort by numeric columns.


select app.nvcName as ApplicationName, 
       rp.nvcName as ReceivePortName,
       '' as SendPortName, 
       it.Name as MapName, 
       bTwoWay 
  from bts_receiveport rp 
inner join bts_receiveport_transform rpt on rpt.nReceivePortID = rp.nID 
inner join bt_mapspec ms on ms.id = rpt.uidTransformGUID
inner join bts_item it on ms.itemid = it.id
inner join bts_application app on rp.nApplicationID = app.nID 
where IT.Name like '%204%'
--order by rp.nvcName, it.Name 
union 
select app.nvcName as ApplicationName, 
       '' as ReceivePortName, 
       sp.nvcName as SendPortName,
       it.Name as MapName, 
       bTwoWay 
  from bts_sendport sp 
inner join bts_sendport_transform spt on spt.nSendPortID = sp.nID 
inner join bt_mapspec ms on ms.id = spt.uidTransformGUID
inner join bts_item it on ms.itemid = it.id
inner join bts_application app on sp.nApplicationID = app.nID 
where IT.Name like '%204%'
--order by sp.nvcName, it.Name 
order by 1, 2, 3

Today, I saw a send port that had a filter such as BTS.Operation == Send204External. I looked at the orchestrations that I thought were involved in this application/project, but couldn’t find that operation name in any of the orchestration send ports.

There are two approaches to doing the xref:
1) Scanning all the code. I use Total Commander which has a great built-in utility to scan files with a mask (such as *.odx) for a given term (Send204External). But this assumes I have all the code on my disk (from our source code repository), and it doesn’t always run quickly.
2) Run a SQL query against the BizTalk SQL database.

In BizTalk, each orchestration logical (internal) SendPort can have one or more operations.

using BizTalkMgmtDB 
select 
       app.nvcName as 'Application', 
       pto.nvcName as 'Operation', 
       pt.nvcName as 'PortType',
       op.nvcName as 'Orch-PortName',
       ass.nvcName as 'Assembly_Name'
         from bts_porttype_operation pto 
   inner join bts_porttype pt on pto.nPortTypeID = pt.nID 
   inner join bts_orchestration_port op on op.nPortTypeID = pt.nID 
   inner join bts_assembly ass on pt.nAssemblyID = ass.nID 
   inner join bts_application app on ass.nApplicationID = app.nID 
where pto.nvcName = 'Send204External' 

select app.nvcName,
       rp.nvcName as ReceivePort, 
       bTwoWay, 
       rl.Name as ReceiveLocation,
       InboundTransportURL, 
       ReceivePipelineData
  from bts_receiveport rp 
inner join adm_ReceiveLocation rl on rl.ReceivePortId = rp.nID 
inner join bts_application app on rp.nApplicationID = app.nID 
--where InboundTransportURL like '%test%'
order by rl.Name 

I’ve been doing a lot of reverse engineering and studying of old systems as we migrate them to new releases of BizTalk. I find myself constantly using SQL command to narrow down the ports I’m interested in… By joining to the bts_sendport_transport table, we are able to show the address (for example, disk/file directory name, FTP address, SQL address, etc…)

Use BizTalkMgmtDb

select app.nvcName as Application, 
       sp.nvcName as SendPortName,
       nvcAddress as Address,
       nvcSendPipelineData, 
       nvcFilter, 
       bDynamic, 
       bTwoWay    
from bts_sendport sp 
   inner join bts_application app on sp.nApplicationID = app.nID 
   inner join bts_sendport_transport spt on spt.nSendPortID = sp.nID and spt.nTransportTypeId is not null 
where sp.nvcName like '%204%' 
order by app.nvcName, sp.nvcName 

We have our own RabbitMQ adapter.

The addresses look like the following.

Send Port
rabbit@dev.myRabbitMQ.com/Exchange=MyRabbitMQTopic&RoutingKey=MyRoutingKey

Receive Location
rabbit@dev.myRabbitMQ.com/Queue=MyQueueName&RoutingKey=MyRoutingKey

What I wanted to do is match which Receive Ports subscribe to RabbitMQ RoutingKeys from other Send Ports. In other words, when I send out a message from one orchestration (send port), which orchestration (or Receive Port) is going to pick it up? The query below doesn’t include the xref to the orchestrations.

This requires some parsing of the address to pull out the RoutingKey, then I join on that.
I could do the same thing for disk files (where I would take of the the \*.* in order to do a potential match).

select sp.nvcName as 'SendPort', 
	   rp.nvcName as 'ReceivePort', 
	   rl.Name as 'ReceiveLocation', 
	   SUBSTRING(spt.nvcAddress, CHARINDEX('&RoutingKey=', spt.nvcAddress)+Len('&RoutingKey='), LEN(nvcAddress)) as 'RoutingKey',
           spt.nvcAddress as 'SendPortAddress', 
	   rl.InboundTransportURL as 'ReceiveLocationAddress'
   
   from bts_SendPort sp 
   inner join bts_SendPort_Transport spt on spt.nSendPortId = sp.Nid and nTransportTypeID is not null 
   	left outer join adm_ReceiveLocation rl on
	      SUBSTRING(spt.nvcAddress, CHARINDEX('&RoutingKey=', spt.nvcAddress)+Len('&RoutingKey='), LEN(spt.nvcAddress)) = 
		  SUBSTRING(rl.InboundTransportURL, CHARINDEX('&RoutingKey=', rl.InboundTransportURL)+Len('&RoutingKey='), LEN(rl.InboundTransportURL))
    left outer join bts_ReceivePort rp on rl.ReceivePortID = rp.NId  

   where nvcAddress like '%RoutingKey%'
   	 and InboundTransportURL like '%RoutingKey%'
	 order by sp.nvcname 

I cannot show actual ports, but column headings would look like this:

Sometimes you need the full assembly name, properly formatted to use for various reasons
(such as a WCF Custom Behavior file that is imported into BizTalk adapter).

Here are a couple of ways to get it:

DOS Comamnd Window (GacUtil /L)


C:\Windows\system32>gacutil /l | find "bLogical"
bLogical.BizTalk.RESTBehavior, Version=1.0.0.0, Culture=neutral, PublicKeyToke
n=8b7f8ad23140b57b, processorArchitecture=MSIL
bLogical.RESTSchemas, Version=1.0.0.0, Culture=neutral, PublicKeyToken=fef5a20
096b53fc0, processorArchitecture=MSIL

C# using reflection


//using System.Reflection -- add this using statement
Assembly assembly = Assembly.LoadFrom(@"d:\GitAtlasCore\RestErrorHandling\bLogical.BizTalk.RESTBehavior\bin\Debug\bLogical.BizTalk.RESTBehavior.dll");
foreach (Type type in assembly.GetTypes())
{
Console.WriteLine(type.FullName);
//Console.WriteLine(type.AssemblyQualifiedName);
}

Console.WriteLine("-----");
Type myType = assembly.GetType("bLogical.BizTalk.RESTBehavior.BizTalkRESTRequestHandlerExtensionElement");
Console.WriteLine(myType.AssemblyQualifiedName);

Console.ReadLine();

Output of C#


bLogical.BizTalk.RESTBehavior.BizTalkRESTResponseHandler
bLogical.BizTalk.RESTBehavior.RequestContext
bLogical.BizTalk.RESTBehavior.BizTalkRESTRequestHandlerExtensionElement
bLogical.BizTalk.RESTBehavior.BizTalkRESTRequestHandlerBehavior
bLogical.BizTalk.RESTBehavior.BizTalkRESTRequestHandler
bLogical.BizTalk.RESTBehavior.BizTalkRESTTransmitHandlerExtensionElement
bLogical.BizTalk.RESTBehavior.BizTalkRESTTransmitHandlerEndpointBehavior
bLogical.BizTalk.RESTBehavior.BizTalkRESTTransmitHandler
bLogical.BizTalk.RESTBehavior.BizTalkRESTTransmitErrorHandler
bLogical.BizTalk.RESTBehavior.CustomJsonErrorBodyWriter
bLogical.BizTalk.RESTBehavior.MessageHelper
-----
bLogical.BizTalk.RESTBehavior.BizTalkRESTRequestHandlerExtensionElement, bLogica
l.BizTalk.RESTBehavior, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8b7f8ad
23140b57b


#Biztalk Kill Process associated with BizTalk Host Instance
$processID = Get-WmiObject Win32_Service -Filter "Name='BTSSvc`$BizTalkServerApplication'" | Select-Object -Expand ProcessID
Write-Host "processID=$processID"
stop-process -id $processID -Force
Write-Host "Completed"

This can be used to kill a BizTalk Host Instance that is in the stop-pending state.

Other commands you can run to see how the Get-WmiObject Win32_Service works:

#get-wmiobject Win32_service
#get-wmiobject Win32_service | where {$_.Name -match "BizTalkServerApplication"}
#get-wmiobject Win32_service | where {$_.Name -eq "BTSSvc`$BizTalkServerApplication"}

The following two queries return all send and receive ports/locations that use a given pipeline component.
You can filter on the friendly name or the fully qualified name. This query can be very useful when you need to change a pipeline component,
and you need to “roll off” the applications that use it, make the change, then “roll them back on.

use BizTalkMgmtDb
SELECT app.nvcName AS [Application],
ass.nvcName AS [Assembly],
rp.nvcName AS [Receive Port],
rl.Name AS [Receive Location],
rppl.Name AS [Receive Pipeline],
rppl.FullyQualifiedName as [RecievePipelineFQ]
FROM bts_application app WITH(NOLOCK)
LEFT OUTER JOIN bts_assembly ass WITH(NOLOCK) ON app.nID = ass.nApplicationId
LEFT OUTER JOIN bts_receiveport rp WITH(NOLOCK) ON rp.nApplicationID = ass.nApplicationID
LEFT OUTER JOIN adm_ReceiveLocation rl WITH(NOLOCK) ON rp.nID = rl.receiveportId
LEFT OUTER JOIN bts_pipeline rppl WITH(NOLOCK) ON rl.ReceivePipelineId = rppl.Id
WHERE
rppl.Name like '%shared%'
and rppl.FullyQualifiedName like '%V2%'
ORDER BY 1,2,3

SELECT app.nvcName AS [Application],
ass.nvcName AS [Assembly],
sp.nvcName AS [Send Port],
sppl.Name AS [Send Pipeline],
sppl.FullyQualifiedName as [Send PipelineFQ]
FROM bts_application app WITH(NOLOCK)
LEFT OUTER JOIN bts_assembly ass WITH(NOLOCK) ON app.nID = ass.nApplicationId
LEFT OUTER JOIN bts_sendport sp WITH(NOLOCK) ON sp.nApplicationID = ass.nApplicationID
LEFT OUTER JOIN bts_pipeline sppl WITH(NOLOCK) ON sp.nSendPipelineID = sppl.Id
WHERE
sppl.Name like ‘%shared%’
and sppl.FullyQualifiedName like ‘%V2%’
ORDER BY 1,2,3

You could make it fancier and come up with one query that does a union of the two, but then you probably would need to add another column to indicate if it’s a receive or send.

If you have orchestrations involved, there is another blog post that shows how to xref orchestrations to pipelines.

Today, I was trying to find if a JSON pipeline we had was being used on a given BizTalk system.
I found the SQL command from here: https://gallery.technet.microsoft.com/Show-BizTalk-Applications-8f73d753
and just modified it to add an additional condition to the where clause

and rppl.Name like ‘%json%’

Here is the full command:


SELECT app.nvcName AS [Application],
ass.nvcName AS [Assembly],
orc.nvcName AS [Orchestration],
orc.nOrchestrationStatus AS [Status],
orp.nvcName AS [Orchestration Port],
pt.nvcName AS [Port Type],
pto.nvcName AS [Port Type Operation],
rp.nvcName AS [Receive Port],
rl.Name AS [Receive Location],
rppl.Name AS [Receive Pipeline],
sp.nvcName AS [Send Port],
sppl.Name AS [Send Pipeline],
spg.nvcName AS [Send Port Group]
FROM bts_application app WITH(NOLOCK)
LEFT OUTER JOIN bts_assembly ass WITH(NOLOCK) ON app.nID = ass.nApplicationId
LEFT OUTER JOIN bts_orchestration orc WITH(NOLOCK) ON ass.nID = orc.nAssemblyId
LEFT OUTER JOIN bts_orchestration_port orp WITH(NOLOCK) ON orc.nID = orp.nOrchestrationId
LEFT OUTER JOIN bts_porttype pt WITH(NOLOCK) ON orp.nPortTypeID = pt.nID
LEFT OUTER JOIN bts_porttype_operation pto WITH(NOLOCK) ON pt.nID = pto.nPortTypeID
LEFT OUTER JOIN bts_orchestration_port_binding opp WITH(NOLOCK) ON orp.nID = opp.nOrcPortID
LEFT OUTER JOIN bts_receiveport rp WITH(NOLOCK) ON opp.nReceivePortID = rp.nID
LEFT OUTER JOIN adm_ReceiveLocation rl WITH(NOLOCK) ON rp.nID = rl.receiveportId
LEFT OUTER JOIN bts_pipeline rppl WITH(NOLOCK) ON rl.ReceivePipelineId = rppl.Id
LEFT OUTER JOIN bts_sendport sp WITH(NOLOCK) ON opp.nSendPortID = sp.nID
LEFT OUTER JOIN bts_pipeline sppl WITH(NOLOCK) ON sp.nSendPipelineID = sppl.Id
LEFT OUTER JOIN bts_sendportgroup spg WITH(NOLOCK) ON opp.nSpgID = spg.nID
WHERE orc.nvcName IS NOT NULL
and rppl.Name like '%json%'
ORDER BY 1,2,3

— Status
— 1 = Unenlisted
— 2 = Stopped
— 3 = Started

NOTE: – above only works with orchestrations

If you don’t have an orchestration involved, I have created another a pair of queries to find send and receive ports that use a given pipeline.