5

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?

sqluser
  • 5,502
  • 7
  • 36
  • 50
James Madison
  • 337
  • 1
  • 4
  • 17
  • 1
    Is your question "How can I copy and paste large amounts of data using SMS" or is your question something else? – Rick S Jun 17 '15 at 18:48
  • @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. – James Madison Jun 18 '15 at 13:36

3 Answers3

9
select cast(convert(varchar(max), VarBinaryMaxColumn, 1) as xml) from Table
Dan Leksell
  • 510
  • 5
  • 6
  • 1
    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://stackoverflow.com/a/57163349/2470012) – Tikall Dec 21 '20 at 09:22
4

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.

Peter Tirrell
  • 2,962
  • 4
  • 29
  • 52
  • 1
    I did this, and the size is bigger: 32767. It's still much smaller then the original: 5469988. – James Madison Jun 17 '15 at 21:53
  • 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. – Peter Tirrell Jun 17 '15 at 22:57
  • unfortunately when this is deployed, it won't have access to a file system – James Madison Jun 18 '15 at 13:39
  • This does not work on my example of 394 kB VARBINARY, the result is truncated. What works is the answer below https://stackoverflow.com/a/54637194/3588010 by @Dan Leksell – Viktor Jan 13 '20 at 07:19
1

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.

Ionic
  • 3,884
  • 1
  • 12
  • 33
  • 1
    "Save result as" will not work for unprocessed varbinary output. Exported data will still be truncated. – Ishmaeel Aug 26 '19 at 03:11