OPENROWSET, binary files, varchars and varbinaries

Question

I'm trying to upload a binary file into SQL Server, into a varbinary field. I've came up with this query to do it:

INSERT INTO Files(File, Name)
SELECT
   "file.bin" AS Name
   * FROM OPENROWSET(BULK 'C:\file.bin', SINGLE_BLOB) AS File
GO

but I'm getting this error:

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

If I understand correctly, it's reading the file as text (varchar) while it's binary and should be stored as binary on varbinary. I'm afraid it may destroy it in the process, will it? and even if it doesn't, how do I use the CONVERT method?

score 2 · Accepted Answer · answered Jul 07 '09 at 19:52

2

Putting the data in the same order as the fields made it work (or seem to work at least):

INSERT INTO Files(File, Name)
SELECT
     * FROM OPENROWSET(BULK 'C:\file.bin', SINGLE_BLOB) AS File
     "file.bin" AS Name
GO

answered Jul 07 '09 at 19:52

Pablo Fernandez

7,438
25
71
83

Hey, had the same problem, same solution, do you know why? – Ernest_Galbrun May 24 '16 at 11:36

score 0 · Answer 2 · edited Jun 11 '20 at 10:02

0

I have never done this myself but I found this page showing the same procedure to insert a jpg in to a varbinaries...the only difference is the N before the path name and no AS switch

Hope that helps but im probably missing something to do with conversion

edited Jun 11 '20 at 10:02

Community

1

answered Jul 07 '09 at 19:25

Rodent43

697
3
11

Adding or removing the N made no different for me. I'm not sure how it would work without the AS switch. – Pablo Fernandez Jul 07 '09 at 19:51

score 0 · Answer 3 · answered Jul 07 '09 at 19:32

0

I'm not sure why that is returning a varchar column. Looks the same as the example in msdn.

I don't know how you would even convert that column. Maybe...

SELECT 
   convert(varbinary(max), * FROM OPENROWSET(BULK 'C:\file.bin', SINGLE_BLOB)) AS File

But, I'd be more concerned with it being a varchar when you specify SINGLE_BLOB.

answered Jul 07 '09 at 19:32

dotjoe

146
7

That gives a Incorrect syntax near '*'. – Pablo Fernandez Jul 07 '09 at 19:48
i didn't think it would work. even if it did, it didn't feel right. :) – dotjoe Jul 07 '09 at 19:59
yes, the columns must match up. that explains the error...you were attempting an implicit cast of varchar 'file.bin' to the varbinary File column. – dotjoe Jul 07 '09 at 20:01

OPENROWSET, binary files, varchars and varbinaries

3 Answers3