BizTalk Receive Location Server Query

Given a fileserver change, I needed a way to get all the server names for Receive Locations in the BizTalkMgmtDb. The InboundTransportURL would be in the form of \\servername\somepath. This query assumes a valid charindex value — often a dangerous assumption. This did the trick nicely!

select 

  [id], [name], [ActiveStartDT], [InboundTransportURL], replace(left([InboundTransportURL], charindex('\', [InboundTransportURL], 3)), '\', '')

from 

  adm_ReceiveLocation rl (nolock) 

where 

  inboundtransporturl like '%\\%'

order by

  replace(left([InboundTransportURL], charindex('\', [InboundTransportURL], 3)), '\', '')

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s