New-Object Confusion.

  • 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.

  • 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!!!

  • It all works fine if used properly...

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

  • 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!!!

  • 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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply