SQL PowerShell details revealed

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-Sqlcmd
  • Decode-SqlName
  • Encode-SqlName
  • Convert-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: ,

13 Responses to “SQL PowerShell details revealed”


  1. 1 Joe Brinkman November 17, 2007 at 1:00 am

    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.

  2. 2 Joel "Jaykul" Bennett November 17, 2007 at 3:45 am

    Does Run-Sqlcmd let you run queries?

  3. 3 dmitrysotnikov November 19, 2007 at 4:09 pm

    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

  4. 4 Harley Green April 14, 2008 at 3:53 pm

    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

  5. 5 dmitrysotnikov April 16, 2008 at 10:55 pm

    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


  1. 1 Exciting news: PowerShell support in SQL Server 2008! « Poshoholic Trackback on November 17, 2007 at 3:12 am
  2. 2 PowerShell mandatory after June 30, 2008 « Dmitry’s PowerBlog: PowerShell and beyond Trackback on November 23, 2007 at 5:21 pm
  3. 3 Ace DDL » PowerShell mandatory after June 30, 2008 Trackback on November 26, 2007 at 6:56 am
  4. 4 Bink.nu | PowerShell mandatory after June 30, 2008 Trackback on November 27, 2007 at 10:56 am
  5. 5 The SQL Server PowerShell Provider Trackback on April 12, 2008 at 4:27 am
  6. 6 LINQ for PowerShell? « Dmitry’s PowerBlog: PowerShell and beyond Trackback on April 14, 2008 at 7:31 am
  7. 7 IIS 7 PowerShell Rant « Dmitry’s PowerBlog: PowerShell and beyond Trackback on May 26, 2008 at 2:32 pm
  8. 8 More SQL Cmdlets « Dmitry’s PowerBlog: PowerShell and beyond Trackback on September 26, 2008 at 8:01 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




My Recent Tweets

Legal

The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer - WSO2 or anyone else for that matter. All trademarks acknowledged.

© 2007-2014 Dmitry Sotnikov

November 2007
M T W T F S S
« Oct   Dec »
 1234
567891011
12131415161718
19202122232425
2627282930  

%d bloggers like this: