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
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
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"
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
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.
About the Author
Jeffery Hicks is an IT veteran with over 25 years of experience, much of it spent as an IT infrastructure consultant specializing in Microsoft server technologies with an emphasis in automation and efficiency. He is a multi-year recipient of the Microsoft MVP Award in Windows PowerShell. He works today as an independent author, trainer and consultant. Jeff has written for numerous online sites and print publications, is a contributing editor at Petri.com, and a frequent speaker at technology conferences and user groups.