We store images in a varbinary(max) column in a table, which are then used in SSRS and our web application. We don't have a front end that allows the users to update these images, because that never occurs.

I had to update a series of images so I thought that instead of providing the images somewhere for the people that would actually update in the client environment I could make insert statements that contain the image as Single_Blob, somehow. I need this because there are many environments where the same thing has to be updated and transferring the images instead of a SQL file, creates great confusion

The problem

I am trying to set a varbinary column to a static series of characters

UPDATE [DBO].tableName
SET columnName = 0x89504E470D0A1A...............

It works fine.

To create the series of characters that describe the image, I use this

SELECT *
FROM OPENROWSET (Bulk 'E:\....', Single_Blob) AS Image

And copy the selected value into the SET columnName = shown above.

When the series of characters that it generates is greater than 32758 characters, the query in SSMS behaves weird (it loses all coloring), the update statement runs successfully but the image is broken once read.

If I run this

UPDATE [DBO].tableName
SET columnName = BulkColumn
FROM OPENROWSET (Bulk 'E:\....', Single_Blob) AS Image

for the same image, it is written to the table correctly, because it is read correctly.

I understand that it is not an optimal solution all along, but I want to know what the 32758 limit I have come across is, because it is nowhere in the internet and that's weird.

1

There are 1 answers

0
George On

In the comments I was pointed to the right direction. In grid mode, the most characters displayed in a cell is by default 32758. The suggested solution was to use an ETL tool. I ended up doing what is suggested in the following post: https://stackoverflow.com/a/11897999/17446997

It worked fine.