Checking data consistency for a varbinary column
I am inserting .jpg
into a column varbinary(max)
in SQL Server 2012 using the following command:
INSERT INTO Employees
VALUES(5, (SELECT * FROM OPENROWSET(BULK N'C:\4.jpg',SINGLE_BLOB) AS image))
Inserts a record into the table.
Then I used the following command to restore the file from db to file system.
BCP "SELECT pic FROM Employees where id=5" queryout "C:\51.jpg" -N -T
When I try to view the uploaded file using an image viewer, I get the error
The file is damaged.
Any ideas?
+3
source to share
1 answer
This worked for me: How to export an image field to a file?
The short version without a cursor looks like this:
DECLARE @ImageData VARBINARY(max)
DECLARE @FullPathToOutputFile NVARCHAR(2048);
SELECT @ImageData = pic FROM Employees WHERE id=5
SET @FullPathToOutputFile = 'C:\51.jpg'
DECLARE @ObjectToken INT
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT;
EXEC sp_OASetProperty @ObjectToken, 'Type', 1;
EXEC sp_OAMethod @ObjectToken, 'Open';
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @ImageData;
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FullPathToOutputFile, 2;
EXEC sp_OAMethod @ObjectToken, 'Close';
EXEC sp_OADestroy @ObjectToken;
Before you can do this, you will only execute it once in your database:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
All credits to @mathijsuitmegen :) thanks for this working solution
0
source to share