Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 How to insert pdf file into varbinary(max)
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

31 Posts

Posted - 01/16/2013 :  02:19:19  Show Profile  Reply with Quote
Hi, all
I'm trying to insert content of .pdf into varbinary(max) column.
I don't have option to use any .net cool tools, just have SSMS on 2008.

I tried to use something like:

insert into t1(xdata) values ('0x1234')

and get this error:
Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

So looks like I can't use single ' around binary value, and '0x12343122' is actual binary value , when I use cast('0x1234' as varbinary(max)) it convert it into another value of course, thinking it's varchar.
Can anybody recommend the way to achieve this. I'm not sure can I reference a physical file in INSERT? and then do convert.

Thanks all for your help.

Flowing Fount of Yak Knowledge

2242 Posts

Posted - 01/16/2013 :  02:36:00  Show Profile  Reply with Quote

create table testBlob (col1 varbinary(max))
insert into testBlob
SELECT BulkColumn
FROM OPENROWSET (BULK 'FullFilePath\FileName.pdf', SINGLE_BLOB) TheFile
SELECT * FROM testBlob

Go to Top of Page

Starting Member

31 Posts

Posted - 01/16/2013 :  13:59:20  Show Profile  Reply with Quote
Thanks, Chandu

It worked OK, I also tried to do reverse cast(col1 as varchar(max)) and see that it broken on the very first NUL value (as I see it in notepad++). It probably expected behavior for HEX-to-Char conversion.

Or what is the good way to test this, ultimately this column will be displayed as pdf file in www browser.


Go to Top of Page

Starting Member

11 Posts

Posted - 05/03/2013 :  02:40:46  Show Profile  Reply with Quote
Another problem on inserting pdf. files?
You said you had tried the methods that bandi recommended to you and it worked OK except for a little mis takes.
I 'm wondering why not to choose anther third party app to help you.
Here is a great PDF app on Yiigo.
It also has many other free trials dealing with different documents and files.
Hope it helps.

Kind Regards,
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000