How do I attach varbinary(max) data from SQL to my SMTPMessage in powershell?

Question

I have a varbinary(max) column in my SQL server containing binary that looks like the following:

  0x255044462D312E340D0A25E2E3CFD30D...

I want to retrieve this value in a powershell so that I can run logic that sends out an email.

#Get the attachment binary into a variable from sql server
$Query = "SELECT AttachmentBinary FROM [dbo].MyFiles"
$e= Invoke-Sqlcmd -Query $Query -ServerInstance $server (etc.etc.)

Running $e.Attachmentbinary in powershell gives me a list of decimal numbers, which I assume is how powershell displays the binary:

37
80
68
70
45
(and so on)

The problem now is that when I prepare the file and send the email..

$contentStream = New-Object System.IO.MemoryStream(,$e.Attachmentbinary)
$MailAttachment = New-Object System.Net.Mail.Attachment($contentStream, "application/pdf")
$SMTPMessage.Attachments.Add($MailAttachment)

.. It leaves sends me a 1kb file. That is to say,

$e.AttachmentBinary.Length #Returns 1024 for some reason..

I know for sure the binary data in the database is correct. The problem seems to be in the second part when adding the attachment.

How can this be corrected?

Answer 1

The Invoke-Sqlcmd needs a -MaxBinaryLength value, as the default size of 1024 (1KB) may be to small for the varbinary.

The resulting array $e.Attachmentbinary needs to be cast to binary explicitly, as @Theo suggested.

[Byte[]]$e.Attachmentbinary