Thinking outside the box

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

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.

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

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

What is the easiest way in SQL Server 2000?

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

Add a table alias for the OPENROWSET function.

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

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

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

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

can u provide me for sql server 2000

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

I'm using Sql Server 2005

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

Thanks alot.

This works too:

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

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.

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