0

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.

Stu
  • 30,392
  • 6
  • 14
  • 33
George
  • 1
  • 1
  • 3
    In grid mode, the most characters *displayed* in a cell is by default 32758, and can be set to a maximum of 65535. If you need more, don't use SSMS to copy your data out of. – Thom A Jan 16 '23 at 08:57
  • 1
    `0x89504E470D0A1A` is a binary value displayed as a HEX value, not a series of characters. SSMS can't display images so it displays the first part of the binary as hex. That doesn't mean that only 32K rows are stored in the field. `UPDATE` without a `WHERE` will replace the image content in *all* rows, not insert a single image. – Panagiotis Kanavos Jan 16 '23 at 09:00
  • What are you trying to do? While `OPENROWSET (Bulk '....', Single_Blob)` can load binary files, it's limited. It can only read files that are accessible by the SQL Server service account, eg local files, or files on a shared folder accessible by the service account. Images are certainly *not* meant to be scripted as HEX values in a SQL script. – Panagiotis Kanavos Jan 16 '23 at 09:03
  • UPDATE without a WHERE will replace the image content in all rows, not insert a single image. I know I simply have a one line table for each customer's DB I did not know its HEX, so I used series of characters to not further confuse. – George Jan 16 '23 at 10:14
  • Images are certainly not meant to be scripted as HEX values in a SQL script. I know its hacky, but it works, it updates the images correctly, as long as it does not surpass this characters limit. The "characters displayed in the cell" as mentioned by @Larnu is the cause, what method would you suggest to copy data. Also even though it makes sense, I still cant find it mentioned as a limit. – George Jan 16 '23 at 10:24
  • Use an ETL tool to *export* the data from SQL Server to a text file. – Thom A Jan 16 '23 at 10:32
  • Why would you want to manually script this update in management studio if you already got the openrowset query working for you automatically? – siggemannen Feb 11 '23 at 11:09
  • We have scripts that update everything automatically. and we transfer the assets(images included) in general. At that moment I had access to a customers terminal where I could run a query but no access to the server or a folder that the server saw. So I tried this and as I said I came across the 32758 characters limit and could not find how to change it, look at the answer I have below. Basically I described the problem so that you know how I stumbled upon this but I wrote this out of curiosity. – George Jul 05 '23 at 09:21

1 Answers1

0

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.

George
  • 1
  • 1