Category: Maintenance

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

Do you sometimes end up with instances in the admin console that just wont go away? That are pending to be suspended, but doesn’t seem to ever get there. I did just the other day. To solve this I used the SQL way of terminating these messages, which always seems to do the trick. But it’s not without risk. Thats one of the many reason why there is emphasis on not running this against production. As the technet how-to describes this procedure is empty by default, so remember to add the logic by running the msgbox_cleanup_logic.sql script. There is also an issue with this procedure and tracking but there is a kb hotfix available that solves that. Presumably it’s correct from the start in R2 but I haven’t compared the two.

In short the SQL way of doing it involves running the procedure bts_CleanupMsgbox (once it contains logic). This procedure has an optional parameter named @fLeaveActSubs which by default is 1. Running it with the optional parameter set to something other then 1 will cause all subscriptions to be removed. Not something you’d typically want. A special note here is that since SQL checks for <> 0, passing in null will cause it to behave as if you sent in 0, since null compared to anything in SQL is always false. This is an issue because if you do Script As Execute in SQL the code that gets generated will default to you passing in null:

DECLARE @RC int

DECLARE @fLeaveActSubs int

— TODO: Set parameter values here.

EXECUTE @RC = [BizTalkMsgBoxDb].[dbo].[bts_CleanupMsgbox] @fLeaveActSubs

Your ports and orchestrations will still look like their running, and in a way they are, but there are no subscriptions, so you’re likely to get a routing failure. For you to get your subscriptions back you need to unenlist the send ports and then restart them. For orchestrations this isn’t enough, here you have to go the additional length of un-binding them and then re-configure and start them.

The BizTalk Core Engine’s WebLog mentions in this post that it might also be a good idea to run bts_PurgeSubscriptions directly after, but if your SQL Agent and the PurgeSubscriptionsJob is enabled (and it should be) then this will be done as part of that job.