Working with Image,text,binary datatypes

Question

Working with Image,text,binary datatypes

vamsi2.k

SSC Journeyman

Points: 86
More actions
September 24, 2014 at 6:37 am

#311766

Hi Mates,
I have a requirement to export the blob(image)file, excel and word documents that are stored in the sqlserver table. Please help me in getting this done using other than the .net code.
Thanks,
Vamsi.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

vamsi2.k SSC Journeyman Points: 86 More actions · Answer 1

It is a Sharepoint database. I have restored it into sqlserver and i am trying to extract the files that are stored in that DB. Please help me.

Thanks,

Vamsi.

Lowell SSC Guru Points: 323518 More actions · Answer 2

writing to disk requires either an external application, like somethign written in .NET , or using bcp or a CLR object to help you do it.

there's no native way for SQL to write to a disk; it's domain is doing stuffin databases and memory.

Elliot Whitlow's awesome project on codeplex is what i've co-opted to do file disk manipulation when i need to;

you need to make sure the account running the SQL service has access to the local disk, or network shares you plan to write to.

http://nclsqlclrfile.codeplex.com/

that makes the code wonderfully easy:

--MFGetFileImage

-- Parameters: @FilePath, @FileName

-- purpose: given a path and filename, return the varbinary of the file to store in the database.

-- usage:

CREATE TABLE myImages(id int,filename varchar(200),rawimage varbinary(max) )

INSERT INTO myImages(id,filename,rawimage)

SELECT 1,'fedora_spinner.gif',dbo.MFGetFileImage('C:\Data\','fedora_spinner.gif' )

--MSPSaveFileImage

-- Parameters: @FilePath,@FileName,@FileBytes

-- purpose: given an varbinary image column in a table, write that image to disk

-- usage:

--assumes table and the file from the example above for dbo.MFGetFileImage exists already.

declare @myfile varbinary(max)

SELECT @myfile = rawimage FROM myImages WHERE id = 1

EXEC dbo.MSPSaveFileImage 'C:\Data','spinning.gif',@myfile

a bcp examples would be like in this link:

http://www.sqlservercentral.com/Forums/Topic1296938-391-1.aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!