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)), '\', '')