How to import an image from a file, using OPENROWSET, to a table record?

Question

I am trying to import a small image from a file located on my MS SQL Server to a column in a database setup as VARBINARY(MAX). I have declared a variable as VARBINARY(MAX) and would like to assign the image to that variable. Once assigned I have to locate the records and store that image to those records. I have a query that works to store the image to the column, but for the life of me I cannot import new images from files on the servers SSD.

USE [Mydb]
DECLARE @ImageFile VARBINARY(MAX)
SELECT @ImageFile = 'Abracon.jpg'
FROM
 OPENROWSET(BULK 'C:\Users\admin\Pictures\Abracon.jpg', SINGLE_BLOB)
 AS BLOB;

Running this query gives me the following error:

Msg 257, Level 16, State 3, Line 16

Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

I read the OPENROWSET page at learn.microsoft.com, but I still don't see my error.

What am I doing wrong?

Answer 1

Your syntax is off. You are reading the value using OPENROWSET, but then ignoring it and instead assigning the text value 'Abracon.jpg' to @ImageFile. Instead assign the bulk column.

DECLARE @ImageFile VARBINARY(MAX);
SELECT @ImageFile = blob.blk
FROM
 OPENROWSET(BULK 'C:\Users\admin\Pictures\Abracon.jpg', SINGLE_BLOB)
 AS blob(blk);

Answer 2

I'm assuming you are trying to run your code in SSMS or something similar. The default behaviour of such tools when processing queries is to display their results, which requires them to be converted to something printable, i.e. character strings.

What you want to do is directly update the target record with the retrieved image, something like

UPDATE target_table
SET image_col = (
 SELECT img 
 FROM OPENROWSET(BULK 'C:\Users\admin\Pictures\Abracon.jpg', SINGLE_BLOB)
 AS bulk_data(img)
)
WHERE -- some criteria to identify the target row

• Gromit: I'm sorry, yes I'm using SSMS to run these queries. I just read a response to a similar question elsewhere and the person who answered the question made a comment about SSMS 'not being up to the task to do these things'.
• mustaccio: Perhaps that person is not up to the task to answer the question. This has very little to do with SSMS. I mean, the error is, but "these things" don't.
• J.D.: @Gromit Did this answer solve your problem? If so, you should consider upvoting it so others can find it helpful too. Otherwise, please clarify what's still wrong?
• Gromit: What does 'img' represent in this query? SSMS complains SELECT img is not a valid column name and the second is a syntax error; 'expecting ID or quoted ID'.
• Charlieface: @Gromit img is the column name, as you can see from the alias AS bulk(img) although it is missing proper quoting due to being a reserved keyword AS [bulk](img)