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?

Eichman

Is your question "How can I copy and paste large amounts of data using SMS" or is your question something else? – Sext

@RickS If copy and paste would work that'd be great, but it hasn't worked thus far so it appears I'll need a different solution. – Eichman

Answer 1

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

Soyuz

This only works if the limit for XML output is set high enough (2MB, 5MB, Unlimited). This can be set here: Options > Query Results > SQL Server > Results To Grid > XML Data (See screenshot: https://mcmap.net/q/75617/-how-do-you-view-all-text-from-an-ntext-or-nvarchar-max-in-ssms) – Bouldon

Answer 2

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.

Gough

I did this, and the size is bigger: 32767. It's still much smaller then the original: 5469988. – Eichman

If it's still not working then you might also try using the bcp command to create an exported data file and then use the same command to import it into your destination after creating empty table structures. – Gough

unfortunately when this is deployed, it won't have access to a file system – Eichman

This does not work on my example of 394 kB VARBINARY, the result is truncated. What works is the answer below https://mcmap.net/q/1880310/-how-can-i-extract-the-value-of-a-varbinary-max-column by @Dan Leksell – Donnelly

Answer 3

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.

Jeb

"Save result as" will not work for unprocessed varbinary output. Exported data will still be truncated. – Plaudit