将 varbinary 数据保存到磁盘的脚本 [英] Script to save varbinary data to disk

问题描述

我有一些 varbinary 数据存储在 MS Sql Server 2005 的表中.有没有人有将查询作为输入的 SQL 代码(假设查询保证返回单列 varbinary)并将字节输出到磁盘(每行一个文件?)我确定这之前已经被问过一千次了，但是谷歌搜索主要是 .net 解决方案.我想要一个 SQL 解决方案.

I have some varbinary data stored in a table in MS Sql Server 2005. Does anyone have SQL code that takes a query as input (lets say the query guarantees that a single column of varbinary is returned) and outputs the bytes to disk (one file per row?) I'm sure this has been asked a thousand times before, but Googling comes up with mostly .net solutions. I want an SQL solution.

推荐答案

BCP 方法对我不起作用.它写入磁盘的字节不能反序列化回我存储的 .net 对象.这意味着磁盘上的字节不等于存储的字节.也许 BCP 正在编写某种标题.我不知道.

The BCP approach does not work for me. The bytes it writes to disk cannot be deserialized back to the .net objects I stored. This means that the bytes on disk aren't equivalent to what's stored. Perhaps BCP is writing some kind of header. I'm not sure.

我在文章底部此处找到了以下代码.它工作得很好！尽管它用于存储的 BMP 图像，但它适用于任何 varbinary.

I found the following code here at the bottom of the article. It works great! Although it was intended for stored BMP images, it works with any varbinary.

DECLARE @SQLIMG VARCHAR(MAX),
    @IMG_PATH VARBINARY(MAX),
    @TIMESTAMP VARCHAR(MAX),
    @ObjectToken INT

DECLARE IMGPATH CURSOR FAST_FORWARD FOR 
        SELECT csl_CompanyLogo from mlm_CSCompanySettingsLocalizations

OPEN IMGPATH 

FETCH NEXT FROM IMGPATH INTO @IMG_PATH 

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @TIMESTAMP = 'd:\' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','') + '.bmp'

        PRINT @TIMESTAMP
        PRINT @SQLIMG

        EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
        EXEC sp_OASetProperty @ObjectToken, 'Type', 1
        EXEC sp_OAMethod @ObjectToken, 'Open'
        EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
        EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
        EXEC sp_OAMethod @ObjectToken, 'Close'
        EXEC sp_OADestroy @ObjectToken

        FETCH NEXT FROM IMGPATH INTO @IMG_PATH 
    END 

CLOSE IMGPATH
DEALLOCATE IMGPATH

这篇关于将 varbinary 数据保存到磁盘的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！