How to insert pdf file into varbinary(max)

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

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

How to insert pdf file into varbinary(max)

Author

Topic

dainova
Starting Member

31 Posts

Posted - 2013-01-16 : 02:19:19

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.
Dai

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-16 : 02:36:00

Example:

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

--
Chandu

dainova
Starting Member

31 Posts

Posted - 2013-01-16 : 13:59:20

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.

Best
Dai

arronlee
Starting Member

12 Posts

Posted - 2013-05-03 : 02:40:46

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,
Arron

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources