{"id":897,"date":"2010-05-28T00:00:00","date_gmt":"2010-05-28T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/im-a-sql-server-dba-and-im-in-love-with-powershell\/"},"modified":"2021-08-24T13:40:28","modified_gmt":"2021-08-24T13:40:28","slug":"im-a-sql-server-dba-and-im-in-love-with-powershell","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/im-a-sql-server-dba-and-im-in-love-with-powershell\/","title":{"rendered":"I’m a SQL Server DBA, and I’m in Love with PowerShell"},"content":{"rendered":"
\n

I’m the kind of guy who can’t resist praising PowerShell, whether it’s applied to SQL Server or\u00a0 some other administrative task. Unfortunately, PowerShell presents the user with an initial hurdle to jump: the initial learning effort that’s required. Thankfully, there is a very easy way to overcome that hurdle, as pointed out to me by Shay Levy ( Twitter<\/a>| blog<\/a>) when I asked him for advice on the best way to learn PowerShell:<\/p>\n

“Just use it”<\/i><\/p>\n

And although it sounds facetious, it’s the honest truth; you will only begin to understand PowerShell if you start using it. More than that, you have to spend hours reading, testing the code, looking to improve it and not be ashamed to ask for pointers. This might sound like a real trial, but it’s actually deeply satisfying, and I’ve found the community which has sprung up to be warm and welcoming.<\/p>\n

My little knowledge of PowerShell is applied purely to SQL Server, and for this purpose it is not difficult to learn. However, that doesn’t change the fact that I’m always looking for ways to improve the code I’ve written, and I’ve seen some examples that truly make my brain hurt! Yet with a little patience, a good IDE and, of course, good contacts with the community, no challenge is too great (or as we say in Brazil, “the lion is dead”).<\/p>\n

To show just how easy PowerShell can be if you have a bit of determination, I got some of my unpublished blog posts together and decided to use them as demonstrations in this article. We’ll see some solutions using just PowerShell, PowerShell with SQL Server and also SQLPSX. I hope you find it useful!<\/p>\n

SSIS and the T-SQL Hammer<\/h1>\n

I firmly believe that you have to read Chad Miller’s Blog Post entitled “The T-SQL Hammer<\/a>“. It is a fantastic<\/i> example of productivity in PowerShell, and I took the liberty of using the same title here because, after reading his words…<\/p>\n

“The over-reliance on a familiar tool is best described with the quote, ‘if all you have is hammer, everything looks like nail’, and for database professionals this means using or sometimes misusing T-SQL. Whenever database administrators are presented with a scripting problem they instinctively reach for good-old-familiar T-SQL.\u00a0 And why not? In many cases T-SQL provides an ideal solution to SQL Server administration scripting problems, however there are certain scenarios where another tool, PowerShell, provides a\u00a0 more elegant solution.”<\/p>\n

…I believe we can demonstrate another great argument in favor of using PowerShell. As you may have guessed, PowerShell might be my own ‘hammer’, but nevertheless, I feel that it offers remarkably elegant solutions to many of the challenges I face, and can do the same for you.<\/p>\n

I’d like to draw your attention to a great article by Rodney Landrum, showing a very interesting solution for consolidating SQL Server Error Logs<\/a>. He shows how to use SSIS and some T-SQL to neatly handle this operation, and I decided to tinker with his solution. Before I carry on, you should either go and read it, or just accept what I’m saying on faith! Rodney’s solution is excellent, make no mistake; the only thing I feel I’m adding to it is ease-of-use and perhaps a little more elegance with respect to my own environment (prompted, perhaps, by Chad’s blog post). You’ll have to be the judge when it comes to which works better for you.<\/p>\n

To start with, one of the things I’ve changed in my version is that I don’t use the MERGE statement, for a reason that become clear in a moment. I have one Server and Database repository, one table repository to save the logs to, and I created another control table called ServersInfo<\/b>\u00a0 with only two columns, Server Name (ServerName<\/b>) and\u00a0 last collection Date (DateLastLogErrorImported<\/b>)<\/p>\n

In his example, Rodney uses the Merge statement and SSIS to record the data that is brought across in the log for each server. In my case, rather than using an SSIS package to control which logs I store, I record the date of the most recent logs in this ServersInfo<\/b> table, and only insert data collected after<\/i> this date. This last table is handy; it means that, when I access the error log, I can always filter my information by date.<\/p>\n

But let’s see the code:<\/p>\n

First, we need some T-SQL to create the repository table and Server\/date control table. Obviously, you will create these tables on your server and database repository.<\/p>\n

Create Table SQLLogInfo (\u00a0\u00a0\u00a0\u00a0 ServerName varchar(50),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LogDate datetime,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ProcessInfo varchar(100),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Text varchar(max)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\r\nCreate Table ServersInfo (\u00a0\u00a0\u00a0 ServerName varchar(100), \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DateLastLogErrorImported datetime\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 )\r\n<\/pre>\n
Now let’s populate the Servers table (Serversinfo<\/b>) with the names of all the servers from which you want to have the error logs collected.<\/p>\n
insert into ServersInfo(ServerName) values ('Colombo-pc\\SQLExpress')\r\ninsert into ServersInfo(ServerName) values ('Colombo-pc')\r\n<\/pre>\n
After that’s sorted, let’s look at the PowerShell code (remember, I am using SQLPSX<\/a>)<\/p>\n
#Define Server and Database Repository\r\n$ServerRepository = $env:COMPUTERNAME\r\n$DatabaseRepository = \"tempdb\"\r\n\u00a0\r\n#Return the servers and the last collection date from error logs\r\nget-SqlData -sqlserver $ServerRepository -dbname $DatabaseRepository -qry \"Select ServerName,DateLastLogErrorImported from ServersInfo\" | foreach {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $ServerName = $_.Servername\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $DateLastLogErrorImported = $_.DateLastLogErrorImported\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 get-sqlserver $ServerName | foreach {\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 #If this is the first collection, takes the date 2010\/01\/01\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if ($DateLastLogErrorImported.value -eq $null -or $DateLastLogErrorImported.value -eq \"\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 { $DateLastLogErrorImported = '2010\/01\/01' }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n#Retrieve the error log from the current server in foreach. Apply a \u00a0\u00a0#filter to only LogDate above and equal to Last Collection date\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 #and insert into Repository\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $Error.Clear()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Get-SqlErrorLog -sqlserver $ServerName -lognumber 0 | where-object { $_.LogDate -ge $DateLastLogErrorImported} | foreach {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $Text = $($_.text) -replace \"'\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Set-SqlData -sqlserver $ServerRepository -dbname $DatabaseRepository -qry \"Insert into SQLLogInfo (Servername,LogDate,ProcessInfo,text) values ('$($ServerName)','$($_.Logdate)','$($_.ProcessInfo)','$Text)')\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 #Update Information Table with the server and the last collection date.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Set-SqlData -sqlserver $ServerRepository -dbname $DatabaseRepository -qry \"Update ServersInfo set DateLastLogErrorImported = getdate() where Servername = '$($Servername)'\" \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n}\r\n<\/pre>\n
I’m not handling errors at this stage, but that would be very simple to implement. With just a Try-Catch before the SQLPSX<\/a> functions, we cover all error possibilities, logging them into a file for later inspection. To make your life even easier, you can find this code in my Simple-Talk articles<\/a>.<\/p>\n
With that , the data is neatly collected and saved into a SQL Server table. We can schedule this script to run once a day and, as I said earlier, we only insert data from the date of the last collection, so avoiding having to work with huge volumes of data. Naturally, with this collection system in place, you can apply summations, aggregates, filters and whatever else is necessary to set up your monitoring. To quote Chad, PowerShell provides a more elegant solution.<\/p>\n
Searching and Logging with PowerShell<\/h1>\n
Not too long ago, I had the pleasure of helping a friend (and great Microsoft community \u00a0influencer) Jorge Segarra (Twitter<\/a>| blog<\/a>), in another PowerShell challenge. Jorge \u00a0needed the following:<\/p>\n
A folder with several txt files (i.e. flat files) should be filtered for files that had the most recent change date (LastWriteTime<\/b>) in a determined period, searched for whether they contain a particular string, and the result this search should be stored in a newly-created file. The results in this file should show the path and file name, as well as the line number corresponding to the string search result, and text of this line.<\/p>\n
It sounds fairly complicated, but it really is quite simple in PowerShell. I’ve created two txt files to illustrate:<\/p>\n
File1.txt contains this content:<\/p>\n
111111111111111111111111111\r\n222222222222222222222222222\r\n33333Error33333333333333333\r\n444444444444444444444444444\r\nError5555555555555555555555\r\n666666666666666666666666666\r\n777777777777777777777777777\r\n8888888888888888888888Error \r\n<\/pre>\n
And File2.txt contains:<\/p>\n
111111111111111111111111111\r\n222222222222222222222222222\r\n33333Error33333333333333333\r\n444444444444444444444444444\r\nError5555555555555555555555\r\n666666666666666666666666666\r\n777777777777777777777777777\r\n8888888888888888888888Error\r\n999999999999999999999999999\r\n100000000000000000000000000\r\nAAAAAAAAAAAAAAAAAAAAErrorAA\r\nBBBBBBBBBBBBErrorBBBBBBBBBB\r\nCCCCCCCCErrorCCCCCCCCCCCCCC \r\n<\/pre>\n
You can see that in red & bold is the word “Error<\/b>“, which is the string that I want to look for.<\/p>\n
Let’s go to the code:<\/p>\n
Function Invoke-AdmErrors\r\n\u00a0\r\n{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <#\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .SYNOPSIS\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Search for errors in files\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .DESCRIPTION\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Search for errors in files\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .INPUTS\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Pipe Path to files\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .OUTPUTS\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Flat file with files found\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 #>\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 param (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Parameter(Position=0, Mandatory = $true, ValueFromPipeline = $true)] [String] $Path,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Parameter(position=1,Mandatory = $true )]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [system.DateTime] \u00a0\u00a0 $InitialDate ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Parameter(position=2,Mandatory = $true )]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [system.DateTime] \u00a0\u00a0 $FinalDate ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [Parameter(position=3,Mandatory = $false )]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [string] \u00a0\u00a0\u00a0\u00a0 $ResultFile = \"c:\\users\\jsegarra\\desktop\\example.txt\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Process\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Get-ChildItem $path | Where-Object {$_.lastwritetime -ge $InitialDate -and $_.lastwritetime -le $FinalDate } |\u00a0 foreach {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Select-String -Pattern \"Error\" -Path $_ -AllMatches | Add-Content -Path $ResultFile -Force\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n}\r\n<\/pre>\n
If you look closely at the PowerShell above, you’ll see that the process of finding and filtering results, and the saving of the final file is all done in one line. The rest are input parameters, help and function configuration.<\/p>\n
Let’s say I want to search for the string “Error” in all files under the path C:\\temp<\/i>, with a filter focusing on files changed between the dates 01\/01\/2010 and 01\/05\/2010 (dd\/mm\/yyyy), and the save the results into the file C:\\temp\\result.txt<\/i>.<\/p>\n
Invoke-AdmErrors -Path c:\\temp -Initialdate '01\/01\/2010' -FinalDate '01\/05\/2010' -SearchFor \"Error\" -ResultFile c:\\temp\\result.txt \r\n<\/pre>\n
…And the file result will look like this, showing the file name (with path), line number of the result, and string searched:<\/p>\n
\"1043-LJ1.JPG\"<\/p>\n
Figure 1. Search and Filter results<\/p>\n
A Simpler Way to Get Values from SQL Server<\/h1>\n
This is a very quick demonstration of something which surprises me. In many code snippets and articles on the web, I see this method (or something similar) of returning values from a query (either a simple query, stored procedure etc. ..) from SQL Server to PowerShell:<\/p>\n
$cn = new-object System.Data.SqlClient.SqlConnection(\"Data Source=myserver;Integrated Security=SSPI;Initial Catalog=Master\")\r\n$cn.open()\u00a0 \r\n$sql = \"Select name,number from master..spt_values\"\r\n$cmd = new-object \"System.Data.SqlClient.SqlCommand\" ($sql, $cn)\r\n$rt = $cmd.ExecuteReader()\r\nwhile ($dr.Read()) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 $rt.GetValue(0)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 $rt.GetValue(1)\r\n}\r\n<\/pre>\n
It’s correct, for sure, but why not simplify the code? We could rewrite it to just:<\/p>\n
Invoke-Sqlcmd -ServerInstance MyServer -Database Master -Query \"Select name,number from master..spt_values\" | Select-Object name,number\r\n<\/pre>\n
We can even put a condition on the return; let’s say we only want to display rows where the value of the column number is 2…<\/p>\n
Invoke-Sqlcmd -ServerInstance MyServer -Database Master -Query \"Select name,number from master..spt_values\" | where-object { $_.number -eq 2} | Select-Object name,number \r\n<\/pre>\n
…Or even run a stored procedure.<\/p>\n
Invoke-Sqlcmd -ServerInstance MyServer -Database MyDatabase -Query \"exec usp_myproc\" | Select-Object column1, column2\r\n<\/pre>\n
PowerShell provides many ways to do this, and you can choose the best method for yourself.<\/p>\n
Scripting SQL Server Objects with SQLPSX<\/h1>\n
I’ve recently started using the SQLPSX library, and I am constantly finding gems buried inside it. One of my personal favorites, and one which I use a lot, is a very easy method for generating scripts of objects . For example, if we want to generate a script from a table, use:<\/p>\n
Get-SqlDatabase -sqlserver YourServer -dbname YourDatabase | Get-SqlTable -name YourTable |\u00a0 Get-SqlScripter  \r\n<\/pre>\n
And to output to a text file:<\/p>\n
Get-SqlDatabase -sqlserver YourServer -dbname YourDatabase | Get-SqlTable -name YourTable |\u00a0 Get-SqlScripter |Out-File c:\\temp\\Script.txt\r\n<\/pre>\n
What if I want all the tables in my database, and not just a specific one? Simple; just delete the -name<\/b> parameter in Get-SQLtable<\/b>:<\/p>\n
Get-SqlDatabase -sqlserver YourServer -dbname YourDatabase | Get-SqlTable\u00a0 |\u00a0 Get-SqlScripter |Out-File c:\\temp\\Script.txt\r\n<\/pre>\n
The same (i.e. how to generalize your scripts) can be applied to all other objects, such as stored procedures:<\/p>\n
Get-SqlDatabase -sqlserver YourServer -dbname YourDatabase | Get-SqlStoredProcedure\u00a0 | Get-SqlScripter\r\n<\/pre>\n
To see a complete list of the functions of SQLPSX, and read more about how to use them, go to SQL Server PowerShell Extensions Help<\/a>.<\/p>\n
How PowerShell Custom Tables Saved the day<\/h1>\n
This article’s final showcase of PowerShell Proclivity came about because a friend contacted me on MSN, and said:<\/p>\n
“Laerte, I saw the hint you gave via twitter to Jorge Segarra<\/a>, and want to see if you can help me. I need to output a result of a proc to an XML document every day (as is described in the Jorge \u00b4s blog post<\/a>), but the result comes from a third-party procedure, and I need to insert a description of a flag. The problem is that the Stored Procedure only returns the code, and I cannot change the procedure (because it’s third-party). Can you help me?”<\/p>\n
Well, enumerating the problems before us:<\/p>\n
    \n
  1. We need to add one more column to the Stored procedure output,<\/li>\n
  2. This column is a description of another column: a flag,<\/li>\n
  3. We cannot change the Stored Procedure to add this column,<\/li>\n<\/ol>\n
    If I told you that we could solve this whole problem with only one line of PowerShell, you believe me? No? Well… Let’s see.<\/p>\n
    First, let’s create the scenario. I created a table and a stored procedure with the code below, and also included some names in the table. This table has a column called InsanityState and the content is a code and describe as:<\/p>\n
    Column InsanityState Char(1) \u00a0– can be\u00a0 I – Insane, O – Out of Control, W – Without Hope, D – He has been a DBA<\/p>\n
    The T-SQL Code:<\/p>\n
    --Create The Table\r\nCreate table\u00a0 Arkham_Asylum\u00a0 (\u00a0\u00a0\u00a0\u00a0\u00a0 Id int ,-- Patient ID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 name varchar(100), -- Patient Name,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 InsanityState char(1) -- \r\nState of madness - can be\u00a0 I - Insane, O - Out of Control, W - Without Hope, D - He has been a DBA\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\ngo\r\n-- Insert some Insanity\r\ninsert into Arkham_Asylum values (1,'Poison Ivy','I')\r\ninsert into Arkham_Asylum values (2,'Scarecrow','O')\r\ninsert into Arkham_Asylum values (3,'Two Face','W')\r\ninsert into Arkham_Asylum values (4,'Joker','I')\r\ninsert into Arkham_Asylum values (3,'Laerte Junior','D')\r\n\u00a0\r\n--Create Stored Procedure\r\ngo\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\r\nalter procedure usp_getInfoInsanes\r\n\u00a0\r\nas\r\n\u00a0\r\nselect\u00a0\u00a0\u00a0\u00a0\u00a0 Id,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 name,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 InsanityState\r\nfrom\u00a0 Arkham_Asylum\r\n\u00a0\r\ngo\r\n<\/pre>\n
    If we execute the proc, you will see the Flag that it needs to display as a description:<\/p>\n
    \"1043-LJ2.JPG\"<\/p>\n
    Figure 2. The results of the Stored Procedure.<\/p>\n
    As I said, we cannot change the Stored Procedure to return the description of InsanityState, so we need to use PowerShell Custom Tables. First, let’s see just the code to return all these crazy people with their Insanity State descriptions, but not the code to output to XML.<\/p>\n
    To repair this situation, I use a custom table, calling a function to convert the ID of the InsanityState flag, and display the description.<\/p>\n
    function get-InfoInsanes\r\n{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Begin\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 function ConvertTo-FlagCaption($Value)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 switch($value)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"I\"\u00a0\u00a0 {\"Insane\";break}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"O\"\u00a0\u00a0 {\"Out of Control\";break}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"W\"\u00a0\u00a0 {\"Without Hope\";break}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"D\"\u00a0\u00a0 {\"He has been a DBA\";break}\r\n\u00a0\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 default {\"Unknown\";BREAK}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Process\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 invoke-sqlcmd -server $env:computername -database teste -Query \"exec usp_getInfoInsanes\" |\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Select-Object \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Id,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Name,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @{Expression={ConvertTo-FlagCaption -value $_.insanityState};Label = \"Insanity State\"}\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n}\r\n<\/pre>\n
    …And look at the output:<\/p>\n
    \"1043-LJ3.JPG\"<\/p>\n
    Figure 3. InsanityStates, clarified.<\/p>\n
    Now, for the output to an XML file, just use the ConvertTo-XML<\/b> cmdlet and the save property:<\/p>\n
    (invoke-sqlcmd -server $env:computername -database teste -Query \"exec sp_getInfoInsanes\" |\u00a0 Select-Object \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Id,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Name,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @{Expression={ConvertTo-FlagCaption -value $_.insanityState};Label = \"Insanity State\"} | ConvertTo-Xml -NoTypeInformation).save(\"c:\\temp\\ResultXml.Xml\")\r\n<\/pre>\n
    …And the XML will look like:<\/p>\n
    \"1043-LJ4.JPG\"<\/p>\n
    Figure 4 – The Insane XML Output<\/p>\n
    Now you just need to schedule this to run, and be happy!<\/p>\n
    Summary<\/h1>\n
    Well my friends, I have described a few of the uses that I’ve put PowerShell to whilst working as a DBA. I realize I’ve presented a mixed bag here, but that reflects the wide spectrum of problems that a working DBA comes across that PowerShell can help to solve. I chose them because they can be used in a lot of different ways by people who are taking the first steps with the language. I hope that you, like me, look at the productivity gain that can be found in working with this fantastic\u00a0 tool.<\/p>\n
    A big hug to everyone, and here are some links for further reading:<\/p>\n