Home Page › Forums › BizTalk 2004 – BizTalk 2010 › How to get Query Notification for events in the BizTalkMgmtDb?
- This topic has 2 replies, 1 voice, and was last updated 9 years, 10 months ago by
community-content.
-
AuthorPosts
-
-
June 14, 2010 at 8:53 AM #24955
Hi.
I’m trying to discern the best way to get query notifications from SQL Server to discover database changes in the BizTalkMgmtDb, when administrators/IT-Pros makes changes to the various artifacts and their properties in the different biztalk applications via the admin console.
When changes occur to the BizTalkMgmtDb I need to be notified about the change (that an event occurred in a database) and then I need to make a query that identifies which application that was changed + which artifacts and properties that was changed within that application (which table changed, which row changed, or which data changed), by an admin person via the console.
I need to do this in order to update a configuration and change management system in our enterprise, but Im not sure how to do this in the best way, since all the different artifacts in an application has its own table in the BizTalkMgmtDb, which makes a query quite complex (and im not good at SQL queries).
I have read Richard Seroter’s article, amongst many others, but I still find my scenario of a more complex nature that I need to get some ideas/help on.
If anyone has some thoughts or clever ideas how to go about this challenge it would be much appreciated.
Thanks.
-M.P
-
June 14, 2010 at 9:30 AM #24956
Hi MP,
Here are some suggestions that you might want to consider:
1. A BizTalkGurus blog on SQL scripts (that can get you started on):
2. A blog on developing custom tools for BizTalk:
3. Have you looked into BizTalkFactory PowerShell Provider for you administrative needs? Here’s a link to the main page on CodePlex:
http://psbiztalk.codeplex.com/
I hope these resources are helpful to you,
Daniel.
-
June 30, 2010 at 3:51 AM #25087
I have played around with this for a couple of days and I have made a SQL script (not finished yet) that reads out the exact same information as you would have under each “Application” in the Biztalk Server Admin console as an XML file from the BizTalkMgmtDb. This gives me the opportunity to read out and compare the data from different points of time during a day or week.
Here is the script, which still needs quite a bit of tweaking before it is 100% and can be used. Maybe it can be of interest to others…
####################################################################################
use BizTalkMgmtDb
declare @applicationId int
select
application.nId as “@Id”
— , ____.nId as “@assemId”
, application.nvcName as “@appName”
, application.DateModified as “DateModified”
————————————————————————————————
— Orchestration
————————————————————————————————
,(
select
Orchestration.orcName as “@Name”
, Orchestration.Modified as “DateModified”
, Orchestration.assemName as “assemName”, bindings
from
(
select app.nID as appId
, ____.nID as assemId
,app.nvcName as appName
, orcestration.nvcFullName as orcName
, orcestration.dtModified as Modified
, ____.nvcFullName as assemName
, ( — Orchestration – bindings
select
p.nvcName as “@Id”
, case
when sendt.nId is not null then sendt.nvcName
when resc.nId is not null then resc.nvcName
when spg.nId is not null then spg.nvcName
else ‘mangler’
end as “@Port”
from dbo.bts_application app
left join dbo.bts_assembly ____ on ____.nApplicationID = app.nID
inner join dbo.bts_orchestration orc on ____.nID = orc.nAssemblyID
left join dbo.bts_orchestration_port p on p.nOrchestrationID = orc.nId
left join dbo.bts_porttype pt on p.nPortTypeID = pt.nID
inner join dbo.bts_orchestration_port_binding bind on bind.nOrcPortID = p.nID
left join dbo.bts_sendport sendt on sendt.nId = bind.nSendPortID and sendt.nApplicationID = app.nID
left join dbo.bts_receiveport resc on resc.nID = bind.nReceivePortID and resc.nApplicationID = app.nID
left join dbo.bts_sendportgroup spg on spg.nID = bind.nSpgID and spg.nApplicationID = app.nID
where app.nID = application.nId
and orcestration.nId = orc.nID
for xml path(‘LogicalPort’),type) ‘Bindings’
, ‘Orchestration’ as bizTalkPart
from dbo.bts_application app
left join dbo.bts_assembly ____ on ____.nApplicationID = app.nID
inner join dbo.bts_orchestration orcestration on ____.nID = orcestration.nAssemblyID
where app.nID = application.nId
) as Orchestration
for xml path(‘Orchestration’),type) ‘Orchestrations’
————————————————————————————————
— Send Port Groups
————————————————————————————————
,(
select
SendPortGroups.portGroupName as “@portGroupName”
, SendPortGroups.DateModified as “DateModified”
, transports
from
(
select
app.nID as appId
, a.nID as assemId
,app.nvcName as appName
, a.nvcFullName as asseName
, sendPortgr.DateModified as DateModified
, sendPortgr.nvcName as portGroupName
, ( select distinct — Send Port Groups properties
sp.nvcName as “@Name”
,case
when st.bIsPrimary = 0 then ‘Backup’
when st.bIsPrimary = 1 then ‘Primary’
end as transport
, adp.Name as type
, st.nvcAddress as URI
, pipeRecive.Name as RecivePipeline
, pipeSend.Name as SendPipeline
from dbo.bts_application app
left join dbo.[bts_assembly] a on a.nApplicationID = app.nID
left join dbo.bts_porttype p on p.nAssemblyID = a.nID
inner join dbo.bts_sendportgroup spg on spg.nApplicationID = app.nID
inner join dbo.bts_spg_sendport spgs on spgs.nSendPortGroupID = spg.nID
inner join dbo.bts_sendport sp on sp.nID = spgs.nSendPortID and sp.nApplicationID = app.nID
inner join dbo.bts_sendport_transport st on sp.nId = st.nSendPortID
inner join dbo.adm_Adapter adp on adp.Id = st.nTransportTypeId
left join dbo.bts_pipeline pipeSend on pipeSend.Id = sp.nSendPipelineID
left join dbo.bts_pipeline pipeRecive on pipeRecive.Id = sp.nReceivePipelineID
where app.nID = application.nId
and sendPortgr.nID = spg.nID
for xml path(‘TransportType’),type) ‘transports’
, ‘SendPortGroups’ as bizTalkPart
from dbo.bts_application app
left join dbo.[bts_assembly] a on a.nApplicationID = app.nID
inner join dbo.bts_sendportgroup sendPortgr on sendPortgr.nApplicationID = app.nID
where app.nID = application.nId
) as SendPortGroups
for xml path(‘SendPortGroup’),type) ‘SendPortGroups’
————————————————————————————————
— Send Ports
————————————————————————————————
,(
select
SendPorts.sendPortName as “@sendPortName”
, SendPorts. DateModified as “DateModified”
, transports
from
(
select
app.nID as appId
, a.nID as assemId
, app.nvcName as appName
, a.nvcFullName as assemName
, sendPort.nvcName as sendPortName
, sendPort.DateModified as DateModified
, ‘SendPorts’ as bizTalkPart
, ( — Send Ports transport
select distinct
case
when st.bIsPrimary = 0 then ‘Backup’
when st.bIsPrimary = 1 then ‘Primary’
end as “@Type”
, adp.Name as type
, st.nvcAddress as URL
, pipeRecive.Name as RecivePipeline
, pipeSend.Name as SendPipeline
from dbo.bts_application app
left join dbo.[bts_assembly] a on a.nApplicationID = app.nID
left join dbo.bts_porttype p on p.nAssemblyID = a.nID
inner join dbo.bts_sendport sp on sp.nApplicationID = app.nID
left join dbo.bts_sendport_transport st on sp.nId = st.nSendPortID
left join dbo.bts_sendport_transform tr on sp.nId = tr.nSendPortID
left join dbo.bts_spg_sendport spgs on spgs.nSendPortID = sp.nId
left join dbo.adm_Adapter adp on adp.Id = st.nTransportTypeId
left join dbo.bts_pipeline pipeSend on pipeSend.Id = sp.nSendPipelineID
left join dbo.bts_pipeline pipeRecive on pipeRecive.Id = sp.nReceivePipelineID
where app.nID = application.nId
and sendPort.nId = sp.nId
for xml path(‘transport’),type) ‘transports’
from dbo.bts_application app
left join dbo.[bts_assembly] a on a.nApplicationID = app.nID
inner join dbo.bts_sendport sendPort on sendPort.nApplicationID = app.nID
where app.nID = application.nId
) AS SendPorts
for xml path(‘SendPort’),type) ‘SendPorts’
————————————————————————————————
— Receive Ports
————————————————————————————————
,(select
resp.nvcName as “@reciverPortName”
, resp.DateModified as DateModified
, (
select
rl.Name as “reciveLocationName”
, rl.InboundTransportURL as URI
, adp.Name as type
, rp.nvcName as recivePort
, host.Name as reciveHandler
, pipeRecive.Name as RecivePipeline
, pipeSend.Name as SendPipeline
from dbo.bts_application app
inner join dbo.bts_receiveport rp on rp.nApplicationID = app.nID
left join dbo.bts_receiveport_transform tr on rp.nId = tr.nReceivePortID
left join dbo.adm_ReceiveLocation rl on rl.ReceivePortId = rp.nID
left join dbo.adm_ReceiveHandler rh on rh.Id = rl.ReceiveHandlerId
left join dbo.adm_Host host on host.Id = rh.HostId
left join dbo.bts_pipeline pipeSend on pipeSend.Id = rl.SendPipelineID
left join dbo.bts_pipeline pipeRecive on pipeRecive.Id = rl.ReceivePipelineID
left join dbo.adm_Adapter adp on adp.Id = rl.AdapterId
where app.nID = application.nId
and resp.nID = rp.nID
order by rl.Name
for xml path(‘ReciveLocation’),type) ReciveLocations
from dbo.bts_application app
inner join dbo.bts_receiveport resp on resp.nApplicationID = app.nID
where app.nID = application.nId
for xml path(‘ReceivePort’),type) ‘ReceivePorts’
————————————————————————————————
— Receive Locations
————————————————————————————————
,(
select
ReceiveLocations.reciveLocationName as “@reciveLocationName”
, ReceiveLocations.DateModified as “DateModified”
, ReceiveLocations.URI as “URI”
, ReceiveLocations.type as “type”
, ReceiveLocations.recivePort as “recivePort”
, ReceiveLocations.reciveHandler as “reciveHandler”
, ReceiveLocations.RecivePipeline as “RecivePipeline”
, ReceiveLocations.SendPipeline as “SendPipeline”
from (
select
app.nID as appId
, a.nID as assemId
, app.nvcName as appName
, a.nvcFullName as assemName
, rl.Name as reciveLocationName
, rl.DateModified as DateModified
, rl.InboundTransportURL as URI
, adp.Name as type
, rp.nvcName as recivePort
, host.Name as reciveHandler
, ‘ReceiveLocations’ as bizTalkPart
, pipeRecive.Name as RecivePipeline
, pipeSend.Name as SendPipeline
from dbo.bts_application app
left join dbo.[bts_assembly] a on a.nApplicationID = app.nID
inner join dbo.bts_receiveport rp on rp.nApplicationID = app.nID
left join dbo.bts_receiveport_transform tr on rp.nId = tr.nReceivePortID
left join dbo.adm_ReceiveLocation rl on rl.ReceivePortId = rp.nID
left join dbo.adm_ReceiveHandler rh on rh.Id = rl.ReceiveHandlerId
left join dbo.adm_Adapter adp on adp.Id = rh.AdapterId
left join dbo.adm_Host host on host.Id = rh.HostId
left join dbo.bts_pipeline pipeSend on pipeSend.Id = rl.SendPipelineID
left join dbo.bts_pipeline pipeRecive on pipeRecive.Id = rl.ReceivePipelineID
where app.nID = application.nId
) as ReceiveLocations
for xml path(‘ReceiveLocation’),type) ‘ReceiveLocations’
————————————————————————————————
— Schemas
————————————————————————————————
,(
Select
Schemas.FullName as “@targetNamespace”
, Schemas.DateModified as “DateModified”
, Schemas.rootName as “RootName”
, Schemas.SchemaTypes as “SchemaType”
from (
select distinct
app.nID as appId
, a.nID as assemId
, app.nvcName as appName
, a.nvcFullName as assemName
, spc.date_modified as “DateModified”
,spc.schema_root_name as rootName
, ‘Schemas’ as bizTalkPart
,’http://’ + item.FullName as FullName
,case
when SchemaType = 1 then ‘Property’
when SchemaType = 0 then ‘Document’
end as SchemaTypes
–, *
from dbo.bts_application app
left join dbo.[bts_assembly] a on a.nApplicationID = app.nID
left join dbo.bt_DocumentSpec spc on a.nID = spc.assemblyid
inner join dbo.bt_XMLShare xmls on xmls.id = spc.shareid
left join dbo.bts_item item on item.id = spc.itemid and item.AssemblyId = spc.assemblyid
where app.nID = application.nId
and property_clr_class is null
UNIONselect distinct
app.nID as appId
, a.nID as assemId
, app.nvcName as appName
, a.nvcFullName as assemName
, spc.date_modified as “DateModified”
, ‘ ‘ as rootName
, ‘Schemas’ as bizTalkPart
,’http://’ + item.FullName
,case
when SchemaType = 1 then ‘Property’
when SchemaType = 0 then ‘Document’
end as SchemaTypes
–, *
from dbo.bts_application app
left join dbo.[bts_assembly] a on a.nApplicationID = app.nID
left join dbo.bt_DocumentSpec spc on a.nID = spc.assemblyid
inner join dbo.bt_XMLShare xmls on xmls.id = spc.shareid
left join dbo.bts_item item on item.id = spc.itemid and item.AssemblyId = spc.assemblyid
where app.nID = application.nId
and item.SchemaType = 1
) as Schemas
for xml path(‘Schema’),type) ‘Schemas’
————————————————————————————————
— Maps
————————————————————————————————
,(
Select
Maps.mapName as “@mapName”
, Maps.DateModified as “DateModified”
, Maps.sourceSchema as “sourceSchema”
, Maps.targetSchema as “targetSchema”
from (select app.nID as appId
, a.nID as assemId
,app.nvcName as appName
, a.nvcFullName as asseName
, map.date_modified as DateModified
, item.fullName as mapName
, map.indoc_docspec_name as sourceSchema
, map.outdoc_docspec_name as targetSchema
, ‘Maps’ as bizTalkPart
from dbo.bts_application app
left join dbo.[bts_assembly] a on a.nApplicationID = app.nID
left join dbo.bts_item item on item.AssemblyId = a.nID
inner join dbo.bt_MapSpec map on map.itemid = item.id and map.assemblyid = a.nID
where app.nID = application.nId
) as Maps
for xml path(‘Map’),type) ‘Maps’
————————————————————————————————
— Pipelines
————————————————————————————————
,(
Select
Pipelines.pipelineName as “@pipelineName”
from (select app.nID as appId
, a.nID as assemId
, app.nvcName as appName
, a.nvcFullName as asseName
, pipe.Name as pipelineName
, ‘Pipelines’ as bizTalkPart
from dbo.bts_application app
left join dbo.[bts_assembly] a on a.nApplicationID = app.nID
inner join dbo.bts_pipeline pipe on pipe.nAssemblyID = a.nID
where app.nID = application.nId
) as Pipelines
for xml path(‘Pipeline’),type) ‘Pipelines’
————————————————————————————————
from dbo.bts_application application
— left join dbo.bts_assembly ____ on ____.nApplicationID = app.nID
— where ____.nApplicationID = @applicationId
order by application.nvcName
FOR XML path (‘Application’), ROOT(‘BizTalk’)####################################################################################
– M.P
-
-
AuthorPosts
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.