Error:
The Messaging Engine failed to add a receive location “rlAtlasMyExchangeName-RabbitMQ” with URL
“rabbit@VM3-NWalters.hg.mysite.net/Queue=D.Data.MySite.MyExchangeName&RoutingKey=D.Data.MySite.MyExchangeName”
to the adapter “RabbitMQ”. Reason: “None of the specified endpoints were reachable”.

Solution:
This means that either you cannot connect to the server (or maybe the user doesn’t have access).
In my case, it was a stupid typo, I had hg instead of hq in the Host Name.

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.

Suppose you have a large code base, and you just want to search for a program name, without downloading the codebase to your local disk.  If the naming conventions are hierarchical, you can usually use the “repo-browser”, for example that comes with Tortoise-SVN.  But if a program is named differently from the hierarchical directory structure, finding a program can be quite a search.

At first, I though “svnlookup” was the answer, but that can only be run on the server.  Specifying the server URL on this command

Reference: https://stackoverflow.com/questions/49202817/svnlook-repository-format-syntax-is-incorrect

This seems to be what I was looking for:

For Windows without Grep command:

svn list -R https://subversion-repo/subfolder | findstr filename

or if you have a “grep” command:

svn list -R https://subversion-repo/subfolder | grep filename

To search just the “Trunk” if you have grep command:

svn list -R https://subversion-repo/subfolder | grep Trunk.*filename

It can take several minutes to run.

Source: http://foldingair.blogspot.com/2013/12/search-whole-svn-repository-for-given.html

 

 

 

The newer versions of RabbitMQ from https://www.nuget.org/packages/RabbitMQ.Client have issues with older version of DotNet and Visual Studio 2013.

I picked the highest release before 4.0 and at least the NuGet package installed.

Install-Package RabbitMQ.Client -Version 3.6.9

Trying to install anything after 4.0 will give the following errors:

PM> Install-Package RabbitMQ.Client -Version 5.0.1
Installing ‘RabbitMQ.Client 5.0.1’.
Successfully installed ‘RabbitMQ.Client 5.0.1’.
Adding ‘RabbitMQ.Client 5.0.1’ to RabbitMQConsoleTester.
Uninstalling ‘RabbitMQ.Client 5.0.1’.
Successfully uninstalled ‘RabbitMQ.Client 5.0.1’.
Install failed. Rolling back…
Install-Package : Could not install package ‘RabbitMQ.Client 5.0.1’. You are trying to install this package into a project that targets
‘.NETFramework,Version=v4.5’, but the package does not contain any assembly references or content files that are compatible with that framework. For more
information, contact the package author.
At line:1 char:1
+ Install-Package RabbitMQ.Client -Version 5.0.1
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Install-Package], InvalidOperationException
+ FullyQualifiedErrorId : NuGetCmdletUnhandledException,NuGet.PowerShell.Commands.InstallPackageCommand

 

When using SOAPUI to call a BizTalk published ochestration or schema, you might get this error response/XML from SOAP-UI:

Error

Main error text is:

The message could not be processed. This is most likely because
the action ‘http://Neal.Temp.WCFTargetNamespace/Outbound214Service/Send214Request’
is incorrect or because the message contains an invalid or expired security context token or because
there is a mismatch between bindings. The security context token would be invalid if the service aborted
the channel due to inactivity. To prevent the service from aborting idle sessions prematurely increase
the Receive timeout on the service endpoint’s binding

Solution Steps

Assuming no security is needed when first testing on your local machine.  You can tighten up the security after you get the basic test working.

  1. Modify the web.config file of the published webservice and set the authentication mode to “None”.
  2. Modify the BizTalk Receive Location created by the WCF publish utility, on the Security tab, set the “Security mode” to none.
  3. In SOAP-UI, click on the WS-A words (tab) below your request xml message.  Set the options as shown below:

References: https://adventuresinsidethemessagebox.wordpress.com/2012/10/22/calling-a-wcf-wshttp-service-from-soapui/

Here is some more forensic/investigative code. Suppose you need to make sure a value gets in a certain column, but you don’t know the system at all.
Maybe there is a trigger or stored proc that sets the column based on some other table/column. This code gives you a list of all the stored procedures, triggers, and functions (and even views) that use that column name (or any string field).


Create table #temp1
(ServerName varchar(64), dbname varchar(64)
,spName varchar(128),ObjectType varchar(32), SearchString varchar(64))

Declare @dbid smallint, @dbname varchar(64), @longstr varchar(5000)
Declare @searhString VARCHAR(250)

set @searhString='OpenDate'

declare db_cursor cursor for
select dbid, [name]
from master..sysdatabases
--where [name] not in ('master', 'model', 'msdb', 'tempdb', 'northwind', 'pubs')
where [name] in ('MyDatabase')

open db_cursor
fetch next from db_cursor into @dbid, @dbname

while (@@fetch_status = 0)
begin
PRINT 'DB='+@dbname
set @longstr = 'Use ' + @dbname + char(13) +
'insert into #temp1 ' + char(13) +
'SELECT @@ServerName, ''' + @dbname + ''', Name
, case when [Type]= ''P'' Then ''Procedure''
when[Type]= ''V'' Then ''View''
when [Type]= ''TF'' Then ''Table-Valued Function''
when [Type]= ''FN'' Then ''Function''
when [Type]= ''TR'' Then ''Trigger''
else [Type]/*''Others''*/
end
, '''+ @searhString +''' FROM [SYS].[SYSCOMMEnTS]
JOIN [SYS].objects ON ID = object_id
WHERE TEXT LIKE ''%' + @searhString + '%'''

exec (@longstr)
fetch next from db_cursor into @dbid, @dbname
end

close db_cursor
deallocate db_cursor
select * from #temp1
Drop table #temp1

Code from: StackOverflow

When you come to a new client, and have try to fix bugs or figure out how the system works, it helps to have some “forensice” SQL tools. Suppose you want to find all columns in a database, regardless of which table they are (and of course, show the table name in the query results). In my case, I was dealing with PickupDateTime, but the screen was showing two fields, a from and to date/time. So I wanted to look for all columns that start contain both “pickup” and “from” in that order.


Use MyDatabaseName
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%pickup%from%'
ORDER BY schema_name, table_name;

Example code came from Dave Pinal’s SQL Blog.

Suppose you want to know what BizTalk Orchestrations are bound to a send port.
Perhaps you ran my query to list send ports related to a receive port.

NOTE: Still experimental code; haven’t verified much.


select
rp.nvcName as 'ReceivePort',
op.nvcName as 'OrchPort',
op.nBindingOption,
o.nvcName as 'Orchestration'
--pto.nvcName as 'PortTypeOperation',
from bts_receiveport rp
inner join bts_orchestration_port_binding opb on rp.nID = opb.nReceivePortID
inner join bts_orchestration_port op on op.nID = opb.nOrcPortId --and op.nPortTypeID in (1,9) -- recieve ports only (9 = activating receive)
inner join bts_orchestration o on o.nID = op.nOrchestrationID
--where rp.nvcName like '%ABC%'

See also the query on this page to do similar by message type: https://social.msdn.microsoft.com/Forums/en-US/06080af3-7c2f-4460-8b07-a668eab9df12/relate-ports-and-messagetypes?forum=biztalkgeneral.

How do you find the hidden map name in the BiztalkMgmtDB (BizTalk Database)?

If you look at the table names, you will obviously find bt_MapSpec, but it doesn’t contain the map name. The map itself is hidden in the bts_Item table, which you have to join to. I found this on Jeroen Maes Integration Blog. He has a more complex query that finds a map based on the input/output target namespace. He also joins to the bts_Assembly table.

My goal was just to list all maps containing some sequence of letters (such as a customer name or abbreviation).


use BizTalkMgmtDb
select i.name, * from bt_MapSpec m
inner join bts_item i on m.itemid = i.id
where i.Name like '%ABC%' -- just the map name
-- where i.FullName like '%ABC%" -- optionally use the fully qualified name

I’m surprised there the Type column doesn’t seem to be populated with some number that indicates that the bts_Item is a map, or a schema, or whatever.

You can turn on message tracking in BizTalk at the Receive Port and Send Port level (as well as various levels in the orchestration).

Here are the views provided by the BizTalk install.


use biztalkDtaDb
--select * from TrackingData
--select 'btsv_Tracking_Fragments', * from btsv_Tracking_Fragments
select 'btsv_Tracking_Parts', * from btsv_Tracking_Parts
select 'btsv_Tracking_Spool', * from btsv_Tracking_Spool

To get the Send/Receive port or more info about the message, you need to join to dtav_MessageFacts. The idea for this join came from sample code here.


SELECT top 200
a.[Event/Direction],
a.[Event/Port],
a.[Event/URL],
CONVERT(VARCHAR(10), a.[Event/Timestamp], 111) as [date],
DATEPART(HOUR,a.[Event/Timestamp]) as [Hour],
imgPart,
imgPropBag
FROM [dbo].[dtav_MessageFacts] a
inner join dbo.btsv_Tracking_Parts b on a.[MessageInstance/InstanceID] = b.uidMessageID

Note: Later I added this line (not shown in picture). You have to use DataLength() instead of Len() to get the size of an image field.

DataLength(imgPart) as Length,

The body of the message is stored in imgPart of the btsv_Tracking_Parts view, but unfortuantely it's in Hex.
The following site is one that can convert the hex to ascii (might also need one to go to Unicode). https://www.rapidtables.com/convert/number/hex-to-ascii.html. Paste the hex data in the top, click convert, and your text will appear in the bottom half. You won't be able to see it all, but you can copy/paste to NotePad++ or some other editor.

While it's nice to use the built-in features of BizTalk when possible, they will typically have limitations compared to custom options. In a few places where I worked, we implemented our own "Trace" that writes data to a SQL trace table. We had our own concept of promoted fields to identify the trace, such as the location where the trace was capture (pipeline, orchestrations, before/after map, etc), a user type key, and a correlation token that can tie together traces across an entire business process.

See also: 3 ways of programmatically extracting a message body from the BizTalk tracking database (Operations DLL, SQL, and WMI). This article explains how Biztalk compresses the data with BTSDBAccessor.dll.