In SSMS if you are connecting to SQL Server instance set to specific port other than 1433 you simply put ",port number" after the instance name. In the same regard if I am using System.Data.SqlClient.SqlConnection
and want to populate a data set with System.Data.DataSet
, I would do the same when specifying the Data Source portion of the connection string to pass into that object.
Is there not a way to do this using Microsoft.SqlServer.Management.Smo.Server
class?
I have tried variations with no luck like:
$cnString = "Data Source=Server\instance,port;Initial Catalog=master;Trusted_Connection=true;"
$cn = new-object system.data.sqlclient.sqlconnection($cnstring)
$cnSql = New-Object Microsoft.sqlserver.management.common.serverconnection($cn)
$s = New-Object Microsoft.sqlserver.management.smo.server($cnSql)
$s | Select Name, Version
Or just trying:
$s = New-Object Microsoft.sqlserver.management.smo.server "Server\Instance,port"
$s | Select Name, Version
Best Answer
One thing you could do is just set the ServerConnection portion of the Server object to an explicit connection string, specifying the port name.
You could either do this by accessing the Server.ConnectionContext property:
Or by passing the
ServerConnection
object in when you instantiate theServer
object:EDIT: I just re-read your question, and it seems like one of your attempts is basically identical to my second one up above. I took your code and all I changed was the
data source
portion of your connection string, and it worked just fine for me: