1

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?

3 Answers 3

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
1
  • Hey, had the same problem, same solution, do you know why? Commented May 24, 2016 at 11:36
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

1
  • Adding or removing the N made no different for me. I'm not sure how it would work without the AS switch. Commented Jul 7, 2009 at 19:51
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.

3
  • That gives a Incorrect syntax near '*'. Commented Jul 7, 2009 at 19:48
  • i didn't think it would work. even if it did, it didn't feel right. :)
    – dotjoe
    Commented Jul 7, 2009 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
    Commented Jul 7, 2009 at 20:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .