Search results matching tag 'PowerShell'

New Article on the SQL Server 2012 Backup and Restore PowerShell Cmdlets

AllenMWhite — Mon, 13 May 2013 13:08:00 GMT

While I was on vacation last week in Scotland Simple Talk published a new article I wrote called Backup and Restore SQL Server with the SQL Server 2012 PowerShell cmdlets.

Hope you have as much fun with it as I did writing it.

Allen

Speaking in Chicago Saturday

merrillaldrich — Thu, 11 Apr 2013 16:03:36 GMT

I’m excited to be offering a beginning PowerShell session at SQL Saturday #211 in Chicago on Saturday, April 13. This time we’re making it a family weekend, bringing our two boys. I haven’t been to Chicago for many years, and it’s, of course, an Architecture dream world, so it should be fun to look at some buildings again!

Emil Bach House, photo © Jeremy Atherton, 2006

The session is intended to help you get started with some PowerShell syntax basics. I try to demystify the Pipeline a bit, help make sense out of all the { curlies } and [ brackets ] and ( parentheses ), and translate some of what you already know about T-SQL into PowerShell. If PowerShell seems cryptic or mysterious, come on down and maybe we can sort it out together.

Should be a great time.

T-SQL Tuesday #39: Managing your SQL Server Services with PowerShell

AllenMWhite — Tue, 12 Feb 2013 18:09:00 GMT

This T-SQL Tuesday is about using PowerShell to do something with SQL Server. Now, if you've read any of my blog posts you probably know I've been using PowerShell to do things with SQL Server for a while now, but I'm glad Wayne decided on this topic for his T-SQL Tuesday topic, because everyone has different ways to use PowerShell, and you can learn from all of them, as I do.

(When I started to write this post I'd intended to share how I convert a PerfMon binary log file into SQL Server data for baseline analysis, but found I'd already done that here. Then, I thought I'd share how I save SQL Agent jobs and move them to another server, but did that one, too!)

One of the interesting aspects of SMO (Server Management Objects) is the Managed Computer object. It doesn't get a lot of attention because, well, that goes to the SQL Server instance and the various database objects. Administrators, though, need to pay attention to managing the instance itself. SQL Server 2008 introduced the Configuration Manager, a GUI application that allows administrators to view the SQL Server services installed, including their current state, the service account they use, etc. It also allows them to manage the external access to the instances via the network protocols supported, the TCP/IP ports, etc.

Here's a diagram of the Managed Computer object:

Now, if you're familiar with the Configuration Manager you should see some parallels there, and that makes sense, because these objects are the ones Configuration Manager is working with.

Let's say it's time for you to change the service account and password for your SQL Server instance. Using this model, we have our guide. We need to create a new ManagedComputer object, connect to the server, connect to the service, use the SetServiceAccount() method to set the new values, then restart the service. In this example I'll also restart the Agent service since I'm resetting the SQL Server instance service account.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer localhost
$sqlinst = $mc.Services['MSSQLSERVER']
$sqlagnt = $mc.Services['SQLSERVERAGENT']
$sqlinst.SetServiceAccount('TESTDOMAIN\AlternateAcct','L44HhRMeF25UDvQeJTj5UqyE')
$sqlinst.Alter()
$sqlinst.Stop()
start-sleep -s 10
$sqlinst.Start()
$sqlagnt.Start()

Let's say I just want to see the services on my local instance, like I do in Configuration Manager. Again, that's pretty easy.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer localhost
$mc.Services | select Name, ServiceState, DisplayName, ServiceAccount | format-table

There are times you'll need to work with the network protocols, and I've actually used the following code to change the IP port for an instance after an install, because it HAD to match a particular value.

$mc = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') localhost
$i=$mc.ServerInstances['MSSQLSERVER']
$p=$i.ServerProtocols['Tcp']
$ip=$p.IPAddresses['IPAll']
$ip.IPAddressProperties['TcpDynamicPorts'].Value = '1099'
$p.Alter()

Now, why is this important, if I can do all this in Configuration Manager? Well, SQL Server 2012 supports installation on Windows Server Core, and there's no GUI on a Core server, so this becomes the best way to access the objects you normally manage with Configuration Manager. More importantly, it allows you to build a set of scripts to "just handle" any number of problems, without trying to remember what submenu gives you access to what property to change to solve your problem.

Good luck, and thanks, Wayne, for hosting this month's T-SQL Tuesday!

Allen

Behind the scenes of PowerShell and SQL

rob_farley — Tue, 12 Feb 2013 00:11:46 GMT

Every year, PowerShell increases its stranglehold on the Windows Server system and the applications that run upon it – with good reason too. Its consistent mechanisms for interaction between its scripting interface and the underlying systems make it easy for people to feel comfortable, and there is a discoverability that has been lacking in many other scripting environments.

Of course, SQL Server hasn’t been overlooked at all, and it’s coming up to five years since the SnapIns were made available (even longer since people started to dabble with SQL using PowerShell).

But what’s going on behind the scenes? Does PowerShell present a threat to those amongst us who will always prefer T-SQL? Does PowerShell give us new options that are not available any other way? Well, let’s have a bit of a look, especially since this month’s T-SQL Tuesday (hosted by Wayne Sheffield who tweets as @DBAWayne) is on the topic of PowerShell.

So we know PowerShell is useful. However we spin it up, we can quickly jump into writing commands, whether it be interacting with WMI, hooking into some .Net assembly we’ve loaded up, or simply browsing the file system. I’ve developed a tendency to use it to start whichever SQL instances I’m needing for the day – by default I have all of them turned off, since I don’t know which one I’ll be wanting most.

If we’re going to be interacting with SQL, then it’s easiest to either load up the SQLPS environment directly (there’s a PowerShell shortcut within Management Studio), or else (as I do), start a PowerShell window with the Snapin loaded. I prefer this later option, as the SQLPS environment is a slightly cut-back version of PowerShell. But either way – the stuff I’ll continue on with is essentially the same whichever environment you use.

If you’ve talked about SQL with me long enough, you’ll have come across the fact that I often use SQL Profiler when I’m curious about where to find information. My former colleague Jim McLeod (@Jim_McLeod) blogged about this a few months ago, with an example that I remember looking through with him four or five years ago. It’s a great technique that works on all kinds of things, even across different versions of SQL Server. It also adds as a terrific reminder that Management Studio is not a special application, it simply knows how to ask for the pieces of information that it shows.

But PowerShell (or SMO, for that matter), that’s in the .Net world. Surely that would be able to bypass the clunky T-SQL stuff that Management Studio does... I mean, Management Studio has to be flexible enough to work across remote servers, talking through firewalls that only allow T-SQL interfaces. Surely PowerShell has access to a deeper magic.

Well, no. PowerShell still lets you talk to remote servers, and ends up using the same methods.

Let’s prove it.

Spin up Profiler, and start a trace against your favourite instance. I like to watch for SQL:BatchCompleted, SP:StmtCompleted and RPC:Completed events when doing this kind of thing. I’m using an instance that isn’t doing anything else, but you could apply a ColumnFilter to filter the events to things with an ApplicationName starting with SQLPS if you prefer.

With that running, I jump into PowerShell and do something like:

PS SQLSERVER:\sql\localhost\sql2008r2> dir Databases | ft name

This lists the names of the databases on my SQL2008R2 instances. You don’t need to see the results, you can imagine them for yourself.

If PowerShell were using some secret interface, it’s unlikely we’d see something in Profiler. But it’s not, and we see a bunch of stuff.

We see a bunch of Remote Procedure Calls, each with a Stored Procedure Statement Completed event showing the same information. And look – we see queries against master.sys.databases, asking for the name of each of the databases, passing in the name as a parameter. Brilliant! Notice just a bit earlier though, there’s a SQL:BatchCompleted call. This means that a query has been passed in directly. It’s this:

SELECT
CAST(
serverproperty(N'Servername')
AS sysname) AS [Server_Name],
dtb.name AS [Name]
FROM
master.sys.databases AS dtb
WHERE
(CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit)=0)
ORDER BY
[Name] ASC

So it grabs the list of database names first, and then makes extra calls to be able to fetch the list of names again, one by one.

The reason why it’s grabbing the list of names one by one isn’t because it’s stupid and is asking to be ridiculed. It’s because we've asked to see that property, and I guess the PowerShell people figured that no matter what property you ask for, it’ll go and fetch it to show you.

When I asked for the CompatibilityLevel property instead, I got some different rows thrown in. Interestingly though, it still asked for the name each time.

Also interestingly, when I asked for the CompatibilityLevel a subsequent time, the calls for “SELECT dtb.compatibility_level…” weren’t in there. They’d been cached by the PowerShell environment – important to note if you ever come across PowerShell giving you old values.

So what about asking something more interesting? Let’s try asking about the IndexSpaceUsage in AdventureWorks.

PS SQLSERVER:\sql\localhost\sql2008r2> gi Databases\AdventureWorks | ft IndexSpaceUsage

The result tells me it’s 62576. Yeah, but today I’m not interested in that, just what happened in the trace.

Four entries. An SP:StmtCompleted with matching RPC:Completed, and two SQL:BatchCompleted.

The SP:StmtCompleted and RPC:Completed were this statement, passing in the parameter value ‘AdventureWorks’. Clearly incredibly informative.

SELECT
CAST(0 AS float) AS [IndexSpaceUsage],
dtb.name AS [DatabaseName]
FROM
master.sys.databases AS dtb
WHERE
(dtb.name=@_msparam_0)

This is getting the value zero. Wow. Brilliant stuff.

The last entry – the second of the two SQL:BatchCompleted events is:

select convert(float,low/1024.) from master.dbo.spt_values where number = 1 and type = 'E'

If you run this in Management Studio, you’ll discover it gives the value 8. Ok.

The other entry is more interesting.

use [AdventureWorks]
SELECT
SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) AS [DataSpaceUsage],
SUM(a.used_pages) AS [IndexSpaceTotal]
FROM
sys.allocation_units AS a INNER JOIN sys.partitions AS p ON (a.type = 2 AND p.partition_id = a.container_id) OR (a.type IN (1,3) AND p.hobt_id = a.container_id)

This is more like it! We run this in Management Studio, and we see two values. DataSpaceUsage is 13682, IndexSpaceTotal is 21504. Neither are our value 62576. But we do have clues in the column names, and in that value 8 that came back too. We can easily deduce that it’s actually (IndexSpaceTotal-DataSpaceUsage)*8, and we have ourselves a nice little method for working out the IndexSpaceUsage ourselves now if we need it.

Or we can just ask PowerShell next time as well.

Incidentally – if you’re considering doing the MCM Lab exam some time, then you might find that a familiarity with PowerShell comes in really handy. I’m not saying there are PowerShell questions on the exam at all – I’m just suggesting that you may find that PowerShell becomes a really useful way of getting at some of the information that you’re looking for. If you’re stumbling around the list of DMVs trying to remember which one it is that stores some particular thing, remember that you might be able to get the data out more easily if you use PowerShell instead.

So can we discover secret things about SQL from PowerShell? Are there things we can do in PowerShell that are impossible through other mechanisms? Hooks that let us break the rules even?

Recently, Kendal van Dyke asked a question about this kind of thing on Twitter. He was wondering if you could have a default constraint on a column in a view. The reason for his wondering was that he saw a property on a view column in PowerShell that made him wonder. The answer is no though, and there’s a simple reason.

PowerShell is a programmatic interface. It involves classes and property and methods. It does things row by row, which is why much of what you see in that trace feels amazingly pedantic – asking about things which shouldn’t have to be that complicated. The implication of this though, is that PowerShell reuses the concept of a column, regardless of whether this is a column in a table, a view, or anywhere else it decides to need a column. The fact that columns in tables have some extra properties isn’t enough to make this class re-use pointless. If we try to set a Default constraint for a column in a view though, we get an error, just like if we tried to do it any other way.

The PowerShell I used was:

$db = Get-Item SQLSERVER:\sql\localhost\sql2008r2\Databases\AdventureWorks
$def = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Default –ArgumentList $db, "ViewDefault"
$def.TextHeader = "CREATE DEFAULT ViewDefault AS"
$def.TextBody = "'ABC'"
$def.Create()
$def.BindToColumn("vStateProvinceCountryRegion","StateProvinceCode","Person")

The code that ended up getting called was to the stored procedure sp_bindefault (despite it being deprecated). Naturally, trying to execute this against a view column gives an error regardless of what wrappers you have put around it – PowerShell or not.

So PowerShell is very useful, and it provides a handy way of getting to a lot of things that could be otherwise hard. But looking below the surface, it isn’t able to circumvent the basic restrictions of SQL Server, because it still ends up doing its work using T-SQL.

@rob_farley

Updated Warehouse Re-Index Script

merrillaldrich — Tue, 15 Jan 2013 00:30:00 GMT

As I talked about in my last post, I just went through a re-indexing project that took the partitioned fact rows from our warehouse and relocated them into new files. There are a lot of tables and indexes involved, so I have a PowerShell “helper” script to generate the actual T-SQL that moves the data. The idea is to find all the indexes that use a particular partition scheme in the database, and make the CREATE INDEX statements that would recreate them on the new partition scheme. This script doesn’t do the re-indexing work, it just outputs the T-SQL that would do the work, so that the SQL can be verified and/or edited and run later, in the context of other modifications to the warehouse.

(This is a much improved version of the work described in this even older post. It frankly wasn’t all that great back then.)

The script was developed on PowerShell 2 and SQL Server 2012 client tools against SQL Server 2005, but I believe the work that the script does (the process of generating the T-SQL and the results) is compatible with any SQL Server EE 2005 or later. It assumes you’ve loaded the SQL provider or SQLPS module into PowerShell.

 <#
 PowerShell Script to generate CREATE INDEX statements from existing indexes
 mapped to a new partition scheme.

 14 Jan 2013 Merrill Aldrich
 Free for your use, but without warranty. Use at your sole risk.
 #>

 $ErrorActionPreference = 'Stop'

 # Create a connection to the SQL Server instance
 Set-Location SQLSERVER:\SQL\<yourserver>\DEFAULT\

 # Names of existing and new partition schemes. New will replace existing in script output:
 $oldPartitionScheme = 'nameOfOldScheme'
 $newPartitionScheme = 'nameOfNewScheme'

 # Compose a query that will list out the tables that use a specific partition scheme
 $partitionedTables = @( Get-ChildItem .\Databases\<yourdatabase>\tables |
     Where-Object -filter { $_.PartitionScheme -eq $oldPartitionScheme } )

 # For each partitioned table, script out create statements for partitioned indexes
 $scripter = New-Object ('Microsoft.SqlServer.Management.Smo.Scripter') ( get-item ( get-location ) )

 # Set scripting options here as needed
 # $scripter.Options.<someoption> = <somevalue>
 $scripter.Options.ScriptDrops = $False

 $partitionedTables | foreach {
     # Note current table in output:
     "/***  Table $($_.Schema).$($_.Name)   ***/"

     $partitionedIndexes = ( $_.indexes |
         Where-Object -Filter { $_.IsPartitioned -eq $True -and $_.PartitionScheme -eq $oldPartitionScheme }
      )

     # Script create statements

     $partitionedIndexes | foreach {
         $indexCreateScr = $scripter.Script( $_ )

         # Change the partition scheme for every statement to the new partition scheme
         $indexCreateScr = $IndexCreateScr -replace $oldPartitionScheme, $newPartitionScheme

         # Change ALTER TABLE ADD CONSTRAINT to CREATE INDEX statements for PKs
         $indexCreateScr = $indexCreateScr -replace `
             'ALTER TABLE (\[[^\]]*\])\.(\[[^\]]*\]) ADD  CONSTRAINT (\[[^\]]*\]) PRIMARY KEY CLUSTERED', `
             ('/* $& */' + "`n" + 'CREATE UNIQUE CLUSTERED INDEX $3 ON $1.$2')

         # For ALTER TABLEs the DROP_EXISTING index option has to be added to the script
         # Find any "WITH (" clause not containing the DROP_EXISTING option, and add it
         $indexCreateScr = $IndexCreateScr -replace 'WITH \((?!.*DROP_EXISTING)', 'WITH ( DROP_EXISTING = ON, '

         # Change index create options, if necessary. Edit to suit:
         $indexCreateScr = $IndexCreateScr -replace 'PAD_INDEX = OFF, ', ''
         $indexCreateScr = $IndexCreateScr -replace 'STATISTICS_NORECOMPUTE = OFF, ', ''
         $indexCreateScr = $IndexCreateScr -replace 'IGNORE_DUP_KEY = OFF, ', ''
         $indexCreateScr = $IndexCreateScr -replace 'SORT_IN_TEMPDB = OFF, ', ''

         $indexCreateScr = $IndexCreateScr -replace 'ONLINE = OFF', 'ONLINE = ON'
         $indexCreateScr = $IndexCreateScr -replace 'DROP_EXISTING = OFF', 'DROP_EXISTING = ON'

         # Insert some line terminators for legibility
         $indexCreateScr = $IndexCreateScr -replace "ON \[$($newPartitionScheme)\]", "`nON [$($newPartitionScheme)]"
         $indexCreateScr = $IndexCreateScr -replace ', ', ", `n`t"

         # Output the revised script
         $indexCreateScr
         "GO`n"
     }
 }

Most of this is pretty basic – put the tables into a collection, loop over them, and for each table go through each partitioned index, scripting them out. The only parts that were tricky to develop involve the indexes that support primary keys.

The scripter from SMO wants to script out a PK as “ALTER TABLE … ADD CONSTRAINT,” and the problem with that is you can’t use it to recreate the existing index using the DROP_EXISTING option. But, in fact, in SQL Server it is perfectly valid to do a CREATE INDEX … WITH ( DROP_EXISTING = ON ) against the index that supports a primary key. It’s just that the scripter isn’t designed to deal with that idea (as far as I know).

I searched around for some solution to this issue to no avail, but instead fell back on good old hacky find and replace. There are a few semi-hairy regex expressions in the script that locate instances of ALTER TABLE … ADD CONSTRAINT and substitute the equivalent CREATE INDEX statement, and also locate the WITH clauses that don’t have the DROP_EXISTING option, and add that. The gibberish parts of those expressions are mostly looking for object names and escaping square brackets.

If it’s not clear what this sort of thing means (\[[^\]]*\])\.(\[[^\]]*\]) drop me a comment and I’ll try to clarify. Conversely, if you are better at regex than I am, which is likely, any advice you might have to simplify this would be welcome!

A side note about regex and PowerShell:

If you are used to using regex in the find and replace dialog in SSMS / Visual Studio, it’s not the same flavor of regex that PowerShell uses, and there are some important differences:

Tagging expressions/ Grouping is accomplished with parens (), not curlies {}

Substitution is done with dollar signs $1 and not backslashes \1

AND, epically confusing, the dollar signs in regex are not the dollar signs in PowerShell. So
"$1" and '$1'
in a –replace expression are different in important and confusing ways. One will look for a PowerShell variable $1 and try to expand it, while the other is the regex matching group #1 and does regex replacement.

What makes this sad is that the regex one might use in editing T-SQL in SSMS can’t be moved over to PowerShell without a lot of changes.

Speaking - SQL Saturday 173, Washington DC

AllenMWhite — Wed, 28 Nov 2012 19:14:00 GMT

After a great time at the PASS Summit in Seattle I'll be once again presenting on PowerShell for SQL Server at SQL Saturday #173 in Chevy Chase, Maryland. On Friday, December 7 I'll be presenting my full day session Automate and Manage SQL Server with PowerShell. Here's the abstract:

This soup-to-nuts all day session will first introduce you to PowerShell, after which you'll learn the basic SMO object model, how to manipulate data with PowerShell and how to use SMO to manage objects. We'll then move on to creating Policy-Based Management policies, work with the Central Management Server, manage your system inventory and gather performance data with PowerShell. We'll wrap up with a look at the new PowerShell cmdlets introduced for SQL Server 2012 and how you can use PowerShell to manage SQL Server 2012 in server environments including the new Always On technology and Windows Server Core. After this one day you'll be ready to go to work and able to use PowerShell to make you truly effective.

On Saturday, for those who can't make Friday's session I'll present PowerShell 101 for the SQL Server DBA, the introductory module from the all day session.

If you've taken a look at Windows Server 2012 - and if you haven't yet, you should - you know that PowerShell is the tool Microsoft has chosen to allow you to manage hundreds, even thousands, of servers. Using the graphical tools available will only slow you down, making you more easily replaceable. Learning how to automate and manage lots of servers improves your efficiency and your value to your company. There's a real advantage to understanding how to use PowerShell to get things done quickly.

So I look forward to seeing you on Friday, December 7 and Saturday December 8 in Chevy Chase, for SQL Saturday #173!

Allen

PASS Summit 2012 PreCon - DBA-298-P Automate and Manage SQL Server with PowerShell

AllenMWhite — Thu, 08 Nov 2012 18:56:00 GMT

On Tuesday I presented an all-day pre-conference session on using PowerShell to automate and manage SQL Server. It was a very full day and we had a lot of great questions. One discussion in Module 6 was around scripting all the objects in a database, and I'd mentioned the script I wrote for the book The Red Gate Guide to SQL Server Team-based Development. When putting together the demos for the attendees to download I realized I'd placed that script in the Module 6 folder, so you don't need to go anywhere special to get it, it's there.

I've attached the demo material to this blog post, and those of you who stayed with me until the end of the day know the password for the file, and to the rest of you, please respect that these people paid to attend the session and the material is exclusively for them.

Thanks to everyone who attended!

Allen

[Speaking] PowerShell at the PASS Summit

AllenMWhite — Mon, 29 Oct 2012 14:49:00 GMT

Next week is the annual PASS Summit, the event of the year for those of us in the SQL Server community. We get to see our old friends, make new friends, and learn an amazing amount about SQL Server, and it'll be in Seattle, so it's close to the mother ship. I love having Microsoft close, because it's easier to get to know the people who actually make this amazing product we spend our lives working with.

This year I'm fortunate to have been selected to present three sessions. One is a regular session called Manage SQL Server 2012 on Windows Server Core with PowerShell, where I'll be showing you how to set up and install SQL Server 2012 on Windows Server 2008 R2 and Windows Server 2012 Server Core, with some cool things that make that setup easy to manage. I'm also doing a Spotlight session called Maintain SQL Server System and Performance Data with PowerShell, where I'll show you how to create a database to contain the inventory of the servers you manage, and how to gather performance metrics, all with PowerShell.

On Tuesday, I'll also be doing a pre-conference session all day called Automate and Manage SQL Server with PowerShell. I truly enjoy spending the day with this session. We start out with an introduction to PowerShell, because you really need some basics before the more advanced topics make sense. This is not a comprehensive PowerShell course, because that would take days, but the introduction will give you enough to truly get started.

Once we get the basics down, we'll dive into Server Management Objects (SMO), because the SQL Server team built this interface specifically for managing SQL Server. You may love SMO or you may hate it, but understanding its structure will help you automate your tasks as you manage your environment. We'll get into the various activities a DBA is expected to manage and walk through scripts that solve those problems. As the day goes on we'll get into managing Central Management Server and Policy-Based Management, and we'll cover the topics in the other sessions I'll be doing on Wednesday. We'll also see how much more important PowerShell is in Windows Server 2012 than ever before.

If you haven't sensed the excitement about the upcoming PASS Summit, check it out, and I hope to see you there!

Allen

Search For a Query in RDL Files with PowerShell

AllenMWhite — Tue, 16 Oct 2012 18:58:00 GMT

In tracking down poorly performing queries for clients I often encounter the query text in a trace file I've captured, but don't know the source of the query. I've found that many of the poorest performing queries are those written into the reports the business users need to make their decisions. If I can't figure out where they came from, usually years after the queries were written, I can't fix them.

First thing I did was find a great utility called RSScripter, which opens up a Windows dialog that allows you to connect to a Reporting Services server and script the reports to RDL source files in a directory you specify. By exporting the RDL files from the Reporting Services server I can search through and find the queries I need to find.

The next thing I do is set a string variable to contain the query in question, which I do here using a here-string:

$q = @'SELECT  d.SalesOrderID,
        d.OrderQty,
        h.OrderDate,
        o.Description,
        o.StartDate,
        o.EndDate
FROM    Sales.SalesOrderDetail d
        INNER JOIN Sales.SalesOrderHeader h ON d.SalesOrderID = h.SalesOrderID
        INNER JOIN Sales.SpecialOffer o ON d.SpecialOfferID = o.SpecialOfferID
WHERE   d.SpecialOfferID <> 1'@

Now, when RSScripter runs it pulls out everything from RS, and I'm only interested in the RDL files so I use the Get-ChildItem cmdlet and pipe the output through the Where-Object cmdlet to select just those files whose extension is '.rdl', and assign that to a variable.

$rdls = Get-ChildItem | where-object {$_.Extension -eq '.rdl'}

Now I have a collection of RDL file objects, which I can iterate through with the foreach command. For each one I'm going to display the file name, then use the Get-Content cmdlet to read the contents of the RDL file, and pipe that to the Select-String cmdlet to look for the query string. If a match is found it'll display that line after the file name.

foreach ($rdl in $rdls) { write-output $rdl.Name; get-content $rdl.FullName | select-string $q }

While this may be a little crude, it's kind of an ad-hoc way of finding where a query comes from. There are recursive options for Get-ChildItem that'll search the entire subdirectory tree, but for now I didn't need that.

I hope this helps you find some of those troublesome queries you're struggling with.

Allen

Scanning the Error Log with PowerShell

AllenMWhite — Tue, 25 Sep 2012 18:33:00 GMT

One of the most important things you can do as a DBA is to keep tabs on the errors reported in the error log, but there's a lot of information there and sometimes it's hard to find the 'good stuff'. You can open the errorlog file directly in a text editor and search for errors but that gets tedious, and string searches generally return just the lines with the error message numbers, and in the error log the real information you want is in the line after that.

PowerShell 2.0 introduced a new cmdlet called Select-String which searches through a text file and returns the lines matching the target string. What it adds is an command-line parameter [-context] which allows you to specify the number of lines before and after the match is found. Bingo! By specifying the parameter -context 0,1 with the Select-String cmdlet it returns the line with the error, and the line afterwards.

To see what this does, first navigate to the errorlog directory, then issue the following commands:

$errlog = Get-Content '.\ERRORLOG'
$errlog | Select-String  -pattern 'Error:' -context 0,1

Now that's really useful, but it does mean that I have to log on to each server to get the errors, or I have to somehow know where every server's errorlog directory is and connect through the network to get to the files. Not so nice if you want to work from a client and 'just get the errors'.

Using SMO, there's a method under the Server object called ReadErrorLog() which reads the error log and (if assigned to a variable) creates a DataRow object containing the LogDate, ProcessInfo and Text properties from the errorlog. The ProcessInfo property contains the spid or other identifying value to indicate the source of the problem. By connecting to an instance using the Server object and loading the error log into the DataRow object, we can then get similar results, but we have to add an additional parameter, -inputobject, which lets us specify the property to search for our string.

$svr = new-object ('Microsoft.SqlServer.Management.SMO.Server') MyServer
$err = $svr.ReadErrorLog()
$err | Select-String -inputobject {$_.Text} -pattern 'Error:' -context 0,1

The advantage of this solution is that I don't log into the target server and I don't have to know where the log file is. The downside is that we lose the LogDate and ProcessInfo properties altogether, and there's no way to piece the parts back together. (Now, as sure as I say this Chad Miller will pipe up with a cleaner way to do this. This is good, and it's how I learn.)

What I found that works, though it's a tad clunky for my taste, is to concatenate the properties back together before sending it to the Select-String cmdlet, like this:

$errlog = @()
$err | foreach { $errlog += [string] $_.LogDate + ' ' + $_.ProcessInfo + ' ' + $_.Text }

The problem with this approach, of course, is you have to pass through the errorlog twice. To minimize this, I added an option to only report the errors starting with a particular date.

$errlog = @()
$err | where {$_.LogDate -ge $startdt} | foreach {
	$errlog += [string] $_.LogDate + ' ' + $_.ProcessInfo + ' ' + $_.Text
	}

This minimizes the issue, and if you're properly cycling the error log (normally once a week) the amount of log data it has to cycle through twice isn't too bad. With the properties concatenated we then return to the original search command to get our results.

$errlog | Select-String  -pattern 'Error:' -context 0,1

But wait, there's more! After playing with it for a bit I realized I'd like an option to just return DBCC results. Unlike errors, DBCC results are reported on the same line as the DBCC reports it was run, so I don't need the line afterward. In my script I added a variable called $srch to the command line arguments, and if it's equal to 'DBCC' it just returns the DBCC lines.

# Search the errorlog and return any error and the subsequent detailed message
if ($srch -eq 'DBCC') {
	$errlog | select-string -pattern 'DBCC' -context 0,0
	}
else {
	$errlog | select-string -pattern 'Error:' -context 0,1
	}

And that's it! I named the script scan-errorlog.ps1, and it takes the instance name, start date and (optionally) 'DBCC' as parameters, and returns either errors for that server after the start date, or DBCC results since the start date.

./scan-errorlog.ps1 MyServer 9/25/2012

That command line will return any errors reported in the errorlog since midnight last night.

It's really fun to find gems like this that make our lives as a DBA much easier.

Allen