SQL Server PowerShell Extensions (SQLPSX) Part 1

  • cmille19

    SSCertifiable

    Points: 5920

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

  • SanjayAttray

    SSChampion

    Points: 13157

    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.

  • Jim4Prez@gmail.com

    Valued Member

    Points: 56

    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.

  • cmille19

    SSCertifiable

    Points: 5920

    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.

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice article.....

  • kettles

    SSC Enthusiast

    Points: 152

    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

  • cmille19

    SSCertifiable

    Points: 5920

    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.

  • kettles

    SSC Enthusiast

    Points: 152

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

  • Sal Young

    SSC-Addicted

    Points: 479

    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]

  • cmille19

    SSCertifiable

    Points: 5920

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

    ./get-invalidlogins 'Z002\SQL2K8'

  • Jason Denne

    SSC Rookie

    Points: 25

    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.

  • jsimpson-988516

    SSC Rookie

    Points: 32

    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.

  • cmille19

    SSCertifiable

    Points: 5920

    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')"

  • jsimpson-988516

    SSC Rookie

    Points: 32

    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!!!

  • Grant Fritchey

    SSC Guru

    Points: 395267

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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