We have images stored in a SQL database as the image datatype.  I need to pull these images out of the SQL Table and save them as a .jpg file.  I have been able to pull the data from SQL but cant figure out how to save it as normal image file.  With a bit of search and cut\paste I have ended up with the following.  Not sure if I am on the right path here but saving the data into a Memory Stream and then trying to save that as an image with System.drawing.image.  Currently stuck on using the System.drawing.image with the following error message.  Thanks.

Exception calling "FromStream" with "1" argument(s): "Parameter is not valid."

 

$Result = $ds.tables[0].Rows[1]
$bytes = $Result.LNL_BLOB
[void][reflection.assembly]::LoadWithPartialName("System.IO")
$memoryStream = new-object System.IO.MemoryStream
$memoryStream.write($bytes,78,$bytes.Length - 78)

## on down cant seem to figure out how to use system.drawing.image

[void][reflection.assembly]::LoadWithPartialName("System.Drawing")
$Image = [System.Drawing.Image]::FromStream

 

 

 

Hi Rich,

Maybe this help:

$Server = "SQLInstance"          
$Database = "testowa"        
$Dest = "c:\Export\"             
$bufferSize = 8192               
        
$sqlCommand = "SELECT * FROM obrazy WHERE (id = '1520')"            
            
$authentication = "Integrated Security=SSPI;"          
$connectionString = "Provider=sqloledb; " +
	                "Data Source=$dataSource; " +
	                "Initial Catalog=$database; " +
	                "$authentication; "


$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sqlCommand,$connection
$connection.Open() 
       
$reader = $command.ExecuteReader()            
$out = [array]::CreateInstance('Byte', $bufferSize)            

While ($reader.Read())            
{            
    $fileStream = New-Object System.IO.FileStream ($Dest + $reader.GetString(0)), Create, Write            
    $binaryWriter = New-Object System.IO.BinaryWriter $fileStream            
               
    $start = 0            
    $received = $reader.GetBytes(1, $start, $out, 0, $bufferSize - 1)            
    While ($received -gt 0)            
    {            
       $binaryWriter.Write($out, 0, $received)            
       $binaryWriter.Flush()            
       $start += $received            
       $received = $reader.GetBytes(1, $start, $out, 0, $bufferSize - 1)            
    }            
            
    $binaryWriter.Close()            
    $fileStream.Close()            
}            
            
$fileStream.Dispose()           
$reader.Close()            
$command.Dispose()            
$connection.Close()            

 Source:

http://social.technet.microsoft.com/wiki/contents/articles/export-sql-server-blob-data-with-powershell.aspx

• Marked as answer by Tiger LiMicrosoft employee Tuesday, November 01, 2011 7:53 AM

Thanks, I played around with that sample today.  Installed AdventureWorks DB to try it out as posted.  Could not get the sample to work as is.   

Just figured it out finally.  I was getting a exception because my $memorystream was not right due to this line

 $memoryStream.write($bytes,78,$bytes.Length - 78) 

Once I changed it to $memoryStream.write($bytes,0,$bytes.Length) it fed in all the image data properly so that

$Image = [System.Drawing.Image]::FromStream($memorystream)

worked without an exception error. 

• Marked as answer by Tiger LiMicrosoft employee Tuesday, November 01, 2011 7:53 AM

Michal, your sample uses $datasource when it should be $server.

Also, I get the following error:

"Exception calling "GetBytes" with "5" argument(s): "Index was outside the bounds of the array.""