Trying to extract data form SQL using PS script

Question

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"

Error appears clear regarding the data problem where the field you are trying to read cannot be called in GetBytes(). What is the data type of extension field? Remember varchar or nvarchar is not the same as text or ntext. Are you saving the binary data of PDF, DOCX, etc. in the database? — Parfait
– Parfait, Commented Dec 3, 2021 at 15:17
Thanks for taking the time to look at this, i managed to figure out the problem is that i was invoking columns that didnt contained the data i was trying to extract, once reviewed and corrected all works fine. Much appreciated — user17581125
– user17581125, Commented Dec 4, 2021 at 15:51

David Browne - Microsoft · Accepted Answer · 2021-12-03 16:40:37Z

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");

Collectives™ on Stack Overflow

Trying to extract data form SQL using PS script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related