SQL Server & PowerShell – Finding and Changing Job Owners

We’ve been hearing for a long time that PowerShell (PS) is the wave of the future for SQL Server administration. Under the covers SSMS itself is powered by PS so there ought to be benefits but how do you get started. Everybody has seen how you can right-click a node in the SSMS Object Explorer and jump right in to PS.

But then what?

Let’s consider a small administrative task. Fred creates a bunch of jobs without setting the job owner so he owns them all. He decides to seek greener pastures and when his account is dropped the jobs quit working.

I know you all have (or should have) a script for finding and changing the owner of certain SQL Agent jobs on a SQL instance. They all use a cursor (or something like it) to query msdb.dbo.sysjobs and step through the results, executing msdb.dbo.sp_Update_Job where appropriate. This certainly works but it’s kind of a kludge. Wouldn’t it be more intuitive to say something like “Look at all the jobs, where ‘Fred’ is the owner then change the owner to ‘sa’.”?

That’s really all you do in PS and you can find examples from Mr. Google. However, PowerShell’s ability to use shortcuts and abbreviations often makes figuring out what’s really happening like making sense of alphabet soup.

Here are two snippets of code. The first lists all the jobs on an instance with their owners and the second finds all the jobs with a certain owner and changes that owner to another. Let’s unpack each and see what’s going on.

dir | FT Name, OwnerLoginName –auto

dir” is an alias for “Get-ChildItem” (Also abbreviated “GCI”). It functions like the “dir” command at the DOS prompt – it looks at the current location and returns everything “below”. The important difference is that it doesn’t return a string about the objects, it returns the objects themselves. Keep that in mind as we go on.

|” The pipe character represents the PS pipeline which hands the object(s) to the next command. In our case we are passing all the objects returned by Get-ChildItem to the next command. See this link for more information on the pipeline.

FT” is short for “Format-Table” and is used to format output. The comma separated arguments “Name” and “OwnerLoginName” tell FT which columns to return and the “-auto” automatically prettifies the output for human consumption.

Follow the above screenshots and paste this line of code into the PowerShell window and hit “Enter” to see it for yourself.

Now that we can see who owns each job lets change some owners.

dir | ?{$_.OwnerLoginName –eq “Fred”} | %{$_.OwnerLoginName = “sa”; $_.Alter(); $_.Name}

dir|” functions as above but remember the actual objects are being passed, not just a property value (name, owner, etc.). In this case we don’t need all the jobs, just the ones Fred owns.

?” is an alias for the “Where-Objects” commandlet which filters for the objects you want. The curly brackets (“{}“) contain the script block where you put the filter logic. “$_.” refers to each object coming through the pipe and “OwnerLoginName” is the attribute examined as it passes through the filter. “-eq” is the equality operator (=) while ‘“Fred”‘ is the string it is trying to match.

After this the objects that met the filter criteria are piped to the next command. Again we have an alias, “%“. This is short for “ForEach-Object” and allows you to iterate the objects and change properties or execute methods. In this case we will set (“=“) the “OwnerLoginName” value to the string “sa”. The “Alter()” is a method call that updates any changes. Finally the “Name” prints out the name of each job changed.

As you can see, PowerShell uses an intuitive and object oriented approach to working with SQL Server objects though sometimes shortcuts and aliases can make it a little hard for beginners to interpret. It’s well worth the time to take a careful look at any scripts you download so you know exactly what’s going on. PowerShell has a very nice help system that allows you to do this research. Look here if you’d like some help on Help.

Advertisements

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.

 

Tables and Files – What’s the Connection?

What is the relationship between a table and the files that contain it?  This isn’t as easy a question as you might think because there is not a direct relationship between a logical object like a table and a physical object like a file.  For a small database like AdventureWorks (< 200 MB) all you need is a single data file to hold all your tables.  As things start to get larger and more complex you may add files as a way of increasing performance.  For example if you have a single table that is large and heavily queried you may want to put it on a faster (and probably more expensive) drive.  You may also have non-clustered indexes that cover your query that would benefit from being separated.  If terms like non-clustered index and covering query are unfamiliar you might benefit from this excellent article by Joe Webb.

So, how do you control table and index placement?  You do it by using filegroups.  Every relational index (clustered or non-clustered) is assigned to a single filegroup that is composed of one or more files.  The relationship looks like this:

In the picture above the Table01 is actually the clustered index and Table01/Index01 is a non-clustered index.  Note that a file can be in exactly one filegroup, but the components of the table can be located and managed through the use of different filegroups.
Here is a script you can use to explore these relationships:

–Create a test DB.  Does the DB exist?  Drop if it does.
            Use tempdb
            Go
            If Exists (Select name from sys.databases Where name = ‘TabFile’)
                        Begin
                                    Drop Database TabFile
                        End
            Go

–Create DB with two filegroups.  You will need a “C:\temp”
–folder to use the script as written.
            Create Database TabFile On
                        Primary                                                                                                –FileGroup
                                    ( Name = N’TabFileF01′,
                                                Filename = N’C:\Temp\TabFileF01.mdf’),
                         Filegroup FG02                                                                               –FileGroup
                                    ( Name = N’TabFileF02′,                                                   –File
                                                Filename = N’C:\Temp\TabFileF02.ndf’ ),
                                    ( Name = N’TabFileF03′,                                                   –File
                                                Filename = N’C:\Temp\TabFileF03.ndf’)
                         Log ON                                                                                                –Log
                                    ( Name = N’TabFile_log’,
                                    Filename = N’C:\Temp\TabFile_log.ldf’)
            Go

–Create table with two indexes
            Use TabFile
            Go
            Create Table dbo.Table_1
                        (Col1 int Constraint PK_Table_1 Primary Key Clustered Not Null Identity (1, 1),
                        Col2 int Not Null)  On [Primary]
            Go
            Create Nonclustered index IX02_Table_1 ON dbo.Table_1
                        (Col1     ) On FG02
            Go
            Create Table dbo.Table_2
                        (Col1 int Constraint PK_Table_2 Primary Key Clustered Not Null Identity (1, 1),
                        Col2 int Not Null)  On FG02
            Go

At this point you can take a look at the various structures using either the Object Explorer in SSMS or TSQL.  I prefer TSQL because it is repeatable and far more flexible.  The following script shows the relationship between tables, filegroups, and files.

–View tables and files
      Use TabFile
            Go
            Select Distinct
                        O.name                         As TableName
                        ,I.name                         As IndexName
                        ,I.type_desc               As IndexType
                        ,I.index_id                  As IndexID
                        ,FG.name                     As FileGroupName
                        ,F.name                        As FileLogicalName
                        ,F.physical_name    As FilePhysicalName
            From
                        sys.indexes                As I
                        Inner Join
                        sys.filegroups            As FG
                                    On I.data_space_id = FG.data_space_id
                        Inner Join
                        sys.master_files       As F
                                    On FG.data_space_id = F.data_space_id
                                    And F.database_id = DB_ID()
                        Inner Join
                        sys.all_objects          As O
                                    On I.[object_id] = O.[object_id]
            Where
                        I.data_space_id = FG.data_space_id
                        And O.[type] = ‘U’
            Order By
                        TableName
                        ,IndexID
                        ,FileLogicalName
            Go

I recommend you play with this script on the sample DB above but also on of your own DBs.  Not only will it familiarize you with the various catalog views it will also point out how your data is arranged and may help you in managing it even better.