Richard Siddaway's Blog

The recording, slides and demo scripts from tonight’s PowerShell and SQL Server session are available

The recording, slides and demo scripts from tonight’s PowerShell and SQL Server session are available

I tested your code and it works fine. Although generally I'll omit the instance name if I provide the port number. The instance name isn't need if the port number is provided, but to be clear it works either way. I also generally use the fully qualified computername (hostname.domain.com) in order to leverage DNS to re-point SQL Servers in a DR scenario. So the connection string will would be

$cons = "server=W08R2SQL12.Aceme.com,port_number;Trusted_Connection=true;multipleactiveresultsets=false"

Another way of specifying SQL Server port number is to use the SQLConnection object:

try {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}

catch {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"}

try {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop; $smoVersion = 10}

catch {add-type -AssemblyName "Microsoft.SqlServer.Smo"; $smoVersion = 9}

$con = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") "Z109943W.aceme.com,1862"

$server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $con

Lastly since a connection isn't made to the SQL Server until properties are retrieved, I prefer to explicitly call $con.Connect() or in your example $cn.Open() before creating my SMO server object.