October 4, 2008 at 4:31 pm
Comments posted to this topic are about the item SQL Server PowerShell Extensions (SQLPSX) Part 1
October 6, 2008 at 11:24 am
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.
October 6, 2008 at 4:31 pm
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.
October 6, 2008 at 5:54 pm
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.
October 7, 2008 at 2:21 am
Nice article.....
October 9, 2008 at 7:27 pm
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
October 11, 2008 at 1:55 pm
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.
October 12, 2008 at 9:07 pm
Thankyou for the tip on how to package SQL with embedded parameters. I will do this (a lot).
February 21, 2009 at 10:14 pm
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]
February 22, 2009 at 7:28 am
Are you passing the SQL instances as a parameter? For example, like this:
./get-invalidlogins 'Z002\SQL2K8'
March 9, 2009 at 10:32 am
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.
March 12, 2009 at 9:21 am
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.
March 13, 2009 at 1:28 pm
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')"
March 13, 2009 at 1:31 pm
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!!!
May 14, 2009 at 1:08 pm
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