invoke-sqlcmd output to variable

This topic contains 4 replies, has 4 voices, and was last updated by Profile photo of Jason Spatz Jason Spatz 3 years, 1 month ago.

  • Author
    Posts
  • #13929
    Profile photo of Jason Spatz
    Jason Spatz
    Participant

    This is probably a newb question.. but I am trying to pull the tcpport information using

    $TCPPort = Invoke-sqlcmd -ServerInstance "MyServer\MyInstance" -Query "select value_data from sys.dm_server_registry WHERE value_name = 'TcpPort'"

    but I just want the value to be fed into my TCPPort Variable and what is coming out is

    value_data
    ____
    17443

    How do I get JUST the 17443 into my variable..

    Thank you for any help!

  • #13930
    Profile photo of Don Jones
    Don Jones
    Keymaster

    Well... start by doing this:

    Invoke-sqlcmd -ServerInstance “MyServer\MyInstance” -Query “select value_data from sys.dm_server_registry WHERE value_name = 'TcpPort'” | GM

    What do you get back? I ask because I'm not sure what Invoke-SqlCmd actually spits out. I'm GUESSING based on what you showed me that this would do it:

    $tcpport = Invoke-sqlcmd -ServerInstance “MyServer\MyInstance” -Query “select value_data from sys.dm_server_registry WHERE value_name = 'TcpPort'” | select -expand value_data

    But I'm not 100% sure. Try it.

  • #13932
    Profile photo of
    Anonymous

    Your query is also going to return multiple rows based on the number of ipv4 and ipv6 addresses that are bound to the sqlserver so add the DISTINCT to your query as it is unlikely you are running on separate ports for each address. Then you wont return multiple objects in your variable.

    select distinct value_data from sys.dm_server_registry
    where value_name = 'TcpPort'

  • #13939
    Profile photo of Richard Siddaway
    Richard Siddaway
    Moderator

    When you use Invoke-sqlcmd what you get back is a collection of System.Data.DataRow

    You need to do something like this

    $tcpport = (Invoke-sqlcmd -ServerInstance “MyServer\MyInstance" -Query "select distinct value_data from sys.dm_server_registry WHERE value_name = 'TcpPort'").value_data

    This assumes that everything is using the same TCP port. If you have multiple ports you'll need to be more selective in your query

  • #13945
    Profile photo of Jason Spatz
    Jason Spatz
    Participant

    Thank you guys so much! the -expand did exactly what I needed! And I am taking the "distinct" advice as well.

You must be logged in to reply to this topic.