How to Export Image column to file on MSSQL II

Another way how to export binary data from database. It is possible to use bcp utility.

bcp "SELECT img FROM db_name.dbo.Images WHERE ID = 1" queryout "c:\001.jpg" -T -S server

Once you run it you will be asked for the file storage type (no change required), prefix-length (needs to be 0), length of field and field terminator (no change)

Update of XML data

Code snippet for updating specific data in xml column in mssql

declare @date datetime
set @date = GETDATE()

UPDATE XmlDocuments
SET    xmlmetadata.modify ('replace value of (/Header/Date/text())[1] with sql:variable("@date")') 
WHERE ID = '1000001'

Bulk update of Image data in MSSQL

This is just simple example how to do update of image data in Microsoft SQL database.

UPDATE Images SET Img = (SELECT BulkColumn AS Img FROM OPENROWSET(BULK N'C:\NoImage.TIF', SINGLE_BLOB) AS [Document])

How to Export Image column to file on MSSQL

It is not that easy how it looks like:) Used the same approach as it is in the answer below but completed the example.

1. Enable the extended stored procedures:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

2. Use sp_OA stored procedures

DECLARE @objStream INT
DECLARE @imageBinary VARBINARY(MAX)
DECLARE @filePath VARCHAR(8000)

SELECT @imageBinary = img
FROM Images
WHERE ID = 1

SET @filePath = 'c:\img_1.jpg'

EXEC sp_OACreate 'ADODB.Stream', @objStream OUTPUT
EXEC sp_OASetProperty @objStream, 'Type', 1
EXEC sp_OAMethod @objStream, 'Open'
EXEC sp_OAMethod @objStream, 'Write', NULL, @imageBinary
EXEC sp_OAMethod @objStream, 'SaveToFile', NULL,@filePath, 2
EXEC sp_OAMethod @objStream, 'Close'
EXEC sp_OADestroy @objStream 

Resources:
How to export a ms sql image column to a file

I didn't have much time to write an article so I decided to post a list of issues I needed to resolve. Hope it can help other people with their issues too.

Reporting services
Report server report viewer 404 - File or directory not found

MSSQL server
Understanding locking in SQL server
Anatomy of deadlock
Deadlock troubleshooting, Part 1
Deadlock troubleshooting, Part 2
Deadlock troubleshooting, Part 3

ASP.NET
Getting :hover to Work in IE7
ASP.NET Menu and IE8 rendering white issue

General
Clipboard is not working