VARBINARY output in MS MGMT Studio different than Powershell/.NET

Question

I'm in a bit of a situation with outputting data from a table where the only column I'm selecting is a VARBINARY(MAX) type.

In management studio, when I execute the query, I get back what I expect in the format:

0x1FABCDEFG......etc

Now, when the same query is executed in powershell, via a simple setup of a SqlCommand, SqlDataAdapter and DataSet, the file is eventually output which uses the following command:

$dataSet.Tables[0] | Select-Object * | Export-Csv $outputFileName -Force -NoTypeInformation;

..it returns a bunch of rows that simply contain:

System.Byte[]

A byte array isn't what I want, just the same output that's shown when you execute it in SQL Management Studio...

Is there some kind of cast/convert magic I can do in the query (SQL 2005 btw), or do I need to go through the DataSet object and mess around with the data there (which is not ideal for the situation but can be done).

Any help or a pat on the back letting me know that I should just take my whole idea out back and shoot it would be greatly appreciated. :)

Answer 1

I have found this and think it may help. Microsoft blog Article which explains how to do hex to string and vise versa. If you add it to the SQL command it might do the trick