Kirk, Richard, and Jeffrey have already blogged that SQL 2008 is (unless this gets canceled) going to have PowerShell support built in. Today on the last day of the IT Forum we learned the details. And frankly were not impressed. At least Richard and I – who were sitting on the front row and were ranting so much that Bob Beauchemin stopped giving us a word at some point (just kidding, Bob had this talk kind of imposed on him and did his job quite well despite not really being a PowerShell guy.)
Anyways, here are the details:
PowerShell is likely to debut in SQL 2008 in CTP 6 in February 2008.
What they will ship is a provider which will allow you to browse SQL databases and tables as a file system.
The provider will not give access to the actual data in the databases: only to the structure (down to columns) and configuration settings
You are not able to create items (tables, columns, rows) or copy them.
Changing any settings is very unintuitive because they have to be done via properties of objects. So you have to first cd into the database/table/column, and then do something like dir | foreach-object { $_.Method (parameter1, parameter2, etc.) } – this is not only lengthy but also not quite in line with PowerShell discoverability as you get no help or tab-completion for such calls.
A much better approach would have been a set of cmdlets but this was not the way selected by the team. At least not for now.
For now, there will be just 4 cmdlets with quite auxiliary tasks:
Run-SqlcmdDecode-SqlNameEncode-SqlNameConvert-UrnToPath
I hope that post 2008 RTM, SQL team will revise their PowerShell design and make it fit better into PowerShell principles and provide better ways of managing SQL databases and accessing the data. Until then, the use will probably be limited to just browsing the databases, and for most tasks .NET classes being called from PowerShell syntax (like in this pack from PowerGUI library).
Tags: PowerShell, SQL
Subscribe by email
Dmitry's Blog: Cloud, PowerShell and beyond 
I guess I need to keep polishing my SMO scripts. Sometimes, just when you think that Microsoft “gets it” they do something completely useless. Based on what I have read so far, they are not providing any capability which doesn’t already exist with the many SMO scripts that are already floating about.
Does Run-Sqlcmd let you run queries?
Joel,
Run-SQLCmd basically runs sqlcmd utility. See this page for details: http://msdn2.microsoft.com/en-us/library/ms180944.aspx
This does indeed mean you will be able to run queries as well because with the sqlcmd you can do something like: sqlcmd -q “SELECT * FROM AdventureWorks.Person.Contact”
Dmitry
Powershell Community Extentions has a few cmdlets similar to the Run-SqlCmd
Get-SqlData executes a sql query, and returns a strongly typed object containing the result set.
Invoke-SqlCommand executes a query that does not return a result set, and returns the rows affected count instead.
These should be in the next release, due in a few weeks.
Thanks
Harley,
This is good but these cmdlets still require me to know the SQL command/query syntax. I don’t know these and frankly do not want to learn. I want my PowerShell knowledge to be re-used and have a much more PowerShell-like, and much less SQL-like experience.
Dmitry