InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 09:04:22 GMT20http://www.sqlservercentral.com/Forums/Topic580775-106-1.aspxJust 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.Thu, 14 May 2009 13:08:42 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic580775-106-1.aspxhehe 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!!!Fri, 13 Mar 2009 13:31:01 GMTjsimpson-988516http://www.sqlservercentral.com/Forums/Topic580775-106-1.aspx[quote][b]jsimpson (3/12/2009)[/b][hr]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. [/quote]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. [user] and only then does the insert work.Set-SqlData 'Z002\SQL2K8' dbautility "INSERT INTO dbo.UPSpod (Sheet, [User]) VALUES (4, 'Jim')"Fri, 13 Mar 2009 13:28:22 GMTcmille19http://www.sqlservercentral.com/Forums/Topic580775-106-1.aspxGreat 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. Thu, 12 Mar 2009 09:21:09 GMTjsimpson-988516http://www.sqlservercentral.com/Forums/Topic580775-106-1.aspxGreat 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: .[b] .\LibrarySmo.ps1[/b]To get this step to work I had to swap the slash around i.e. [b]. ./LibrarySmo.ps1[/b]Just thought it was worth mentioning in case other beginners like me get stuck at that point.Mon, 09 Mar 2009 10:32:12 GMTJason Dennehttp://www.sqlservercentral.com/Forums/Topic580775-106-1.aspxAre you passing the SQL instances as a parameter? For example, like this:./get-invalidlogins 'Z002\SQL2K8'Sun, 22 Feb 2009 07:28:34 GMTcmille19http://www.sqlservercentral.com/Forums/Topic580775-106-1.aspxCan 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 followPS [TPT60P]&gt;.\Get-InvalidLoginsUnexpected 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 &lt;&lt;&lt;&lt; { $_.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( &lt;&lt;&lt;&lt; $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[$ &lt;&lt;&lt;&lt; sqlserver]Sat, 21 Feb 2009 22:14:04 GMTSal Younghttp://www.sqlservercentral.com/Forums/Topic580775-106-1.aspxThankyou for the tip on how to package SQL with embedded parameters. I will do this (a lot).Sun, 12 Oct 2008 21:07:36 GMTkettleshttp://www.sqlservercentral.com/Forums/Topic580775-106-1.aspxWhen 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.[code]param($au_lname)$scriptRoot = Split-Path (Resolve-Path $myInvocation.MyCommand.Path). $scriptRoot\LibrarySmo.ps1$srcServer = 'Z002\SqlExpress'$qry = @"SELECT * FROM dbo.authorsWHERE au_lname = '$au_lname'"@ Get-SqlData $srcServer 'pubs' $qry[/code]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.[code]./getAuthor "White"[/code]Also when I need to load the contents of a file such as a .sql file, I'll use the .NET ReadAllText method:[code] $qry = [System.IO.File]::ReadAllText("c:\users\u00\scripts\pubqry.sql")[/code]As far Get-Help, eventually I plan on creating a proper snapin with Get-Help functionality in a future release.Sat, 11 Oct 2008 13:55:28 GMTcmille19http://www.sqlservercentral.com/Forums/Topic580775-106-1.aspxThankyou for these extensions to the Powershell. I would like [b]Integrated help[/b] That is I would like[code]get-help get-sqlserver[/code]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[code]get-sqldata -script myScript -arg1 myArg1 -arg2 myArg2 [/code]or, better still, to make a powershell script with a call to get-sqldata buried in it and invoke it like this[code]./runSQL -arg1 myArg1 . . . etc[/code]Thu, 09 Oct 2008 19:27:07 GMTkettleshttp://www.sqlservercentral.com/Forums/Topic580775-106-1.aspxNice article.....Tue, 07 Oct 2008 02:21:47 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic580775-106-1.aspxI 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.Mon, 06 Oct 2008 17:54:02 GMTcmille19http://www.sqlservercentral.com/Forums/Topic580775-106-1.aspxWhat ever happened to EDITORS? This article has run on after run on! Geez. The first paragraph has this monster:[u][i]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][/u]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.Mon, 06 Oct 2008 16:31:08 GMTJim4Prez@gmail.comhttp://www.sqlservercentral.com/Forums/Topic580775-106-1.aspxGood 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.Mon, 06 Oct 2008 11:24:45 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic580775-106-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/powershell/64316/">SQL Server PowerShell Extensions (SQLPSX) Part 1</A>[/B]Sat, 04 Oct 2008 16:31:56 GMTcmille19