New-Object Confusion.

• Duran

  SSChampion

  Points: 13259

  More actions

  March 22, 2016 at 12:17 am

  #325999

  Hello,

  I am really confused about something.

  $DBServer = "ServerName"

  $DBServerport = "1433"

  If I type in the following...

  $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList ("$DBServer"+","+"$DBServerPort")

  $Server

  Returns a value of...

  InstanceName

  ----------------

  ...just that, nothing else. $Server is used anywhere else in the script. Why does it not return 'ServerName, 1433'?

  It will allow me to list databases as I get prompted for databases after the period...

  $server.databases

  ...and get a list of databases. I'm just confused by the first bit.

  On the other hand I can type...

  $server = ("$SSRSdatabaseservername"+","+"$SSRSDatabaseServerTcpPort")

  ...and $server returns a value of

  servername, 1433

  ...but as its not an object, I cannot search for databases, as I am not prompted for 'databases' , I just get prompted for 'contains' etc.

  $Server.contains.

  ... I'd really like to get the desired result ( servername, 1433 ) as I would like to use the variable elsewhere. It does in fact work, but I am not happy with it. I think it could be cleaner.

  Also, how does one exit out of a PS SQL Server drive, and back to the PS C:\Windows\System32> prompt? I cant seem to find an answer to that anywhere!

  I have one other issue.

  If the SQL browser is stopped on the target machine, how can I get this to work?...

  Set-Location SQLSERVER:\SQL\$DBSERVER\$DBInstance

  ...ideally I'd use

  Set-Location SQLSERVER:\SQL\$DBSERVER\$DBServerPort

  ...but I understand that ports simply cannot be used for this. Which surprises me.

  Thank you for reading.

  Regards,

  D.

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  March 22, 2016 at 1:57 am

  #1866678

  Either I am a little confused. Or you are. Or, perhaps, we both are.

  The following provides us with a SQL Server Management object that reflects a server:

  $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList ("$DBServer"+","+"$DBServerPort")

  Whereas the following just gives us a string (hence Contains etc.):

  $server = ("$DBServer"+","+"$DBServerPort")

  Or course you could have the following:

  $serverNameAndPort = ("$DBServer"+","+"$DBServerPort")

  $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList ($serverNameAndPort)

  Gaz

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

• Shawn Melton

  SSC-Insane

  Points: 24685

  More actions

  March 22, 2016 at 8:25 pm

  #1866981

  It all works fine if used properly...

  

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

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  March 23, 2016 at 1:36 am

  #1867002

  Shawn is spot on (again). In my haste I took the question at face value.

  Gaz

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

• Duran

  SSChampion

  Points: 13259

  More actions

  March 23, 2016 at 7:13 pm

  #1867285

  Thanks Shawn, those are two great tips! In the end, mainly because it suites my environment, I wrote a FN for the start and end that started, then stopped the SQL Browser service on the target machine. But I may rewrite with your tips just for the sake of learning.

  I used this resource to help me..

  https://msdn.microsoft.com/en-us/library/hh403394.aspx

  Stop and start SQL services with powershell.

  Regards,

  D.