BizTalk, Maintenance, SQL

How-to: Use BizTalkMgmtDb to get referenced assemblies

Sometimes when you are trying to remove an assembly from within the Administration Console you get and error saying that it’s being used. Most often you’ll get information on where it’s being used at the same time, but not always. These queries are for those times. With the help of these queries, and the knowledge gained from working with these tables you could also lookup things like assemblies that aren’t being used or on which ports a particular map or pipeline is used – information that is not easily accesible through the Administration Console.


Get Assemblies referenced by Maps on ReceivePorts

Select    ass.nvcName as Assembly,
itm.Name as Map,
rcv.nvcName as ReceivePort
from bts_receiveport_transform tr
join bt_MapSpec map on tr.uidTransformGUID = map.id
join bts_item itm on map.itemid = itm.id
join bts_assembly ass on map.assemblyid = ass.nID
join bts_receiveport rcv on rcv.nID = tr.nReceivePortID
order by Assembly, Map, ReceivePort

Get Assemblies referenced by Maps on SendPorts

Select    ass.nvcName as Assembly,
itm.Name as Map,
snd.nvcName as SendPort
from bts_sendport_transform tr
join bt_MapSpec map on tr.uidTransformGUID = map.id
join bts_item itm on map.itemid = itm.id
join bts_assembly ass on map.assemblyid = ass.nID
join bts_sendport snd on snd.nID = tr.nSendPortID
order by Assembly, Map, SendPort

Get Assemblies referenced by Pipelines on ReceiveLocations

select    ass.nvcName as Assembly,
pipe.Name as Pipeline,
loc.Name as ReceiveLocation
from adm_receiveLocation loc
join bts_pipeline pipe on pipe.ID = loc.ReceivePipelineId
join bts_assembly ass on ass.nID = nAssemblyID

Get Assemblies referenced by Pipelines on SendPorts

select    ass.nvcName as Assembly,
pipe.Name as Pipeline,
snd.nvcName as SendPort
from bts_sendport snd
join bts_pipeline pipe on pipe.ID = snd.nSendPipelineId
join bts_assembly ass on ass.nID = nAssemblyID

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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