Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server PowerShell Extensions (SQLPSX) Part 1 Expand / Collapse
Author
Message
Posted Saturday, October 4, 2008 4:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:01 AM
Points: 258, Visits: 701
Comments posted to this topic are about the item SQL Server PowerShell Extensions (SQLPSX) Part 1


Post #580775
Posted Monday, October 6, 2008 11:24 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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
Posted Monday, October 6, 2008 4:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, October 6, 2008 5:54 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:01 AM
Points: 258, Visits: 701
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
Posted Tuesday, October 7, 2008 2:21 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,299, Visits: 1,378
Nice article.....


Post #581587
Posted Thursday, October 9, 2008 7:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 1, 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
Posted Saturday, October 11, 2008 1:55 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:01 AM
Points: 258, Visits: 701
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
Posted Sunday, October 12, 2008 9:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 1, 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
Posted Saturday, February 21, 2009 10:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:40 AM
Points: 108, Visits: 310
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
Posted Sunday, February 22, 2009 7:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:01 AM
Points: 258, Visits: 701
Are you passing the SQL instances as a parameter? For example, like this:

./get-invalidlogins 'Z002\SQL2K8'



Post #662186
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse