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, triggers, and functions (and even views) that use that column name (or any string field).

Create table #temp1
(ServerName varchar(64), dbname varchar(64)
,spName varchar(128),ObjectType varchar(32), SearchString varchar(64))

Declare @dbid smallint, @dbname varchar(64), @longstr varchar(5000)
Declare @searhString VARCHAR(250)

set @searhString='OpenDate'

declare db_cursor cursor for
select dbid, [name]
from master..sysdatabases
--where [name] not in ('master', 'model', 'msdb', 'tempdb', 'northwind', 'pubs')
where [name] in ('MyDatabase')

open db_cursor
fetch next from db_cursor into @dbid, @dbname

while (@@fetch_status = 0)
PRINT 'DB='+@dbname
set @longstr = 'Use ' + @dbname + char(13) +
'insert into #temp1 ' + char(13) +
'SELECT @@ServerName, ''' + @dbname + ''', Name
, case when [Type]= ''P'' Then ''Procedure''
when[Type]= ''V'' Then ''View''
when [Type]= ''TF'' Then ''Table-Valued Function''
when [Type]= ''FN'' Then ''Function''
when [Type]= ''TR'' Then ''Trigger''
else [Type]/*''Others''*/
, '''+ @searhString +''' FROM [SYS].[SYSCOMMEnTS]
JOIN [SYS].objects ON ID = object_id
WHERE TEXT LIKE ''%' + @searhString + '%'''

exec (@longstr)
fetch next from db_cursor into @dbid, @dbname

close db_cursor
deallocate db_cursor
select * from #temp1
Drop table #temp1

Code from: StackOverflow

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). In my case, I was dealing with PickupDateTime, but the screen was showing two fields, a from and to date/time. So I wanted to look for all columns that start contain both “pickup” and “from” in that order.

Use MyDatabaseName
SELECT AS table_name,
SCHEMA_NAME(schema_id) AS schema_name, AS column_name
FROM sys.tables AS t
WHERE LIKE '%pickup%from%'
ORDER BY schema_name, table_name;

Example code came from Dave Pinal’s SQL Blog.

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.

rp.nvcName as 'ReceivePort',
op.nvcName as 'OrchPort',
o.nvcName as 'Orchestration'
--pto.nvcName as 'PortTypeOperation',
from bts_receiveport rp
inner join bts_orchestration_port_binding opb on rp.nID = opb.nReceivePortID
inner join bts_orchestration_port op on op.nID = opb.nOrcPortId --and op.nPortTypeID in (1,9) -- recieve ports only (9 = activating receive)
inner join bts_orchestration o on o.nID = op.nOrchestrationID
--where rp.nvcName like '%ABC%'

See also the query on this page to do similar by message type:

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. He has a more complex query that finds a map based on the input/output target namespace. He also joins to the bts_Assembly table.

My goal was just to list all maps containing some sequence of letters (such as a customer name or abbreviation).

use BizTalkMgmtDb
select, * from bt_MapSpec m
inner join bts_item i on m.itemid =
where i.Name like '%ABC%' -- just the map name
-- where i.FullName like '%ABC%" -- optionally use the fully qualified name

I’m surprised there the Type column doesn’t seem to be populated with some number that indicates that the bts_Item is a map, or a schema, or whatever.

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 get the Send/Receive port or more info about the message, you need to join to dtav_MessageFacts. The idea for this join came from sample code here.

SELECT top 200
CONVERT(VARCHAR(10), a.[Event/Timestamp], 111) as [date],
DATEPART(HOUR,a.[Event/Timestamp]) as [Hour],
FROM [dbo].[dtav_MessageFacts] a
inner join dbo.btsv_Tracking_Parts b on a.[MessageInstance/InstanceID] = b.uidMessageID

Note: Later I added this line (not shown in picture). You have to use DataLength() instead of Len() to get the size of an image field.

DataLength(imgPart) as Length,

The body of the message is stored in imgPart of the btsv_Tracking_Parts view, but unfortuantely it's in Hex.
The following site is one that can convert the hex to ascii (might also need one to go to Unicode). Paste the hex data in the top, click convert, and your text will appear in the bottom half. You won't be able to see it all, but you can copy/paste to NotePad++ or some other editor.

While it's nice to use the built-in features of BizTalk when possible, they will typically have limitations compared to custom options. In a few places where I worked, we implemented our own "Trace" that writes data to a SQL trace table. We had our own concept of promoted fields to identify the trace, such as the location where the trace was capture (pipeline, orchestrations, before/after map, etc), a user type key, and a correlation token that can tie together traces across an entire business process.

See also: 3 ways of programmatically extracting a message body from the BizTalk tracking database (Operations DLL, SQL, and WMI). This article explains how Biztalk compresses the data with BTSDBAccessor.dll.

Suppose you are at a new clients, and you want to see quickly all the orchestrations are bound to send and receive ports.
Here’s some code to help you get started with that.

select op.nvcName as 'OrchInternalPortName',
       o.nvcName as 'OrchName', 
	   'SendPort' as 'SendOrReceive',
	   sp.nvcName as 'PortName'
from bts_orchestration_port op 
inner join bts_orchestration o on o.nid = op.nOrchestrationID 
inner join bts_orchestration_port_binding opb on opb.nOrcPortID = op.nID 
inner join bts_sendport sp on sp.nid = opb.nSendPortID
--order by sp.nvcName 
select op.nvcName as 'OrchInternalPortName',
       o.nvcName as 'OrchName', 
	   'ReceivePort' as 'SendOrReceive',
	   rp.nvcName as 'PortName' 
from bts_orchestration_port op 
inner join bts_orchestration o on o.nid = op.nOrchestrationID 
inner join bts_orchestration_port_binding opb on opb.nOrcPortID = op.nID 
inner join bts_receiveport rp on rp.nid = opb.nReceivePortID
order by 'portname'

Sorry, can’t show any actual results at this time.

This is similar but different from a prior SQL xref I posted that ties RecievePorts to SendPorts.

I actually forgot about this, and did the same code again on another day: Xref BizTalk Receive Ports to Orchestrations.

<code><span class="str">When running NUGET "install-package NEST" (or possibly any other that uses newtonsoft-json), get error: 
"'Newtonsoft.Json' already has a dependency defined for 'Microsoft.CSharp'"

In my case, I was at a client running Visual Studio 2013 and needed to upgrade the version of Nuget. 

Reference: <a href=""></a>

In Visual Studio: Tools -&gt; Extensions and Updates -&gt; Updates tab -&gt; Visual Studio Gallery.
Below is the screen I saw, I clicked the "Update" ubtton next to NuGet, it installed, had to restart Visual Studio, then the tried the original install-package command again and it worked. </span></code>

In my previous blog, I talked about how to use ImageMagick to square a file. Now, in this blog I’m taking it one step further.

I have a directory of photos from all over the internet, and I want to post them to a social media site. If the pictures are taller than 510 pixels, I need to square them as discussed in the last blog (and I’m adding a white padding/background). This will keep them from being cut off when I post them to the specific social media site.

Secondly, if the picture is “wide” vs “tall”, then I don’t need to add the background, but I might as well resize it to save upload/posting time. Some pictures downloaded from the internet are very large, so I’m standardizing on resizing them to a width or height of 510, whichever is smaller.

So in the Powershell, here’s what is happening:
1) Get all the files in a directory ($inputPath) and loop through them one at a time
2) Call ImageMagick “identify” to return the size of the file (primarily I need to know it’s width and height)
3) Use RegEx pattern matching to pull the width/height out of the returned string, convert them to numbers, then use compare them
4) If it is a wide picture, call the ImageMagick resize function
5) If it is a tall picture, call the ImageMagick convert function. Technically, I’m converting to a thumbnail, but it’s a very large thumbnail.

For both #4 and #5 in the above steps, the output file is a variable written to the $targetPath directory with the same filename.


$inputPath = "e:\Photos\ToResize\"
$targetPath = "e:\Photos\Resized\"
$files = get-ChildItem $inputPath  

foreach ($file in $files) 

    #$output = &  magick identify e:\Photos\ToResize\022cca60-a2a9-49b8-964e-b228acf517f3.jpg
    # shell out to run the ImageMagick "identify" command 
    $output = &  magick identify $file.FullName 
    $outFilename = $targetPath + $file.Name 

    # sample value of $output: 
    #e:\Photos\ToResize\022cca60-a2a9-49b8-964e-b228acf517f3.jpg JPEG 768x512 768x512+0+0 8-bit sRGB 111420B 0.000u 0:00.000

    write-host $output 

    $pattern = ".* (\d*?)x(\d*?) .*"
    #the first parenthese is for capturing the cityState into the $Matches array
    #the second parentheses are needed above to look for $ (which is end of line)
    #or zip code following the city/state
    $isMatch = $output -match $pattern
    if ($Matches.Count -gt 0)
        #$width = $Matches[1];
        #$height = $Matches[2];

        [int]$width = [convert]::ToInt32($Matches[1]); 
        [int]$height = [convert]::ToInt32($Matches[2]); 

        if ($height -gt $width) 
           $orientation = "tall"; 
           # shell out to run the ImageMagick "convert" command 
           & convert -define jpeg:size=510x510 $file.FullName -thumbnail "510x510>" -background white -gravity center -extent "510x510" $outFilename 
           $orientation = "wide"; 
           # shell out to run the ImageMagick "convert" command 
           & convert -resize 510x510^ $file.FullName $outFilename 
        Write-Host ("file=$($ width=$width height=$height orientation=$orientation `n`n"); 
       Write-Host ("No matches"); 

It took me a while to find a good utility to square images and make them a certain size. The results come from this confusing documentation page for Image Magic.

convert -define jpeg:size=510x510 Test1.jpg -thumbnail "510x510>" -background white -gravity center -extent "510x510" testResult.jpg

Of course you change the sizes and the background color. The page above also show you how to crop the picture if desired to fit the square image (rather than padding with the background color).

The following command (mogrify) does the exact same for every file in the current directory, and outputs to another directory specified by the -path parameter. (reference: mogrify)

mogrify -define jpeg:size=510x510 -thumbnail "510x510>" -background white -gravity center -extent "510x510" -path ../ImageOutputDir *.jpg

Note: When installing on Windows, be sure to check the box to the left of “Install legacy utilities” so it will install the convert.exe program.

See also: Photo.StackExchange/questions/27137K

See also: Downloads for ImageMagick.


I made this quick summary of some of the size and limitations and specs for developing Amazon Alexa Skill. Please contact me at Amazon Skill Developer (Dallas, Texas) if you need one developed.

Max 8000 characters of speech when Alexa is speaking based on text.

MP3 – must be bit rate 48kbps, sample rate 16000 Hz, codec version (MPEG Version 2)

Maximum length of simple “conversational/speech” audio files is 90 seconds.
You can do long audio files of any length, such as podcasts, but that requires a separate programming model.

You can optionally include “cards” which a person can see at:

Brief explanation of cards here:

In addition to signing up with AWS, you also have to have a login here:
There are two parts to the skill, the skill/vocabulary definition, and the software code.

Logo Images:
You need a small 108×108 PNG and a large 512×512 PNG file for getting certified and putting the skill in the skill store.

If you decide to use “cards”, see above, they have images as well, but the size requirements are different:

small 720w x 480h
large 1200x x 800h