Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

2 Answers 2

up vote 3 down vote accepted

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:

$ConnectionString = "data source = yourinstancename,10055; initial catalog = master; trusted_connection = true;"

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server

$SqlServer.ConnectionContext.ConnectionString = $ConnectionString

$SqlServer.Databases |
    Select-Object Name

Or by passing the ServerConnection object in when you instantiate the Server object:

$ConnectionString = "data source = yourinstancename,10055; initial catalog = master; trusted_connection = true;"

$ServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$ServerConnection.ConnectionString = $ConnectionString

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerConnection)

$SqlServer.Databases |
    Select-Object Name

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:

$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
share|improve this answer
    
I just tried it again and what I had did work. Not to sure what I was doing wrong. However your first example looks much cleaner and more readable to me. I had tried that but couldn't get it to work and see why with your example. –  Shawn Melton Dec 8 '13 at 4:39

You can specify TCP/IP as the network protocol using the 'Network Library=DBMSSOCN' connection setting. Then the indicated SQL Server port is used:

$cnString = "Data Source=Server\instance,port;Network Library=DBMSSOCN;Initial Catalog=master;Trusted_Connection=true;"

Here's a link with additional information: SqlConnection -> Connect via an IP address

This assumes that you have enabled TCP/IP connections to SQL Server: Enable TCP/IP Network Protocol for SQL Server

share|improve this answer
    
That does not work when I try it. –  Shawn Melton Dec 7 '13 at 3:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.