CODEWITHSUNDEEP
sqlsql-serverssmsvarbinaryvarbinarymax
James Madison
James Madison

Reputation: 357

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

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?

Upvotes: 5

Views: 12047

Answers (3)

Dan Leksell
Dan Leksell

Reputation: 540

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

Upvotes: 11

Ionic
Ionic

Reputation: 3935

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.

Upvotes: 1

Peter Tirrell
Peter Tirrell

Reputation: 3003

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.

Upvotes: 4

Related Questions