Tag: T-SQL Tuesday

This month the invitation is from Wayne Sheffield, asking if you have a PowerShell story for everyone. That’s fitting since Wayne is currently writing a month of PowerShell blogs, and they are worth reading.

The second Tuesday of each month is T-SQL Tuesday, with a new theme each month. If you follow the community on Twitter, you should get an invite every month, and I also keep a history page on my blog that I try to update.

PowerShell

I believe in PowerShell. It’s one of the few directives that Microsoft has taken in their products that I think is both a great idea, and one that will last for a long time. As far as I’ve heard, all their future products in the server space need to support Powershell and include hooks to incorporate it into the administration of those products.

That being said, I don’t use PowerShell (PoSH) a lot. Scripting tasks is good for repetitive actions. However since I work at home and manage a couple instances, with disparate requirements, the time to write a PoSH script seems to outweigh the time it takes just performing a few tasks.

I’m always looking for places that I can use PowerShell, and where it makes sense for me and I found one last year.

Binary Data in SQL Server

I wrote a presentation last year that looked at binary (BLOB) data in SQL Server, and worked with the FileStream/Filetable features. In building a demo, I wanted to show that binary documents in SQL Server work well with Filestream and that you can query the binary fields just like any other. However to render something like an image, you really need to stream the data into an application, or a file.

I chose a file, and built a short PoSH script to do just that. Here’s the script:

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

$server = "SevenFalls"
$database = "AdventureWorks2008"
$query = "SELECT TOP 10 Document, FileName +'.' +  FileExtension FROM Production.Document WHERE Document IS NOT NULL"
$dirPath = "C:\Users\Steve\Documents\Presentations\UnstructuredData\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()

The script works by essentially setting variables to a server, database, and a query. In this case I use the AdventureWorks database from 2008 that includes FileStream data. I then open a connection to the instance, run the command and look through the results, streaming out the data in the “Document” field to a file which has the name from the FileName+FileExtension fields.

If you run this on your system, assuming fix the file paths, you’ll get 9 new documents in the file system, each one a document from the AdventureWorks sample database.

That’s the best example of where I’ve used PowerShell in my daily work, but I know if I had to manage multiple instances, I’d be using this tool all the time.