BizTalk SQL Queries to find out application artefacts.

bts_application
        Which provides  BizTalk application names which were hosted in the BizTalkApplication reside in BizTalkMgmtDb.


bts_application_reference
         Which contain the BizTalk application reference details reside in BizTalkMgmtDb.


List of BizTalk tables.
http://social.technet.microsoft.com/wiki/contents/articles/22940.biztalkmgmtdb-all-table-details.aspx



SELECT (select nvcName from [bts_application] where nID=appRef.nApplicationID),
         (select app.nvcName where appRef.nReferencedApplicationID=app.nID)
            FROM [BizTalkMgmtDb].[dbo].[bts_application] app
inner join [BizTalkMgmtDb].[dbo].[bts_application_reference] appRef on appRef.[nReferencedApplicationID] = app.nid
where appref.nReferencedApplicationID not in ( '2')


SELECT app.nvcName as ApplicationName,RP.nvcName as ReceivePort, RL.InboundTransportURL FROM [BizTalkMgmtDb].[dbo].bts_receiveport RP
inner join [BizTalkMgmtDb].[dbo].bts_application app on RP.nApplicationID=app.nID
inner join [BizTalkMgmtDb].dbo.adm_ReceiveLocation RL on RP.nID = RL.ReceivePortId
where app.nID not in ('2') and app.nvcName='APPLICATION NAME'


SELECT ItemRef.nvcAssemblyName Artifact_Name, ASS.nvcName Refered_IN, ASS.nvcFullName FullName FROM [BizTalkMgmtDb].[dbo].[bts_itemreference] ItemRef
INNER JOIN [BizTalkMgmtDb].[dbo].[bts_assembly] ASS ON ItemRef.nReferringAssemblyID=ASS.nID
ORDER BY ItemRef.nvcAssemblyName


use BizTalkMgmtDb
 SELECT
app.nvcName as ApplicationName,
SP.nvcName as SendPort,
SPT.nvcAddress AS Address

 FROM [BizTalkMgmtDb].[dbo].bts_sendport SP

inner join [BizTalkMgmtDb].[dbo].bts_application app on SP.nApplicationID=app.nID
inner join [BizTalkMgmtDb].dbo.bts_sendport_transport SPT on SP.nID = SPT.nSendPortID

where app.nID not in ('2') and app.nvcName = 'APPLICATION NAME' and SPT.nvcAddress != ''

HIPPA EDI Introduction.

The Health Insurance Portability and Accountability Act (HIPAA) requires all health insurance payers to comply with the Electronic Data Interchange (EDI) standards for health care as established by the Department of Health and Human Services.


837   --- Claim
835   --- Payment
834   --- Enrolment

276   --- Claim Status Request
277   --- Claim Status Response

270   --- Eligibility Benefit  Request
271   --- Eligibility Benefit  Response

Find out the biztalk applications and it dependants.

SELECT  (select nvcName from [BizTalkMgmtDb].dbo.[bts_application] where nID=appRef.nApplicationID) as MainApplication,
         (select app.nvcName where appRef.nReferencedApplicationID=app.nID) as Dependents
            FROM [BizTalkMgmtDb].[dbo].[bts_application] app
inner join [BizTalkMgmtDb].[dbo].[bts_application_reference] appRef on appRef.[nReferencedApplicationID] = app.nid
where appref.nReferencedApplicationID not in ( '2') order by MainApplication