Thinking outside the box

# re: Insert binary data like images into SQL Server without front-end application

Wow .. it's that easy??? Very, very cool -- thanks Peter!!!

# re: Insert binary data like images into SQL Server without front-end application

I don't think it work for SQL Server 2000.
The SINGLE_BLOB setting was introduced for SQL Server 2005, if I am not mistaking.

# re: Insert binary data like images into SQL Server without front-end application

yeah.. this is not possible in ss2k
i use this for fast xml import among other thing :)
http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx

# re: Insert binary data like images into SQL Server without front-end application

Great Post! --- I added a loop script to it for a bunch of images I needed to put into my db...

-----------------------------------
DECLARE @imgString varchar(80)
DECLARE @insertString varchar(3000)


SET @imgNumber = 1

WHILE @imgNumber < 101

BEGIN

SET @imgString = 'E:\images\Picture' + CONVERT(varchar,@imgNumber) + '.jpg'

SET @insertString = N'INSERT INTO images(imageData)
SELECT * FROM OPENROWSET(BULK N''' + @imgString + ''', SINGLE_BLOB) as tempImg'

EXEC(@insertString)

SET @imgNumber = @imgNumber + 1

END

GO
---------------------------------

Again, Thanks for the post

Best Regards

# re: Insert binary data like images into SQL Server without front-end application

What is the easiest way in SQL Server 2000?

# re: Insert binary data like images into SQL Server without front-end application

It gives an error:"A correlation name must be specified for the bulk rowset in the from clause."

# re: Insert binary data like images into SQL Server without front-end application

Add a table alias for the OPENROWSET function.

# re: Insert binary data like images into SQL Server without front-end application

thank u dear..
i was looking for it since very long
now i got it
thx

# re: Insert binary data like images into SQL Server without front-end application

What do u mean by
Add a table alias for the OPENROWSET function.
how?? i am a noob in SQL

# re: Insert binary data like images into SQL Server without front-end application

this is great it is working in SQL Express 2005 . no change required.

# re: Insert binary data like images into SQL Server without front-end application

What do u mean by
Add a table alias for the OPENROWSET function.
how?? i am a noob in SQL

Select * from OPENROWSET(BULK N'D:\aa.gif', SINGLE_BLOB) as image


') as image' is compulsory in above statement.you cannot negate it

# re: Insert binary data like images into SQL Server without front-end application

can u provide me for sql server 2000

# re: Insert binary data like images into SQL Server without front-end application

I have an error: Incorrect syntax near the keyword 'BULK'.

I'm using Sql Server 2005

# re: Insert binary data like images into SQL Server without front-end application

Have you checked your compatibility level?
It must be set to 90 or higher.

# re: Insert binary data like images into SQL Server without front-end application

Thanks alot.

This works too:

UPDATE table
SET column =
(SELECT * FROM
OPENROWSET(BULK N'D:\Filename.aspx', SINGLE_BLOB) AS ORS)
WHERE ID = 2

# re: Insert binary data like images into SQL Server without front-end application

Hi,

I have a varbinary(max) column in my database table in which i am saving "test.docx" file (remember it;s a docx file, not doc). When i am performing a string search on this column then it is returning no result. The same search is running on "doc" files saved in binary format in database.

My search query is

Select * from <tablename>
where convert(varchar(max),<mybinarycolumn>) like '%<keywordtosearch>%';

is there a way i can perform a binary search on this table.

please reply asap.

# re: Insert binary data like images into SQL Server without front-end application

You will have to convert to search parameter value to varbinary too.
Or use the new FILESTREAM datatype.

# re: Insert binary data like images into SQL Server without front-end application

thnxs very much!!!

Its wonderful to work with such thing............

# re: Insert binary data like images into SQL Server without front-end application

thnxs very much!!!

Its wonderful to work with such thing............

# re: Insert binary data like images into SQL Server without front-end application

Hey can any1 guide on how to insert into an image in sql*plus 2002

# re: Insert binary data like images into SQL Server without front-end application

am new to sqlsevre am now using sqlserver 2000 i dont no how to insert photo in sqlsever and how to extract it. if any know just please post me my email id shri.nandini@gmail.com

am doing in my project have lot to develop my front end jsp so please help.

# re: Insert binary data like images into SQL Server without front-end application

Im trying a simple script to insert into the table but get a really arb error. I have tried as an image but crystal reports wont read it. I have converted it to a Varbinary (255) and ge the following error.

INSERT INTO [Broker info].[dbo].[Broker Image]
([cpkBrokerRef]
,[Broker Number]
,[Broker image])
VALUES
(2322
,345235
,'C:\Documents and Settings\pmasters\My Documents\My Pictures\IAM\arsig.JPG')

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

# re: Insert binary data like images into SQL Server without front-end application

You're trying to insert string datatype item into the image type field.
Guys, I have Access database, how to insert an usual textfile into the field of type "OLE Object"??

# re: Insert binary data like images into SQL Server without front-end application

You're trying to insert string datatype item into the image type field.
Guys, I have Access database, how to insert an usual textfile into the field of type "OLE Object"??

# re: Insert binary data like images into SQL Server without front-end application

Hey, I have to upload a word document to test for a clase, I know we need to make it a binary file and then upload, but honestly, we keep searching and just don't get it... I think the answers here are more or less what we want to do, but if you could guide us a little it'd be great

# re: Insert binary data like images into SQL Server without front-end application

need SQL-2000 Image Insert

# re: Insert binary data like images into SQL Server without front-end application

GREAT

How do you get the image back out to the file system?

Or do we just let the front end grab it?

# re: Insert binary data like images into SQL Server without front-end application

You can use BCP. Or the front-end, as you suggest.

# re: Insert binary data like images into SQL Server without front-end application

Nice Concept. Thanks.

# re: Insert binary data like images into SQL Server without front-end application

Hi Guys, for any of you struggling to update a single row:
---------------------------------------------------------------------------

declare @image varchar(max)

set @image = (SELECT BulkColumn from Openrowset( Bulk 'C:\CBR600RR9.jpg', Single_Blob) as BikeImage)
UPDATE Models
SET ModelImage = @image where ModelName = 'CBR600RR9'
----------------------------------------------------------------------------

# re: Insert binary data like images into SQL Server without front-end application

Shouldn't @image variable be declared as VARBINARY(MAX) instead?

# re: Insert binary data like images into SQL Server without front-end application

Perhaps, but in my case it worked out fine. I'll keep that in mind though.. Thanks..

# re: Insert binary data like images into SQL Server without front-end application

thanks man, its wonderful how its is simple and do the work instead of stream inserting....
thanks again

# re: Insert binary data like images into SQL Server without front-end application

Brilliant, I've been looking for this for ages. So simple aswell. Why the F doesn't MS have this on their site!
Cheers

# re: Insert binary data like images into SQL Server without front-end application

They do here http://msdn.microsoft.com/en-us/library/ms190312.aspx

# re: Insert binary data like images into SQL Server without front-end application

I am refering SQL Server2005. i am trying to insert image into the database without using front end. It is very difficult to insert the image. and display that table in asp.net. plz sir,tell me how to insert the image and display that image in asp.net?

I tried to insert image like following code
create table image(image_name image,path ntext);
insert into image values("C:\winter.jpg","C:\Winter.jpg");
Select * from OPENROWSET(BULK N'C:\Winter.jpg', SINGLE_BLOB) as image

it dint work. plz tell me how to insert the image without using front end . and display that image in asp.net

# re: Insert binary data like images into SQL Server without front-end application

A correlation name must be specified for the bulk rowset in the from clause.
Add the correlation name like dis

CREATE TABLE myTable(Document varbinary(max)) INSERT INTO myTable(Document) SELECT * FROM OPENROWSET(BULK N'C:\Image1.jpg', SINGLE_BLOB) as look

in the above query (look) is the correlation name u can use any name