SQL Server PowerShell Extensions (SQLPSX) Part 1

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Chad Miller » SQL Server PowerShell Extensions (SQLPSX)...

15 posts, Page 1 of 2

1 2 »»

SQL Server PowerShell Extensions (SQLPSX) Part 1

Rate Topic

Display Mode

Topic Options

Author

Message

cmille19

Posted Saturday, October 04, 2008 4:31 PM

SSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 5:46 AM
Points: 257, Visits: 671

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

Post #580775

SanjayAttray

Posted Monday, October 06, 2008 11:24 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554

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.

Post #581273

Jim4Prez@gmail.com

Posted Monday, October 06, 2008 4:31 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 12, 2012 1:53 PM
Points: 2, Visits: 39

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.

Post #581434

cmille19

Posted Monday, October 06, 2008 5:54 PM

SSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 5:46 AM
Points: 257, Visits: 671

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.

Post #581446

Anipaul

Posted Tuesday, October 07, 2008 2:21 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 2:50 AM
Points: 4,785, Visits: 1,334

Nice article.....

Post #581587

kettles

Posted Thursday, October 09, 2008 7:27 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, March 01, 2011 4:19 PM
Points: 12, Visits: 11

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

Post #583676

cmille19

Posted Saturday, October 11, 2008 1:55 PM

SSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 5:46 AM
Points: 257, Visits: 671

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.

Post #584436

kettles

Posted Sunday, October 12, 2008 9:07 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, March 01, 2011 4:19 PM
Points: 12, Visits: 11

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

Post #584640

Sal Young

Posted Saturday, February 21, 2009 10:14 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, March 30, 2013 2:45 PM
Points: 108, Visits: 273

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]

Sal Young
MCITP Database Administrator

Post #662138

cmille19

Posted Sunday, February 22, 2009 7:28 AM

SSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 5:46 AM
Points: 257, Visits: 671

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

./get-invalidlogins 'Z002\SQL2K8'

Post #662186

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions