Why SMO return a lot of emty properties and how to filter only ones containing values

• ebooklub

  Hall of Fame

  Points: 3905

  More actions

  January 21, 2016 at 10:12 am

  #323778

  Example

  my station has SSMS 2008R2

  # SQL server ( myserver\inst1) is SQL 2014 STD

  code executed from station

  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

  $srv = new-Object Microsoft.SqlServer.Management.Smo.Server('myserver\inst1,7889')

  $svr | fl * -- return empty values for most of properties ( example - I cut most of the properties)

  Output

  Collation :

  CollationID :

  Edition :

  ServiceAccount :

  ServiceInstanceId :

  ServiceName :

  DefaultTextMode : True

  ConnectionContext : server=''myserver\inst1'';Trusted_Connection=true;Application Name='SQL Management';multipleactiveresultsets=false

  State : Existing

  1.Why most of the properties empty ?

  2. how can filter out output result and return only properties with values

  something like

  $svr | where {$_.value -ne ''} | fl *

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  January 21, 2016 at 10:23 am

  #1853564

  1. Either there are no values or, possibly, you don't have permission to view those values.

  Gaz

  -- Stop your grinnin' and drop your linen...they're everywhere!!!

• Shawn Melton

  SSC-Insane

  Points: 24685

  More actions

  January 21, 2016 at 11:26 am

  #1853588

  $srv = new-Object Microsoft.SqlServer.Management.Smo.Server('myserver\inst1,7889')

  ConnectionContext : server=''myserver\inst1'';Trusted_Connection=true;Application Name='SQL Management';multipleactiveresultsets=false

  Properties are empty because you are not connected to the instance. You cannot pass the port number to SMO as you have done above, you can see it does not get picked up by viewing the ConnectionContext property.

  If you have to connect to a non-default port you have to use a Connection String with SMO. It will be something like this:

  $cn = "Data Source = MyServer;Initial Catalog=master;trusted_connection=true;"

  $s = new-object Microsoft.sqlserver.management.smo.server

  $s.ConnectionContext.ConnectionString = $cn

  $s | fl *

  Shawn Melton
  Twitter: @wsmelton
  Blog: wsmelton.github.com
  Github: wsmelton

• ebooklub

  Hall of Fame

  Points: 3905

  More actions

  January 21, 2016 at 2:58 pm

  #1853661

  I tested 2 scenario and can confirm (SQL profiler) that in both scenarios connections is open to SQL server

  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

  $inst ='myserver\inst1,7889'

  #test1

  $cn = "Data Source=$inst;Initial Catalog=master;trusted_connection=true;Application Name=' **TEST** '"

  $s = new-object Microsoft.sqlserver.management.smo.server

  $s.ConnectionContext.ConnectionString = $cn

  #test2

  $svr = New-Object 'Microsoft.SqlServer.Management.SMO.Server' $inst

  Compare-Object -DifferenceObject $s -ReferenceObject $svr -Property ServiceName ,name, JobServer , Urn

  But as you can see properties of name and job server are different

  ServiceName : inst1

  name : MYDBAWORKSATION001

  JobServer : [MYDBAWORKSATION001]

  Urn : Server[@Name='myserver\inst1']

  SideIndicator : =>

  ServiceName : inst1

  name : myserver\inst1,7889

  JobServer : [myserver\inst1,7889]

  Urn : Server[@Name='myserver\inst1']

  SideIndicator : <=

  Thanks for the tip with

  s.ConnectionContext.ConnectionString = $cn