Powershell Megathread: Verb-Noun Scripting is the future

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Powershell Megathread: Verb-Noun Scripting is the future

Z-Bo: Jul 2, 2005; more like z-butt

Been hacking in Powershell lately to automate some bullshit garbage that I can now pass off to somebody who can just click the script and monitor it to make sure nothing failed.

See http://z-bo.tumblr.com/post/657962194/optimizing-vba where I have been dabbling in using Powershell to mess with CSV files, using the Excel COM API. It was rough getting used to the API and what idioms worked best, but now that I have done a bit I like it. A cheat sheet for Powershell and Excel would be a nice addition to the Powershell community.

I'll bump this thread in the future with some neat Powershell idioms Victor and I came up with about a year and a half ago that have proven rock solid and seriously cut down on lines of code.

# ¿ Jun 3, 2010 00:29

Adbot: ADBOT LOVES YOU

# ¿ Apr 27, 2024 20:23

Z-Bo: Jul 2, 2005; more like z-butt

This might help. Most Microsoft products have a customized version of PowerShell. Exchange's is called Exchange Management Shell

You can also use:

get-command *queue*
get-command *message*
in PowerShell to search for Exchange Message Queue-related or Message-related CMDlets.

Then, having realized I hit jackpot, I would take get-queue and type it into Google Books.

http://www.google.com/search?tbs=bks%3A1&tbo=1&q=get-queue&btnG=Search+Books

The first link, Mastering Microsoft Exchange Server 2007 - Page 733, probably gets you as far as I can get you.

Note: I am not an Exchange Admin, and figured this all out in 10 mins. What I am teaching you is how to get information on how to do tasks in PowerShell.

# ¿ Jun 16, 2010 16:24

Z-Bo: Jul 2, 2005; more like z-butt

I need some goon help.

Mobsync.exe is pegging my CPU at 100% every 5 seconds. I have done just about everything I could find on the Internet to disable this:

1) I've disabled offline files
2) I've tried deleting the USB Device Stack and rebooting so that Vista automatically rebuilds it to fix any device ordering issues
3) I've stopped the Portable Device Enumerator Service and set it to Disabled.
4) I've changed the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WINEVT\Publishers\{b44aec44-38f4-4b59-8 df3-10306abf19b2} property 'Enabled' to 0.
5) I've read that you can disable Execute/Traverse permissions for Everyone on C:\Windows\System\mobsync.exe but am not sure how to do it using PowerShell, and do not want to try a non-PowerShell solution (if I am going to fix this problem, then I refuse to rely on strange Windows programs I have never heard of like xcacl.exe -- it is just one more piece of crap I don't need installed on my computer, and I don't want to install that guys tool either).

How do I use set-acl to get rid of mobsync.exe's TrustedInstaller ownership and revoke traverse/execute from Everyone?

# ¿ Jun 17, 2010 18:36

Z-Bo: Jul 2, 2005; more like z-butt

Can you provide a more detailed problem report?

For example, can you print out the commands you are using to search your PowerShell environment? What is your path variable? Does it include Exchange? What is the ExecutionPolicy for that path? etc.

# ¿ Sep 7, 2010 19:35

Z-Bo: Jul 2, 2005; more like z-butt

I have an idea and want to throw it out there to get feedback from goons.

What do you all think about the idea of writing a PowerShell snap-in that can automatically report on stuff like licenses and installed software on a machine?

It seems with WMI we can pretty much automate what a free solution like LANSweeper does, without requiring a client-side executable for it to work. PowerShell would simply use the new remoting features in 2.0 and you'd automatically get ops reports, centralized into perhaps a SQL Server database so we could use SQL Server Reporting Services for building dashboards like LANSweeper has.

# ¿ Nov 22, 2010 21:15

Z-Bo: Jul 2, 2005; more like z-butt

I am in disbelief.

I was writing a PowerShell script to dump our database schema to version control and ran into the stupidest bug in the whole world: [PS V2 RTM] Multiple Ambiguous Overloads

Wow! This totally wasted my entire afternoon trying to figure out. Also, unfortunately, the workaround in the article doesn't appear to work for me. ALL I WANT TO DO IS DUMP USER DEFINED FUNCTIONS and AGGREGATES!!! This problem doesn't occur for Tables, Views, Triggers and Stored Procedures due to the interface differences between those and UDFs and UDAs.

I tried the workaround suggested in the above link, but it was ultimately an epic failure.

Right now I am trying to figure out how to use System.Reflection to invoke the right signature in PowerShell, which is gross.

Help me.

code:

param 
(
  [string] $server,
  [string] $database,
  [string] $schema = "dbo",
  [string] $output_directory = "$HOME\Desktop\repos\dump\"
)

# Example call
# C:\SQL\Powershell\Examples\script_data.ps1 ".\sql2005" "tempdb" "dbo" "temp"
# or specify output file
# C:\SQL\Powershell\Examples\script_data.ps1 ".\sql2008" "tempdb" "dbo" "temp" "c:\temp\temp.sql"

if ($server -eq $null)
{
	exit;
}
if ($database -eq $null)
{
	exit;
}

Remove-Item -Force -Recurse -LiteralPath "$output_directory\UserDefinedFunction\"

New-Item -Force -Type Directory -Path "$output_directory\UserDefinedFunction\"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.Sdk.Sfc") | out-null

$srv = New-Object ("Microsoft.SqlServer.Management.SMO.Server") $server
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database")
$tbl = New-Object ("Microsoft.SqlServer.Management.SMO.Table")
$scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($server)

# Get the database and table objects
$db = $srv.Databases[$database]
$tables = $db.tables | Where-object { $_.schema -eq $schema } 

# Set scripter options to ensure only schema is scripted
$scripter.Options.ScriptSchema = $true;
$scripter.Options.ScriptData = $false;
$scripter.Options.NoCommandTerminator = $false; # Include GOs after every script
$Scripter.Options.DriAll=$True; # DRI = "Declarative Referential Integrity"
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False

$udfs = $db.UserDefinedFunctions | Where-object { $_.Schema -eq $schema } 

if ($udfs.Count -ne 0)
{
	foreach ($udf in $udfs)
	{
		[Microsoft.SqlServer.Management.Smo.UrnCollection] $smoObjects = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection;
		$smoObjects.Add($udf.Urn);
		Write-Host "Dumping $udf";
		$output_filename = $output_directory + "\UserDefinedFunction\" + $udf.Name + ".sql";
		$scripter.Options.FileName = $output_filename;
		$Scripter.Options.AppendToFile=$False;
		$Scripter.Options.ScriptDrops=$True;
		$Scripter.Options.IncludeIfNotExists=$True;
		$Scripter.Script([Microsoft.SqlServer.Management.Smo.UrnCollection]$($smoObjects));
		$Scripter.Options.ScriptDrops=$False;
		$Scripter.Options.IncludeIfNotExists=$False;
		$Scripter.Options.AppendToFile=$True
		$Scripter.Options.Permissions = $True;
		$Scripter.Script([Microsoft.SqlServer.Management.Smo.UrnCollection]$($smoObjects));
	}
}

Z-Bo fucked around with this message at 21:27 on May 6, 2011

# ¿ May 6, 2011 20:55

Z-Bo: Jul 2, 2005; more like z-butt

code:

$Scripter.Script([Microsoft.SqlServer.Management.Smo.UrnCollection]$($smoObjects));

Originally, my code was just:

code:

$Scripter.Script($udf);

But that didn't work either.

Both say roughly the following error:

code:

Multiple ambiguous overloads found for "Script" and the argument count: "1".
At C:\Users\ME\Desktop\dumper\Script-DatabaseObjectsNew.ps1:188 char:19
+         $Scripter.Script <<<< ([Microsoft.SqlServer.Management.Smo.UrnCollection]($($smoObjects)));
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

I am trying to get MethodInfo Invoke working but it is a huge PITA in PowerShell as compared to doing it in C#.

I've also tried:

code:

$params = new-object object[] 1;
$params[0] = $smoObjects;
[Microsoft.SqlServer.Management.SMO.Scripter].GetMethod("Script", [System.Type[]] @([Microsoft.SqlServer.Management.Smo.UrnCollection])) | % { $_.Invoke($scripter, $params) }

but it produces the following error:

code:

Exception calling "Invoke" with "2" argument(s): "Script failed for Server 'MyServerName'. "
At C:\Users\ME\Desktop\dumper\Script-DatabaseObjectsNew.ps1:189 char:155
+         [Microsoft.SqlServer.Management.SMO.Scripter].GetMethod("Script", [System.Type[]] @([Microsoft.SqlServer.Mana
gement.Smo.UrnCollection])) | % { $_.Invoke <<<< ($scripter, $params) }
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodTargetInvocation

Z-Bo fucked around with this message at 22:15 on May 6, 2011

# ¿ May 6, 2011 22:08

Z-Bo: Jul 2, 2005; more like z-butt

adaz posted:

I can't get it to reproduce the error (also those ; aren't needed )

So I ran a parsed down version of your script and it worked fine with no errors whatsoever. This makes me think that maybe you're passing an empty $smoObjects or something stupid to it -- if you try to manually do all this by hand and check each output object what type is smoObjects.GetType() returning to you and are there actually objects being stored in it?

Thanks for the help thinking through how to better debug this problem.

1) smoObjects.GetType() --> an SMO UrnCollection
2) smoObjects.Count --> 1

Can anyone help me with the reflective invoking of the Scripter.Script(...) method?

I also don't understand why System.Reflection.MethodInfo's Invoke(..., ...) method isn't recognized by PowerShell.

It may also help to repeat that this appears to be a known PSv2 bug and I need a workaround.

Z-Bo fucked around with this message at 15:13 on May 7, 2011

# ¿ May 7, 2011 15:07

Z-Bo: Jul 2, 2005; more like z-butt

This time it is complaining it can't find the Script method on the Scripter object.

code:

Exception calling "Script" with "1" argument(s): "Script failed for Server 'asp6'. "
At C:\Users\ME\Desktop\repos\dumper\Script-DatabaseObjectsNew.ps1:184 char:18
+     $Scripter.Script <<<< ($smoObjects)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

This is completely insane. The code works on the foreach block above it that goes through all the stored procedures, and so on through tables, views, triggers.

I am going to code this up in C# just to make sure I don't suddenly completely suck as a programmer.

# ¿ May 9, 2011 21:04

Z-Bo: Jul 2, 2005; more like z-butt

Yup! Actually, debugging a little further thanks to C# made it a little clearer what the issue was. Basically, $udfs was null - I started testing on a new database today and hadn't compiled UDFs or UDAs yet.

So this was a totally different problem from the other day. Your code works, it just needs:

code:

if ($udfs)
{
  #foreach loop here
}

What a pain in the rear end.

Edit: Thanks for your help.

# ¿ May 9, 2011 21:33

Z-Bo: Jul 2, 2005; more like z-butt

Thanks.

For debugging, on the SQLPSX project I told everyone my general trick fellow goon Victor and I came up with for helping improve PowerShell tracing. PowerShell Tracing module just is too verbose and non-configurable as far as I can tell (compared to say C# or VB.NET or any .NET language). So what we did 2 years ago was this simple idiom:

code:

# Wrap ActualCommand in Invoke-Command, for logging purposes
function Invoke-Command ($argument_1, $argument_2)
{
	if (!$argument_1)
	{
		throw "`$argument_1 is not defined"
	}
	if (!$argument_2)
	{
		throw "`$argument_2 is not defined"
	}

	$invocation = {ActualCommand -Argument1 $argument_1 -Argument2 $argument_2}
	Write-Host "$invocation`r"
	Write-Host "`$argument_1=$argument_1, `$argument_2=$argument_2"
	& $invocation

	if ($LASTEXITCODE -ne 0)
	{
		$LASTEXITCODE
		throw "$invocation`r`$argument_1=$argument_1, `$argument_2=$argument_2`r exited with an error return code: $LASTEXITCODE"
	}
}

This tells you most things you want to know about the program you called:

What program did I actually call when I ran this PS function?
What arguments were passed to it?
Did I make any mistakes when calling this PS function?
Did the program complete without errors? If there are errors, use exception handling instead of having to check return codes. Useful for very large scripts Victor and I were writing.

If you scroll back in this thread, I promised to post stuff like this a long, long time ago but was just too lazy.

The basic idea is it lets you abstract away the underlying Windows command line program you are wrapping, and provide PowerShell defensive error handling for it. You can also use it to check that the program even exists, and possibly even install it if it doesn't.

I need to improve it so that it is generic. The signature should be:

code:

function Invoke-Command ($command, $args)

And rather than use the syntactic sugar { -Argument_1 = $argument_1 } for script blocks, just build the script block imperatively using the underlying PowerShell API.

quote:

I do appreciate that script though, on my List Of Things To Do had been a script to pull off all the stored procedures/functions off a DB and hey now I have one

I can post the final version here if people like, but I might put together a polished version for the ISE module in SQLPSX (I am a core contributor to that project, although none of my modules have yet to make it into a release/been reviewed by the team; I am working on a super-awesome profiling and tracing SQLPSX API that hooks into the SqlIse module since the long-term goal of the project is to let you use PowerShell-ISE to replace the god awful SQL Server Management Studio).

Z-Bo fucked around with this message at 00:43 on May 10, 2011

# ¿ May 10, 2011 00:40

Adbot: ADBOT LOVES YOU

# ¿ Apr 27, 2024 20:23

Z-Bo: Jul 2, 2005; more like z-butt

Just chiming in to say: Cha-CHING!

Googled "Powershell ScriptBlock class" today to go lookup the documentation on how to rewrite my Invoke-Command wrapper, and found somebody else's ConvertTo-ScriptBlock snippet.

This is so incredibly useful I felt I had to share it with you all and rejoice.

# ¿ May 11, 2011 20:24

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Powershell Megathread: Verb-Noun Scripting is the future