SQL Server PowerShell Extensions (SQLPSX) Part 1

  • Comments posted to this topic are about the item SQL Server PowerShell Extensions (SQLPSX) Part 1

  • Good article with examples. Tried couple of them just now, but still I had a question? How many SQL DBA's use these commands and most of the info we get using powershell can get in Management studio. So, why use powershell? Also, to write these commands I think one has to have some knowledge of widows scripting.

    Well, I had the some or say minimal.

    SQL DBA.

  • What ever happened to EDITORS? This article has run on after run on! Geez. The first paragraph has this monster:

    For a DBA new to PowerShell scripting this presents a doubly step learn curve first you must learn the PowerShell language and then you must learn the SMO objects necessary to accomplish your task

    I was an CompSci. major and I am no grammar Nazi, but dang, that sentence is king run-on. There are 3 sentences there. Use a dang period, comma or colon.

    Sorry, I just happen to really dislike any article that could have valuable informative, yet it loses all value due to horrendous grammar.

    P.S. I am writing this drunk-as-a-skunk. There may be typos or grammar errors in my writting, yet nothing like this horrible article.

  • I consider using PowerShell or for that matter VBScript or Perl when I need to pull data from many SQL Servers, or need to implement a scriptable/repeatable solution (which excludes a GUI solution) or when I need to accomplish a task which would either be difficult or impossible to do in T-SQL. SQL Server Management Studio (SSMS) does not scale when you are working with hundreds of servers.

    But, the main reason I started the SQLPSX project was to solve a problem of reporting on the security information for hundreds of databases across many servers. The solution needed to pull the security data into a reporting database and recursively enumerate roles and AD groups. This would have been impossible to do in SSMS or T-SQL, but easily accomplished in PowerShell.

  • Nice article.....

  • Thankyou for these extensions to the Powershell. I would like

    Integrated help That is I would like

    get-help get-sqlserver

    to explain things.

    And I am hoping that I will be able to save a file's worth of SQL, which includes references to parameters ($arg1, $arg2, say), and invoke that say with

    get-sqldata -script myScript -arg1 myArg1 -arg2 myArg2

    or, better still, to make a powershell script with a call to get-sqldata buried in it and invoke it like this

    ./runSQL -arg1 myArg1 . . . etc

  • When I need to execute a query with parameters I just create a PowerShell script for that specific purpose and load LibrarySmo at the beginning of the script. Here's a simple example.

    param($au_lname)

    $scriptRoot = Split-Path (Resolve-Path $myInvocation.MyCommand.Path)

    . $scriptRoot\LibrarySmo.ps1

    $srcServer = 'Z002\SqlExpress'

    $qry = @"

    SELECT * FROM dbo.authors

    WHERE au_lname = '$au_lname'

    "@

    Get-SqlData $srcServer 'pubs' $qry

    To execute save the code as a ps1 file (here I'm using getAuthor.ps1). You'll also need to change the $srcServer variable or add it as a parameter to script and pass the server name.

    ./getAuthor "White"

    Also when I need to load the contents of a file such as a .sql file, I'll use the .NET ReadAllText method:

    $qry = [System.IO.File]::ReadAllText("c:\users\u00\scripts\pubqry.sql")

    As far Get-Help, eventually I plan on creating a proper snapin with Get-Help functionality in a future release.

  • Thankyou for the tip on how to package SQL with embedded parameters. I will do this (a lot).

  • Can someone tell me why I can't execute the Get-InvalidLogins. I set-location to the folder with all the SQLPSX code and execute as follow

    PS [TPT60P]>.\Get-InvalidLogins

    Unexpected token 'foreach' in expression or statement.

    At C:\Documents and Settings\syoung\My Documents\PowerShell\SQLPSX_1.3\LibrarySmo.ps1:86 char:23

    + $ds.Tables foreach <<<< { $_.Rows}

    Exception calling "Contains" with "1" argument(s): "Key cannot be null.

    Parameter name: key"

    At C:\Documents and Settings\syoung\My Documents\PowerShell\SQLPSX_1.3\Get-InvalidLogins.ps1:75 char:37

    + if (!($__SQLPSXInvalidLogin.Contains( <<<< $sqlserver)))

    Index operation failed; the array index evaluated to null.

    At C:\Documents and Settings\syoung\My Documents\PowerShell\SQLPSX_1.3\Get-InvalidLogins.ps1:80 char:30

    + return $__SQLPSXInvalidLogin[$ <<<< sqlserver]

    [font="Verdana"]Sal Young[/font]
    [font="Verdana"]MCITP Database Administrator[/font]

  • Are you passing the SQL instances as a parameter? For example, like this:

    ./get-invalidlogins 'Z002\SQL2K8'

  • Great article. Just a small point being a complete beginner to Powershell....but I had a problem sourcing the scripts into Powershell. i think it came down to a syntax error in your example :

    Launch PowerShell and source or dot the LibrarySmo.ps1 file using the following command:

    . .\LibrarySmo.ps1

    To get this step to work I had to swap the slash around i.e. . ./LibrarySmo.ps1

    Just thought it was worth mentioning in case other beginners like me get stuck at that point.

  • Great stuff. I have no problems at all with get-sqldata, but I cant get set-sqldata to work...

    Ok, so this works

    Get-SqlData 'VISTA-LAPPY\SQLEXPRESS' Worldship 'SELECT top 3 * FROM

    dbo.UPSData'

    But this does not...

    Get-SqlData 'VISTA-LAPPY\SQLEXPRESS' Worldship 'INSERT INTO dbo.UPSpod

    (Sheet, User) VALUES (4, Jim)'

    Nether does:

    Set-SqlData 'VISTA-LAPPY\SQLEXPRESS' Worldship "INSERT INTO dbo.UPSpod

    (Sheet, User) VALUES (4, 'Jim')"

    The insert statement works in SQL, so I am not sure what i am

    missing.... Hope you can help.

  • jsimpson (3/12/2009)


    Great stuff. I have no problems at all with get-sqldata, but I cant get set-sqldata to work...

    Ok, so this works

    Get-SqlData 'VISTA-LAPPY\SQLEXPRESS' Worldship 'SELECT top 3 * FROM

    dbo.UPSData'

    But this does not...

    Get-SqlData 'VISTA-LAPPY\SQLEXPRESS' Worldship 'INSERT INTO dbo.UPSpod

    (Sheet, User) VALUES (4, Jim)'

    Nether does:

    Set-SqlData 'VISTA-LAPPY\SQLEXPRESS' Worldship "INSERT INTO dbo.UPSpod

    (Sheet, User) VALUES (4, 'Jim')"

    The insert statement works in SQL, so I am not sure what i am

    missing.... Hope you can help.

    Thanks. I'm suprised the insert statement works in SQL for you. The column name "user" gives me an error message of "Incorrect syntax near the keyword 'user'". In both SQL Server Management Studio and Powershell I have to enclose the SQL reserved keyword "user" in brackets i.e. and only then does the insert work.

    Set-SqlData 'Z002\SQL2K8' dbautility "INSERT INTO dbo.UPSpod (Sheet, [User]) VALUES (4, 'Jim')"

  • hehe I figured that out too... I had been removeing the []'s in posh, because I thought I couldn't use them. Live and learn. 🙂 Thanks!!!

  • Just chiming in as someone getting started with PowerShell. Well done. Thanks for the work. I'm still just digging through it all & figuring it out.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply