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


function prepareSQLServerManagementPowerShell
	# Adding the SQL Server Snap-ins to Windows PowerShell


	if (Get-ChildItem $sqlpsreg 
                -ErrorAction "SilentlyContinue")
   "SQL Server Provider for Windows PowerShell is not installed."
$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
	cd $sqlpsPath
	Add-PSSnapin SqlServerCmdletSnapin100
	Add-PSSnapin SqlServerProviderSnapin100
	Update-TypeData -PrependPath SQLProvider.Types.ps1xml 
	update-FormatData -prependpath SQLProvider.Format.ps1xml 



$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;


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

	#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
   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: Logo

You are commenting using your 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