Script to export SQL Server Blob data with ADO.Net via PowerShell

Below is a shamelessly copied script from Olaf Helper over at TechNet.

If you have a fully configured FileStream SQL Server, Database and Table Column with the FileStream attribute then the following script is much better and avoids possible bugs with buffers and byte reading.  Check that out here on TechNet

I have reproduced here almost entirely for my own purpose of not having to search endlessly for it when I need to grab some records from older SQL database columns that are holding blob data in a varbinary(MAX).

# Configuration data 
$Server = ".";             # SQL Server Instance, local. 
$Database = "DBname"; # Database 
$Dest = "F:\Export\";     # Path to export to. 
$bufferSize = 8192;         # Stream buffer size in bytes.

# Select-Statement for name & blob 
$Sql = "
SELECT [Nom], [Data]
FROM Table
WHERE Value = 1
"; 
 
# Open ADO.NET Connection 
$con = New-Object Data.SqlClient.SqlConnection; 
$con.ConnectionString = "Data Source=$Server;" + 
 "Integrated Security=True;" + 
 "Initial Catalog=$Database"; 
$con.Open(); 
 
# New Command and Reader 
$cmd = New-Object Data.SqlClient.SqlCommand $Sql, $con; 
$cmd.CommandTimeout = 120 
$rd = $cmd.ExecuteReader(); 
 
# Create a byte array for the stream. 
$out = [array]::CreateInstance('Byte', $bufferSize) 
 
# Looping through records 
While ($rd.Read()) 
{ 
 try 
  { 
   Write-Output ("Exporting: {0}" -f $rd.GetString(0)); 
   # New BinaryWriter 
   $fs = New-Object System.IO.FileStream ($Dest + $rd.GetString(0)), Create, Write; 
   $bw = New-Object System.IO.BinaryWriter $fs; 
 
   $start = 0; 
   # Read first byte stream 
   $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1); 
   While ($received -gt 0) 
   { 
    $bw.Write($out, 0,      $received); 
    $bw.Flush(); 
    $start += $received; 
    # Read next byte stream 
    $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1); 
   } 
   $bw.Close(); 
   $fs.Close(); 
  } 
  catch 
  { 
   Write-Output ($_.Exception.Message) 
  } 
  finally 
  { 
   $fs.Dispose();         
  }
 }  
   
# Closing & Disposing remaining objects 
$rd.Close(); 
$cmd.Dispose(); 
$con.Close(); 
 
Write-Output ("Finished");
Read-Host -Prompt "Press Enter to exit"

All credit to the original authors and contributors

Posted in .Net, Microsoft, PowerShell, SQL Server | Leave a comment

Windows 10 Lock screen black after 1 minute

Does your Windows 10 machine power off the screen after 1 minute on the lock screen, leaving you with a black screen after just 1 minute?

Mine did, and it was really irrationally irritating!

There are unfortunately a lot of very similar issues surrounding this and other issues to do with power saving, lock screens and the screen saver which made searching for the right answer very tricky. Lots of advice is given without explanation unfortunately and this makes comparing situations hard.

The fix that worked for me.

The magic setting that worked for me was “Console lock display off timeout” which seems to be carried over from Windows 8/8.1 but oddly remains hidden from view in the advanced power settings in Windows 10.

There are numerous forum posts about setting the registry entry to enable it. However, on my Windows 10 install there appears to be more than one key detailing the change!

There are two registry locations that enable the mystical “Console lock display off timeout”

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Power\PowerSettings\238C9FA8-0AAD-41ED-83F4-97BE242C8F20\7bc4a2f9-d8fc-4469-b07b-33eb785aaca0

Which did not work for me, and

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Power\PowerSettings\7516b95f-f776-4464-8c53-06167f40cc99\8EC4B3A5-6868-48c2-BE75-4F3044BE88A7

Which did!

Set the data of the value “Attributes” to 2

This will enable the Console lock display off timeout in the advanced power options.
Once the registry is updated go to Control Panel > Hardware and Sound > Power Options > Edit Plan Settings and then change advanced power settings. In Advanced settings expand Display > the Console lock display off time-out to set the values you want when on battery or plugged in.

The second registry location is referenced by: http://www.eightforums.com/tutorials/8267-power-options-add-console-lock-display-off-timeout.html

Your mileage may vary given how much fun I had. It was compounded by the fact there are a few other places that you may well think should control this setting. I have detailed the online responses that didn’t work.

Other suggested fixes that don’t work

  • This issue is not the same problem as people’s machines actually locking after 1 minute of inactivity. That is a separate issue that seems to be related to preview editions of Windows.
  • Editing the Group Policy
    gpedit.msc > Local group Policy Editor\Local Computer Policy\Computer Configuration\Windows Settings\Security Settings\Local Policies\Security Options\Interactive Logon: Machine inactivity Limit = 1200 seconds (20 minutes)
    *This is an auto lock policy from what I can see, not too much of an issue if you set it. Default is 0 for never.
  • gpedit.msc > User Configuration > Administrate templates > Control Panel > Personalization
    • Enable screen saver
    • Screen saver timeout
    *Neither of these option had any effect and only confirmed the settings available to users in the usual places.
  • Altering the screen saver from “None” to another screen saver and back again
    *No effect, screen saver never kicks in before 1 minute
  • Right click on your desktop-> Personalise-> Screen Saver and setting time-out to more than 1
    *This should work, and is a logical starting point so but it didn’t
  • Altering the power profile to high performance
    *No noticeable change
  • Updating display drivers
    *Last chance pretty much, also why are you not up to date already?
  • Sacrificing USB memory sticks to the Gods of Windows
    *Thankfully not required this time!
Posted in Microsoft, Windows | Leave a comment