Prof. Powershell

Easy SQL Rider

You'll likely use a script in order to use SQL expressions in PowerShell. But you might consider this efficient alternative.

• By Jeffery Hicks
• 01/19/2010

Windows PowerShell can take advantage of the .NET SQL (and other) database-related classes to connect to database servers and extract information. Querying a SQL server using these classes requires about 10 lines of code just to get the query executed, then you have to parse the results. Consequently, using SQL expressions in PowerShell was almost always done in a script.

But there is a very slick and efficient alternative if you have just about any flavor of SQL Server 2008 installed.

The easiest approach is to install the freely available SQL Server 2008 Express on a Windows 7 client. You don't need to set up any local databases. All we want are the PowerShell snapins. With these, we can connect to any SQL server in our enterprise. The PSSnapins aren't loaded by default, but you should be able to see them as registered:

PS C:\> get-pssnapin sql* -registered

Name        : SqlServerCmdletSnapin100
PSVersion   : 2.0
Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.

Name        : SqlServerProviderSnapin100
PSVersion   : 2.0
Description : SQL Server Provider

The provider snapin creates a SQL PSDrive, but for this lesson we want to load the cmdlet snapin:

PS C:\> Add-PSSnapin SqlServerCmdletSnapin100

This snapin only has a few cmdlets:

PS C:\> gcm -ps sql* | select name

Name
----
Invoke-PolicyEvaluation
Invoke-Sqlcmd

We will be using Invoke-Sqlcmd. Be sure at some point to read the full help and examples. But here's how easy it is to use:

PS C:\> Invoke-Sqlcmd -Query "Select computername from computers" -Database ComputerData -ServerInstance ".\sqlexpress"

computername
------------
Win7Test
GODOT7
JDHIT-DC01
MYCOMPANY-DC01

It's as simple as that! The query parameter is self-explanatory and can be any SQL query, including UPDATE, INSERT and DELETE. You need to know the table and database names. You also need to specify the server to query. In my example, I'm querying the local SQLExpress instance, but this could easily have been a full SQL server on the network.

The cmdlet has no provision for alternate Windows credentials so you must run the expression with appropriate credentials. However, there are parameters (Username, Password) that you can use for SQL authentication.

The cmdlet returns a DataRow object whose properties correspond to the table columns:

PS C:\> $data=Invoke-Sqlcmd -Query "Select * from computers" -Database ComputerData -ServerInstance ".\sqlexpress"
PS C:\> $data | gm -MemberType Property | select name

Name
----
Computername
OS
PhysicalMemoryBytes
RegisteredOrganization
RegisteredOwner
ReportDate
ServicePack

I can now use this information in PowerShell:

PS C:\> $data | test-connection

This expression works for me because the Test-Connection is expecting a pipelined object with a property name of Computername, which I have in $data. I could take this information, connect to each computer, get current service pack information and update the database if the value has changed. Using the Invoke-Sqlcmd cmdlet makes all of this much easier to accomplish. So if databases are part of your administrative PowerShell solutions, take a look at these new cmdlets.

Note: The SQL snapins are 32-bit only. If you are running a 64-bit OS, you won't see the snapins unless you start a 32-bit (x86) PowerShell session.