Inserting Binary Objects Into A SQL Server Table Using A Stored Procedure And PowerShell



Our team was tasked with putting scanned expense report receipts (.PDF) in a SQL Server table for review.  As the DBA I thought my part was pretty straight forward.  I knocked out the table and associated objects including the stored procedures to service the table and was enjoying a cup of coffee when I realized I had not tested my insert procedure.  Then it dawned on me – I had no way to test it in SSMS.  How can you use a text based tool to manipulate binary files into the database?  If you’ve figured that one out I’d love to see it.  If you haven’t then perhaps my PowerShell approach will be of some use to you.

First let’s create some basic SQL objects, a table and a stored procedure:

CREATE TABLE TAPTEST (Col1 VARBINARY(MAX) NULL)

GO

CREATE PROCEDURE usp_TAPTEST @Col1 VARBINARY (MAX) AS

      INSERT INTO dbo.TAPTEST (Col1) VALUES (@Col1)

GO

Here is the PowerShell code if you are in a hurry but it’s worth going through line by line as we will do below.

#Get the file

      [Byte[]]$file = get-content -Encoding Byte c:\Temp\TAPTEST.pdf 

#Connect to DB   

      $DB = “server=MyInstanceName;integrated security=sspi;database=Bob”

      $conn = new-object System.Data.SqlClient.SqlConnection($DB)

#Build the command and parameters

      $cmd = new-object System.Data.SqlClient.SqlCommand(usp_TAPTEST”,$conn)

      $cmd.CommandType = [System.Data.CommandType]StoredProcedure

      $cmd.Parameters.Add(“@Col1”, [System.Data.SqlDbType]VarBinary)

      $cmd.Parameters[“@Col1”].Size = 1

      $cmd.Parameters[“@Col1”].Value = $file

#Execute the command

      $conn.Open()

      $cmd.ExecuteNonQuery()

 Taking a closer look.

#Get the file

      [Byte[]]$file = get-content -Encoding Byte c:\Temp\TAPTEST.pdf

 The square brackets force the data type for the $file variable.  Additionally, since Get-Content normally reads the file as text the –Encoding parameter is used to tell it to read it as binary.

Connecting to the database is just as you would do for any other SMO connection, nothing special here.

#Connect to DB   

      $DB = “server=MyInstanceName;integrated security=sspi;database=Bob”

      $conn = new-object System.Data.SqlClient.SqlConnection($DB)

Building the command and parameter objects was a little more interesting.

 

#Build the command and parameters

      $cmd = new-object System.Data.SqlClient.SqlCommand(usp_TAPTEST”,$conn)

 

Note the command text above:  usp_TAPTEST which will be executed at the server.  Any valid DML should execute, including the following. usp_TAPTEST @Col1 = 1″ which works great as long as you have a printable data type.  If it weren’t for reading the file into a Byte data type in the first line we’d be stuck again.   So how DO you tell it this is a binary object?  Read on.

In the first line below we tell PowerShell to treat the CommandText as a stored procedure.   Then we set the first parameter’s data type to varbinary.  Setting the size parameter to -1 tells it that the size is MAX  Finally, set the value of the parameter to the file you read in initially.

 

      $cmd.CommandType = [System.Data.CommandType]StoredProcedure

      $cmd.Parameters.Add(“@Col1”, [System.Data.SqlDbType]VarBinary)

      $cmd.Parameters[“@Col1”].Size = 1

      $cmd.Parameters[“@Col1”].Value = $file

 At this point you are ready to go, just open the connection and execute.

#Execute the command

      $conn.Open()

      $cmd.ExecuteNonQuery()

 

And voila!

Notice the French lingo – this is a high class blog!

 

 

 

 

 

As you can see, the code is pretty simple with the only tricky part having to do with fitting the binary nature of the document with the parameter used by the SQL Command object.

Hopefully you will find this useful both as stand-alone code and as another step in the drive for PowerShell mastery.

 

Advertisements

Using PowerShell and SMO to Script SQL Server Database Objects

I’ve been telling myself to learn PowerShell for quite awhile but I’ve never been able to get over the learning curve.  What I needed was a piece of work that was bigger than trivial and smaller than huge that would force me into PowerShell.  It came along this week in the form of a request to script all the views in a test database before the refresh from production and then “putting them back” into the new test database.  Doing this from SSMS is pretty easy but I sure don’t want to do it every time we refresh.  I needed something repeatable, simple, and parameter driven. 

 I knew PS would be relatively simple to manage so I looked into using PS with SQL Server Management Objects (SMO).  First I searched the Web for something like “PowerShell SMO Script Objects” and found this, which pointed me in the right direction. 

The complete script is at the bottom of the article but I’ve broken it up into sections which I’ll cover in detail.  My focus is those of us learning PS so if you are a guru please be patient with the rest of us.

 

Figure 1

All the user configurable parameters can be set in Figure 1 above.  In line 13 note that $ObjType will be inserted as a literal in the code so PowerShell will read MyObject.$ObjType but execute MyObject.views.  This allows you to script tables, views, stored procedures, and other Db objects by simply changing the parameters.  For even more flexibility you could read in parameters from a text file.

Also note in line 15 that the “@” indicates a list of string objects that will be excluded from the script.  You could also add other schemas if you wish.

Finally, the If block (lines 18-21) tests for the output file and deletes it if it exists.  Without this check successive runs of the script would append the same scripts to the file.

 

Figure 2

Next, let’s look at the code to create the SMO we’ll use to script our objects.  Line 25 tells PowerShell which assembly we want to use so it can create the objects we need.  Lines 28 and 29 create the Server and Databases collection objects respectively.  Note that the Server object takes the instance name on creation.

Once you have the DB collection available you are almost ready to start scripting.  The Script method is available for each object (table, view, etc.) but there are a number of options you can use to control the scripts generated.  For example, if you want to test for existence before creating an object you would set the IncludeIfNotExists = 1.  To use the option you will create a ScriptingOptions object (line 32) and set various properties as needed.  A complete list of options is here.

If you want to perform multiple scripting tasks, say Drop and then Create you can perform the scripting multiple times, appending the results as we see in the following two code blocks.

 

Figure 3

First you set the ScriptingOptions as in Figure 3 for the operation you want to perform (lines 38 & 50).  Then you iterate through every object of the specified type in the database using a foreach loop (lines 41 & 53).  Each time the foreach starts it filters out the object if the schema is any one of the values passed in from the list discussed above.

Without the whitespace, the whole PowerShell script takes up only about 20 lines.  As you can see, PowerShell and SMO together make a very powerful tool to put in your toolbox.    I’d be interested to hear about how you modified this code to meet your own needs.

Here is the whole script, ready to use with your parameters.

##########################################################
# PS Script DB Objects
#
# Uses PS and SMO to script the drop and create a type of
# SQL Server object (views, tables, etc.)
#
# Tom Powell                                       2/24/2011
##########################################################
 
#Setup####################################################
    $SQLInstance = “Server\Instance”
    $DB = “DBName ”
    $ObjType = “views”
    $OutFile = “C:\temp\Scripts.sql”
    $ExcludeSchemas = @(“sys”,”Information_Schema”) #List of schemas to exclude
   
#Create smo objects##########################################
    [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
   
    #SQL Instance & DB object
    $s = new-object (‘Microsoft.SqlServer.Management.Smo.Server’) $SQLInstance
    $dbs=$s.Databases
   
    #ScriptingOption object to pass parms for Script method
    $so = new-object (‘Microsoft.SqlServer.Management.Smo.ScriptingOptions’)
    $so.IncludeIfNotExists = 1
    $so.SchemaQualify = 1
    $so.AllowSystemObjects = 0

#Script Drop Objects###########################################
    $so.ScriptDrops = 1         #Script Drop Objects

    #Generate script for all objects##################################
    foreach ($objs in $dbs[$DB].$ObjType)
    {
        If ($ExcludeSchemas -notcontains $objs.Schema )  
        {
            $objs.Script($so) + “`r GO `r ” | out-File $OutFile #-Append
        }
    }

#Script Create Objects#########################################
    $so.ScriptDrops = 0         #Script Create Objects

    #Generate script for all objects#################################
    foreach ($objs in $dbs[$DB].$ObjType)
    {
        If ($ExcludeSchemas -notcontains $objs.Schema )
        {
            $objs.Script($so) + “`r GO `r ” | out-File $OutFile -Append
        }
    }