CODEWITHSUNDEEP
sql-serverexport
JustinCredible
JustinCredible

Reputation: 61

Export images from SQL Server

I'm building a huge inventory and sales management program on top of Dynamics CRM 2011. I've got a lot done but I'm kinda stuck on one part:

Images are stored in the database encoded as base64 with a MimeType column. I'm wondering how I might extract those images programmatically on a schedule to be sent as part of a data transfer to synchronize another DB.

I have a SQL Server Agent job that exports a view I created. I'm thinking about writing a program that will take that resultant CSV and use it to get a list of products we need to pull images for, and then it queries the DB and saves the files as say productserial-picnum.ext

Is that the best way to do that? Is there an easier way to pull the images out of the DB and into files?

I'm hoping it will be able to only export images that have changed since say a Last Modified column or something.

I don't know C# at all, VB, PHP and JavaScript enough to do some damage though..

Upvotes: 0

Views: 461

Answers (1)

Krishna
Krishna

Reputation: 2481

you should be able to achieve this in TSQL itself

OPEN cursor with qualifying records (where now>lastmodified etc)

For Each Record
Select Binary Data into "@BinaryData
Convert "@BinaryData to @VarcharData (Something like below will work)
    SET @VarcharData = CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:variable("@BinaryData")))', 'VARCHAR(MAX)')

Write @VarcharData to file (on server or a network drive if the agent is configured to write out)
Close File
Next Record

Upvotes: 1

Related Questions