PowerShell – HashTable – Hard Coded 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.

BTW, the script comes from here :-

Docs > Previous Versions > SQL > SQL Server 2008 > Scripting (Database Engine) > SQL Server PowerShell Overview > Running SQL Server PowerShell

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

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


	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
   # technet.microsoft.com/en-us/library/dd347622.aspx
   clear-variable -name results4Query;


Occasionally, what looked to be a simple list traversal 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 )

Google photo

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

Connecting to %s