Tập lệnh để lưu dữ liệu varbinary vào đĩa

Cách tiếp cận BCP không phù hợp với tôi. Các byte nó ghi vào đĩa không thể được giải mã trở lại các đối tượng .net mà tôi đã lưu trữ. Điều này có nghĩa là các byte trên đĩa không tương đương với những gì được lưu trữ. Có lẽ BCP đang viết một số loại tiêu đề. Tôi không chắc.

Tôi tìm thấy mã sau đây ở cuối bài viết. Nó hoạt động tuyệt vời! Mặc dù nó được thiết kế cho các hình ảnh BMP được lưu trữ, nhưng nó hoạt động với bất kỳ varbinary nào.

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

Tôi đang thêm điều này để xây dựng trên câu trả lời của JohnOpincar , để những người khác muốn sử dụng LinqPad có thể nhận được giải pháp làm việc nhanh hơn.

/*
This LinqPad script saves data stored in a VARBINARY field to the specified folder.
1. Connect to SQL server and select the correct database in the connection dropdown (top right)
2. Change the Language to C# Program
3. Change "Attachments" to the name of your table that holds the VARBINARY data
4. Change "AttachmentBuffer" to the name of the field that holds the data
5. Change "Id" to the unique identifier field name
6. Change "1090" to the identity of the record you want to save
7. Change the path to where you want to save the file. Make sure you choose the right extension.

Notes: Windows 10 may give you "Access Denied" error when trying to save directly to C:\. Rather save to a subfolder.
*/

void Main()
{
    var context = this;
    var query = 
        from ci in context.Attachments
        where ci.Id == 1090
        select ci.AttachmentBuffer
    ;
    byte[] result = query.Single().ToArray();
    File.WriteAllBytes(@"c:\DEV\dumpfile.xlsx", result);
    Console.WriteLine("Done");
}

Bạn có thể sử dụng BCP, không phải T-SQL, nhưng hoạt động tốt.

BCP "SELECT FileContent FROM table WHERE ID = 1" queryout "C:\file.txt" -T

Tôi biết đó là một bài đăng cũ, nhưng tôi đã tìm ra lý do tại sao phần sau không hoạt động và cách khắc phục nó:

BCP "SELECT FileContent FROM table WHERE ID = 1" queryout "C:\file.JPG" -T -N

Lý do là bcp đặt Độ dài tiền tố ở đầu tệp. Nó là 4 byte hoặc 8 byte, tùy thuộc vào kiểu dữ liệu của cột FileContent (văn bản, ntext, hình ảnh: 4 varchar (max), varbinary (max): 8 Tham khảo https://msdn.microsoft.com/en-us /library/ms190779.aspx )

Sử dụng trình chỉnh sửa nhị phân, như trình chỉnh sửa trong Visual Studio, để xóa các byte tiền tố và mọi thứ chạy hoàn hảo. :-)

Nếu bạn có linqpad, điều này hoạt động:

void Main()
{
    var context = this;
    var query = 
        from ci in context.Images
        where ci.ImageId == 10
        select ci.Image
    ;
    var result = query.Single ();
    var bytes = Convert.FromBase64String(result);
    File.WriteAllBytes(@"c:\image.bmp", bytes);
}

Chỉ là một sự thay thế. Bạn có thể sử dụng Toad phần mềm miễn phí cho máy chủ SQL và lưu trực tiếp từ trình chỉnh sửa.

Bạn có thể truy cập trang web của họ https://www.toadworld.com và nhận phần mềm miễn phí ở đó hoặc dùng thử 30 ngày phiên bản đầy đủ. Trong phần Tải xuống và chọn Cóc cho máy chủ SQL.

Bạn thực hiện một câu lệnh select thông thường trong Toad trên dòng có hình ảnh bạn muốn lưu. Khi bạn thấy kết quả, bạn có thể nhấp vào cột hình ảnh byte và ở bên phải, bạn sẽ thấy tab PDF nếu đây là tài liệu PDF hoặc ở bên trái, bạn thấy tab Hình ảnh. Khi nhấp vào tab, bạn có thể thấy biểu trưng lưu ở dưới cùng để lưu hình ảnh hoặc tệp.

Với Powershell

function SQLExecuteScalar([string]$pServer, [string]$pDatabase, [string]$pQuery)
{
    # Connection
    $pSQLConnection = New-Object System.Data.SqlClient.SqlConnection
    $pSQLConnection.ConnectionString = "Data Source=$($pServer);Initial Catalog=$($pDatabase);Integrated Security=SSPI;Application Name=FileExtractor.Powershell"
    $pSQLConnection.Open()

    # Command
    [System.Data.SqlClient.SqlCommand]$cmd = New-Object System.Data.SqlClient.SqlCommand($pQuery, $pSQLConnection)

    # Execute and Get scalar value
    [byte[]]$return = $cmd.ExecuteScalar()
    
    # Close Connection
    $pSQLConnection.Close()

    # Result to pipe
    return $return
}

[string]$Server = "MyServer"
[string]$DataBase = "MyDb"
[string]$Query = "select BlobValue from dbo.MyTable"
[string]$FileName = "C:\Temp\BlobValue.bin"

SQLExecuteScalar -pServer $Server -pDatabase $DataBase -pQuery $Query | Set-Content $FileName -Encoding Byte

SQL được thiết kế để hoạt động với các đối tượng cơ sở dữ liệu, vì vậy theo quan điểm của nó, bất kỳ thứ gì khác không tồn tại. Chắc chắn, có những thủ tục mở rộng như xp_cmdshellvậy cho phép bạn tương tác với hệ điều hành, nhưng chúng là những phần mở rộng độc quyền và không phải là một phần của T-SQL.

Có thể cách tiếp cận gần nhất sẽ là sử dụng thuộc tính FILESTREAM cho các kiểu nhị phân của SQL Server 2008, cho phép lưu trữ một số cột trực tiếp dưới dạng tệp trong một thư mục thay vì sử dụng cơ sở dữ liệu:

Tổng quan về FILESTREAM

Lưu ý rằng bộ lưu trữ FILESTREAM được thiết kế để duy trì các tệp lớn bên ngoài cơ sở dữ liệu nhằm tăng hiệu suất và không cho phép truy cập trực tiếp vào tệp (tức là T-SQL vẫn chưa có khái niệm về hệ thống tệp). Theo tôi, việc truy cập trực tiếp vào hệ thống tệp từ SQL sẽ đánh bại một số mục đích của cơ sở dữ liệu (chủ yếu là dữ liệu được lưu trữ theo cách có cấu trúc).

Vì vậy, tôi khuyên bạn nên làm theo lời khuyên của Dustin và sử dụng một công cụ như BCP hoặc bất kỳ công cụ kết xuất dữ liệu nào khác.