Retrieve binary file from SQL Server

  • 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.

  • 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)

  • 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?

  • 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.

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

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