Here is some more forensic/investigative code. Suppose you need to make sure a value gets in a certain column, but you don’t know the system at all. Maybe there is a trigger or stored proc that sets the column based on some other table/column. This code gives you a list of all the stored procedures, […]
Month: February 2018
T-SQL Query to Find a Given Column Name (or Like-Column) From All Tables in a Database
When you come to a new client, and have try to fix bugs or figure out how the system works, it helps to have some “forensice” SQL tools. Suppose you want to find all columns in a database, regardless of which table they are (and of course, show the table name in the query results). […]
SQL Query to XRef BizTalk ReceivePorts to Orchestrations
Suppose you want to know what BizTalk Orchestrations are bound to a send port. Perhaps you ran my query to list send ports related to a receive port. NOTE: Still experimental code; haven’t verified much. select rp.nvcName as 'ReceivePort', op.nvcName as 'OrchPort', op.nBindingOption, o.nvcName as 'Orchestration' –pto.nvcName as 'PortTypeOperation', from bts_receiveport rp inner join bts_orchestration_port_binding […]
How to Query Maps in Biztalk’s Management Database (BiztalkMgmtDb)
How do you find the hidden map name in the BiztalkMgmtDB (BizTalk Database)? If you look at the table names, you will obviously find bt_MapSpec, but it doesn’t contain the map name. The map itself is hidden in the bts_Item table, which you have to join to. I found this on Jeroen Maes Integration Blog. […]
Accessing BizTalk Tracking data with SQL Views
You can turn on message tracking in BizTalk at the Receive Port and Send Port level (as well as various levels in the orchestration). Here are the views provided by the BizTalk install. use biztalkDtaDb –select * from TrackingData –select 'btsv_Tracking_Fragments', * from btsv_Tracking_Fragments select 'btsv_Tracking_Parts', * from btsv_Tracking_Parts select 'btsv_Tracking_Spool', * from btsv_Tracking_Spool To […]