Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 1 down vote accepted

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
share|improve this answer

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

share|improve this answer
    
Adding or removing the N made no different for me. I'm not sure how it would work without the AS switch. –  J. Pablo Fernández Jul 7 '09 at 19:51

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.

share|improve this answer
    
That gives a Incorrect syntax near '*'. –  J. Pablo Fernández Jul 7 '09 at 19:48
    
i didn't think it would work. even if it did, it didn't feel right. :) –  dotjoe Jul 7 '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 7 '09 at 20:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.