Ggtcf

I'm running a lot of invoke-sqlcmd commands, all with a different server name, different port, and a different instance.

From what I've read a hash table is the way to go

1. Can a hash table be a separate file?


2. How do I create a table that includes server name, port number, and instance?


3. How do I incorporate that into the script?

A PS Custom object is well-suited for containing your data. Consider

$o = [PSCustomObject]@
 'Name' = 'dev'
 'Server' = 'Server1'
 'Instance' = 'dev'
 'Port' = '50000'

Now the $o object contains NoteProperty members that contain data. Accessing is simple enough:

$o.server+$o.instance+','+$o.port
Server1dev,50000

Hashtables in Powershell are created with built-in syntax: $myHashTable = @.

A quick demo how to work with hash tables is in order. Let's populate a list of aforementioned custom objects first. Like so,

# Empty array
$l = @()
# Add some servers
$l += [PSCustomObject]@
 'Name' = 'dev'
 'Server' = 'Server1'
 'Instance' = 'dev'
 'Port' = '50000'

$l += [PSCustomObject]@
 'Name' = 'test'
 'Server' = 'Server1'
 'Instance' = 'test'
 'Port' = '50001'

$l += [PSCustomObject]@
 'Name' = 'qa'
 'Server' = 'Server1'
 'Instance' = 'qa'
 'Port' = '50002'

$l += [PSCustomObject]@
 'Name' = 'prod'
 'Server' = 'Server2'
 'Instance' = 'prod'
 'Port' = '50000'

Now there's a list of server objects, but it's hard to access a specific one. Server's index in the list must be known, or the list otherwise searched.

Consider a hash table: its contents can be retrieved based on a key. Let's use Name property as the key and add the servers into a hash table:

# Empty hashtable
$ht = @
# Iterate server list and add each PSObject into the hasthtable
$l | % $ht.add($_.name, $_ )
# Print results
$ht
Name Value
---- -----
qa @Name=qa; Server=Server1; Instance=qa; Port=50002
dev @Name=dev; Server=Server1; Instance=dev; Port=50000
prod @Name=prod; Server=Server2; Instance=prod; Port=50000
test @Name=test; Server=Server1; Instance=test; Port=50001

#Access the prod server object
$ht.prod

Name Server Instance Port
---- ------ -------- ----
prod Server2 prod 50000

# Get production's port
$ht.prod.port
50000

# Generate a connection string to prod
"Server=0,1" -f $($ht.prod.server+$ht.prod.instance), $ht.prod.port
Server=Server2prod,50000

As for how to use file-based hashtable, look up Export-Clixml and Import-Clixml.