Retrieve binary file from SQL Server

Question

Retrieve binary file from SQL Server

Confusing Queries

Ten Centuries

Points: 1137
More actions
April 14, 2014 at 7:14 pm

#306480

Hi All,
I have been trying to store binary file in a folder from the SQL Server.
Here is the code I am using to do this but, it is not working. It doesn't show any error only shows 1 row(s) affected.
The folder remains empty after running this query.
I have already configured server using
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
SP_CONFIGURE 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO
DECLARE @File VARBINARY(MAX),
@OpPath VARCHAR(MAX),
@ObjectToken INT
select @File = BinFile FROM Test WHERE RID = 2106469733
SET @OpPath = 'C:\Users\BinaryFile.bin'
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @File
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @OpPath, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
GO
Any help would be appreciated.
Thanks.

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

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Confusing Queries (4/14/2014)
Hi All,
I have been trying to store binary file in a folder from the SQL Server.
Here is the code I am using to do this but, it is not working. It doesn't show any error only shows 1 row(s) affected.
The folder remains empty after running this query.
I have already configured server using
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
SP_CONFIGURE 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO
DECLARE @File VARBINARY(MAX),
@OpPath VARCHAR(MAX),
@ObjectToken INT
select @File = BinFile FROM Test WHERE RID = 2106469733
SET @OpPath = 'C:\Users\BinaryFile.bin'
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @File
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @OpPath, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
GO
Any help would be appreciated.
Thanks.

There's no loop in that. It run's once and that's it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Confusing Queries Ten Centuries Points: 1137 More actions · Answer 2

Jeff Moden (4/14/2014)
There's no loop in that. It run's once and that's it.

I know that it runs once but, once it runs it should dump file into the folder that is been specified, but it is not dumping any file. Is there any mistake in the query?

bgrossnickle Ten Centuries Points: 1332 More actions · Answer 3

This worked for me. I did change 'SP_CONFIGURE 'Ole Automation Procedures', 1' to 'EXEC SP_CONFIGURE 'Ole Automation Procedures', 1' and changed the SELECT statement to 'select top 1 @File = BinFile FROM Test'. I am running @@Version SQL 2008 R2.