Post reply

Export TIFF files from DB

  • December 15, 2015 at 6:20 am

    #150423

    Hi,

    I got a database which contains in a table, a column contains files.

    The column is from type image and contains documents of PDF or TiFF.

    The PDF I succeeded to export by BCP but the TIFF files I get a small corrupted file.

    Does anybody know how to export TIFF files from the database? Is there any common why which somebody zipped it into the DB?

    BTW, I don't have of course the source code...

  • February 24, 2016 at 9:24 am

    #1860784

    The TIFF file should be in a binary format (binary or varbinary). This means you need to extract the binary data and write it to a file.

    I'm guessing that you have a filename or some way to generate one from the rows? If not, you need something.

    I demonstrate this in one of my talks on Filestream, where there is binary data in AdventureWorks. IF you have Adventureworks 2008 with Filestream, you can use this PoSh code to extract the Word documents. Feel free to modify and use to get your TIFFs out.

    $server = "JollyGreenGiant"

    $database = "AdventureWorks2008"

    $query = "SELECT TOP 10 Document, FileName FROM Production.Document WHERE Document IS NOT NULL"

    $dirPath = "D:\Documents\Docs\"

    $connection=new-object System.Data.SqlClient.SQLConnection

    $connection.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $server,$database

    $command=new-object system.Data.SqlClient.SqlCommand($query,$connection)

    $command.CommandTimeout=120

    $connection.Open()

    $reader = $command.ExecuteReader()

    while ($reader.Read())

    {

    $sqlBytes = $reader.GetSqlBytes(0)

    $filepath = "$dirPath{0}" -f $reader.GetValue(1)

    $buffer = new-object byte[] -ArgumentList $reader.GetBytes(0,0,$null,0,$sqlBytes.Length)

    $reader.GetBytes(0,0,$buffer,0,$buffer.Length)

    $fs = new-object System.IO.FileStream($filePath,[System.IO.FileMode]'Create',[System.IO.FileAccess]'Write')

    $fs.Write($buffer, 0, $buffer.Length)

    $fs.Close()

    }

    $reader.Close()

    $connection.Close()

  • February 25, 2016 at 5:34 am

    #1861004

    Wowwww thank you!!!

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

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