How can I extract the value of a varbinary(max) column?

Question

I have a varbinary(max) column that is storing images in an SQL database.

I am working on a newdb script, where an application creates a new instance of the db and populates a few of the tables. One of those tables I am working on is initializing that image column.

In order to do this, I printed the contents of the column using a select statement and pasted the content into the insert statement of the newdb script. This appeared to work initially, but the image didn't load correctly.

So I compared the DATALENTH() of the original data (5469988) and the new data (21839). It appears the Microsoft SQL Server management Studio - 2014 cut off the data why I copied it from the original db at a certain point. I need to be able to get the entire content of the column. Any ideas?

Answer 1

Instead of copying/pasting, right-click on the results and do 'Save Results As...', and that should export the full contents. Funny thing is setting the query output to text or file explicitly will still truncate long data values.

Answer 2

If you copy and paste your limited to the query result options. Mostly columns will be cut of after a certain lenght (often 256 chars).

You can select in the top bar "save result as..." which will prompt you an dialog for data export.

You can use the data export wizard too.

Answer 3

select cast(convert(varchar(max), VarBinaryMaxColumn, 1) as xml) from Table