Microsoft – PowerShell – HashTable – HardCoded Entries

One sometimes feels that in LIFE that there is no futility quite like trying to write a quick app, without the benefits of good Learning & deep studying.

Well, this last couple of days found me trying to stitch together a quick script…I need a

quick hard-coded list of Servers.  And, found one, by using the script below:

# This powershell script runs query against list of servers
$warningpreference = "Continue";
$errorpreference = "SilentlyContinue";

<%
The function (prepareSQLServerManagementPowerShell) below was 
copied (in whole) from MS'S web site:
 Adding the SQL Server Snap-ins to Windows PowerShell
 http://msdn.microsoft.com/en-us/library/cc281962(v=sql.100).aspx

%>

function prepareSQLServerManagementPowerShell
{
	# Adding the SQL Server Snap-ins to Windows PowerShell
	# http://msdn.microsoft.com/en-us/library/cc281962.aspx

	$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

	if (Get-ChildItem $sqlpsreg 
                -ErrorAction "SilentlyContinue")
	{
	  throw
   "SQL Server Provider for Windows PowerShell is not installed."
	}
	else
	{
$item = Get-ItemProperty $sqlpsreg
$sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
	}

	#
	# Set mandatory variables for the SQL Server provider
	#
	Set-Variable -scope Global
          -name SqlServerMaximumChildItems -Value 0
	Set-Variable -scope Global 
           -name SqlServerConnectionTimeout -Value 30
	Set-Variable -scope Global 
           -name SqlServerIncludeSystemObjects -Value $false
	Set-Variable -scope Global
-name SqlServerMaximumTabCompletion -Value 1000

	#
	# Load the snapins, type data, format data
	#
	Push-Location
	cd $sqlpsPath
	Add-PSSnapin SqlServerCmdletSnapin100
	Add-PSSnapin SqlServerProviderSnapin100
	Update-TypeData -PrependPath SQLProvider.Types.ps1xml 
	update-FormatData -prependpath SQLProvider.Format.ps1xml 
	Pop-Location

}

prepareSQLServerManagementPowerShell;

$instanceNameList = @(
			    "DBEngineering", "DBFinance"
                          , "DBSales"
			  , "DBAgriculture", "DBPublicRelations"
                          , "DBAccounting"
		      );

$myDBUser = "mySQLuser";
$myDBPassword = "mySQLPassword";
$query = "select @@servername as sname";

$instanceID = 0;

$objListofSQLResults = New-Object System.Collections.Hashtable;

foreach($instanceName in $instanceNameList) 
{ 

   $instanceID = $instanceID + 1;

   write-host "Executing query against server: " $instanceName;

   Try
   {

	$results4Query = Invoke-Sqlcmd -Query $query -ServerInstance $instanceName -U $myDBUser -P $myDBPassword;

   }
   Catch
   {
	#null object
	$results4Query = $null;

	$errMessage = $_.Exception.Message;
	$failedItem = $_.Exception.ItemName;
	$errMessageInFull = $_.Exception.ToString();

	$strLogFormat = "Unable to connect to Server {0} Failed Item {1} - Err Message {2} - Err Message Verbose {3}";

	$strLog = $strLogFormat -f $instanceName, $errMessage, $failedItem, $errMessageInFull;

	write-host $strLog;

   }

   $objListofSQLResults.Set_Item($instanceName, $results4Query);		

   # Clear Variable
   # technet.microsoft.com/en-us/library/dd347622.aspx
   clear-variable -name results4Query;

}

Occasionally, what looked to be a simple list transversal will fail and emit
something to the effect that the Server Name is null.

Added, Exception Handling.

And, after a bit of the customary googling. I ended up with the cause of the problem.

When declaring HashTables, use a semi-colon to separate the list elements.

# When declaring Hash tables, use a semi-colon to separate the 
# list elements.

$instanceNameList = @(
		         "DBEngineering"; "DBFinance"; "DBSales"
		       ; "DBAgriculture"; "DBPublicRelations"
                       ; "DBAccounting"
	             );

What a waste of time.

Should have out-sourced the server-list declaration to a text-file in the first place.

Thought, I could save some time embedding it in the source-code.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s