Trying to extract data form SQL using PS script

I have been trying to get a PS script to work in extracting files (pdf, word, etc.) from an SQL Server database. I came across the PowerShell script below. The script runs and populates the destination folder but all files are 0 bytes and during the script execution. It throws the error:

“Exporting Objects from FILESTREAM container: .docx Exception calling “GetBytes” with “5” argument(s): “Invalid attempt to GetBytes on column ‘extension’. The GetBytes function can only be used on columns of typ e Text, NText, or Image.””

Can anyone point me in what am I doing wrong and how to fix this please? Much appreciated.

$Server = ".xxxxxx";             
$Database = "xxxxxx"; 
$Dest = "C:DATA";     
$bufferSize = 8192;       


$Sql = "
SELECT 
      [extension]
      
  FROM [XXXXXXXX].[dbo].[XXXXXXdocuments]
"; 
 
 
$con = New-Object Data.SqlClient.SqlConnection; 
$con.ConnectionString = "Data Source=$Server;" + 
 "Integrated Security=True;" + 
 "Initial Catalog=$Database"; 
$con.Open(); 

Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Started ..."); 
 
 
$cmd = New-Object Data.SqlClient.SqlCommand $Sql, $con; 
$cmd.CommandTimeout = 120 
$rd = $cmd.ExecuteReader(); 
 
 
$out = [array]::CreateInstance('Byte', $bufferSize) 
 
 
While ($rd.Read()) 
{ 
 try 
  { 
   Write-Output ("Exporting Objects from FILESTREAM container: {0}" -f $rd.GetString(0)); 
 
   $fs = New-Object System.IO.FileStream ($Dest + $rd.GetString(0)), Create, Write; 
   $bw = New-Object System.IO.BinaryWriter $fs; 
 
   $start = 0; 
 

    enter code here

   $received = $rd.Getbytes(0, $start, $out, 0, $bufferSize - 1); 
   While ($received -gt 0) 
   { 
    $bw.Write($out, 0,      $received); 
    $bw.Flush(); 
    $start += $received; 
 
    $received = $rd.Getbytes(0, $start, $out, 0, $bufferSize - 1); 
   } 
   $bw.Close(); 
   $fs.Close(); 
  } 
  catch 
  { 
   Write-Output ($_.Exception.Message) 
  } 
  finally 
  { 
   $fs.Dispose();         
  }
 }  
   
 
$rd.Close(); 
$cmd.Dispose(); 
$con.Close(); 
 
Write-Output ("Finished");
Read-Host -Prompt "Press Enter to exit"

Answer

BinaryWriter is unnecessary. It’s for writing primitive types to a Stream.

And there’s no need to muck around with buffers; you can simply use SqlDataReader.GetStream(int).CopyTo(Stream), eg

$Server = "localhost";             
$Database = "adventureworks2017"; 
$Dest = "C:temp";     

$Sql = "
SELECT concat('photo', ProductPhotoID, '.jpg') name, LargePhoto from Production.ProductPhoto
"; 
 
 
$con = New-Object Data.SqlClient.SqlConnection; 
$con.ConnectionString = "Data Source=$Server;Integrated Security=True;Initial Catalog=$Database;TrustServerCertificate=true"; 
$con.Open(); 

Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Started ..."); 
  
$cmd = New-Object Data.SqlClient.SqlCommand $Sql, $con; 
$cmd.CommandTimeout = 120 
$rd = $cmd.ExecuteReader(); 

 

While ($rd.Read()) 
{ 
 try 
  { 
   Write-Output ("Exporting: {0}" -f $rd.GetString(0)); 
 
   $fs = New-Object System.IO.FileStream ($Dest + $rd.GetString(0)), Create, Write; 
   $rd.GetStream(1).CopyTo($fs) 
   $fs.Close()
  } 
  catch 
  { 
   Write-Output ($_.Exception.Message) 
  } 
  finally 
  { 
   $fs.Dispose();         
  }
 }  
   
 
$rd.Close(); 
$cmd.Dispose(); 
$con.Close(); 
 
Write-Output ("Finished");