Month: November 2011

Creating & Passing Enums in Powershell

To create an enumeration in powershell:

$msgTypeEnum = " 
namespace MessageTypes 
{ 
	public enum MessageType 
	{ 
		NEW_JOB = 4, 
		FAILED_JOB = 5, 
		JOB_REMOVED = 6, 
		JOB_SCHED_CHANGE = 7, 
		SERV_NOT_REACHABLE = 8, 
		CLUST_FAILOVER = 9, 
		LOW_DRIVE_SPACE = 10,
		LONG_RUNNING_JOB = 11
	} 
} 
" 
Add-Type -TypeDefinition $msgTypeEnum -Language CSharpVersion3

To pass said enumeration to a function (notice the parentheses around the enumeration value):

SendMessage $server.ServerName ([MessageTypes.MessageType]::LONG_RUNNING_JOB) "Long Running Job Alert" $msg

And, in the function (if you’re inserting the value into sql server) you’ll need to convert it to an int:

function SendMessage([string]$ServerName, [MessageTypes.MessageType]$MessageTypeID, [string]$Subject, [string]$Message)
{
	#Write-Host "EXEC dbo.usp_AddMessage @ServerName='$ServerName', @MessageTypeID=$MessageTypeID, @Subject='$Subject', @Message='$Message'"
	$nMsgType = [int]$MessageTypeID
	SaveData "EXEC dbo.usp_AddMessage @ServerName='$ServerName', @MessageTypeID=$nMsgType, @Subject='$Subject', @Message='$Message'"
}

Script Database Objects with Powershell

Here’s a quick script to script out all database objects using powershell.  This was adapted (heavily) from the Hey Scripting Guy! blog script here.  This also stores the password credential in a file on the server.  You’ll need to generate the file before running the script.  Remember to generate the file under the account that will be executing the script, as the contents of the file will be encrypted using the account that generated it.  Use this script to create the file:

read-host -assecurestring | convertfrom-securestring | out-file C:\Testcredentials.txt

And, here is the script to script out the objects.  I use this to create a backup of the schema daily in case any changes need to be rolled back.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.smo') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Common') | out-null

$servers = Get-Content c:TestServers.txt
$savePath = "C:TestScriptDB"
$Error.Clear()
cls

$currDate = get-date -format yyyyMMdd

foreach($server in $servers)
{
	$password = get-content C:Testcredentials.txt | convertto-securestring
	$srvConn = New-Object('Microsoft.SqlServer.Management.Common.ServerConnection') ($server, 'LogonName', $password)
	$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $srvConn
	$scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($srv)
	$scriptr.Options.AppendToFile = $True
	$scriptr.Options.AllowSystemObjects = $False
	$scriptr.Options.ClusteredIndexes = $True
	$scriptr.Options.DriAll = $True
	$scriptr.Options.ScriptDrops = $False
	$scriptr.Options.IncludeHeaders = $True
	$scriptr.Options.ToFileOnly = $True
	$scriptr.Options.Indexes = $True
	$scriptr.Options.Triggers = $true
	$scriptr.Options.Permissions = $True
	$scriptr.Options.WithDependencies = $False
	try
	{

		foreach($database in $srv.Databases | where{!($_.IsSystemObject)})
		{
			$dbName = $database.Name
			$serverName = $server.Replace("","-")

			if(!(Test-Path $savePath$serverName$currDate$dbName))
			{
				New-Item -ItemType directory -Name $serverName$currDate$dbName -Path $savePath
			}

			$dbObj = $database.Tables
			$dbObj += $database.Views
			$dbObj += $database.StoredProcedures | where{!($_.IsEncrypted)}
			$dbObj += $database.UserDefinedFunctions | where{!($_.IsEncrypted)}

			foreach ($scrptObj in $dbObj | where {!($_.IsSystemObject)})
			{
				$TypeFolder = $scrptObj.GetType().Name
				if ((Test-Path -Path "$savePath$serverName$currDate$dbName$TypeFolder") -eq "true")
				{
					"Scripting Out $TypeFolder $scrptObj"
				}
				else
				{
					new-item -type directory -name "$TypeFolder" -path "$SavePath$serverName$currDate$dbName"
				}
				$ScriptFile = $scrptObj -replace "[|]"
				$scriptr.Options.FileName = "$savePath$serverName$currDate$dbName$TypeFolder$ScriptFile.sql"
				$scriptr.Script($scrptObj)
			}

		}
	}
	catch
	{
		foreach($err in $Error)
		{
			Write-Host $err
			write-host $err.ErrorDetails
			write-host $err.Exception
			Write-Host $err.Exception.InnerException
			Write-Host $err.Exception.StackTrace
		}
	}
}

Compress a Directory with 7-zip and Powershell

Ensure you have 7-zip installed, obviously.  I adapted this from some other code I found on the internets, but unfortunately I can’t find the original post, so apologies for not giving the original author props.

# Alias for 7-zip
if (-not (test-path "$env:ProgramFiles\7-Zip\7z.exe")) {throw "$env:ProgramFiles\7-Zip\7z.exe needed"}
set-alias sz "$env:ProgramFiles\7-Zip\7z.exe"

$path = "C:\Test\ScriptDB\"

foreach($dir in gci -Path $path | where{$_.PSIsContainer})
{
	$zipFile = $path + $dir.Name + ".zip"
	sz a -tzip "$zipfile" $dir.FullName
	Remove-Item $dir.FullName -Recurse -Force
}

ConsoleColor error in Powershell job step in Sql Server

If you see an error like this when you execute a powershell job in sql server, it is due to an errant cls in your powershell script.  Remove the cls and it should execute correctly.  There is no screen to clear, hence it errors out.

A job step received an error at line 3 in a PowerShell script. The corresponding line is ‘$space.ForegroundColor = $host.ui.rawui.ForegroundColor’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Exception setting "ForegroundColor": "Cannot convert null to type "System.ConsoleColor" due to invalid enumeration values. Specify one of the following enumeration values and try again. The possible enumeration values are "Black, DarkBlue, DarkGreen, DarkCyan, DarkRed, DarkMagenta, DarkYellow, Gray, DarkGray, Blue, Green, Cyan, Red, Magenta, Yellow, White"."  ‘.