This topic contains 4 replies, has 4 voices, and was last updated by
March 19, 2014 at 2:00 pm #13929ParticipantPoints: 0Rank: Member
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
How do I get JUST the 17443 into my variable..
Thank you for any help!
March 19, 2014 at 2:12 pm #13930KeymasterPoints: 1Rank: Member
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.
March 19, 2014 at 10:34 pm #13932
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'
March 20, 2014 at 7:37 am #13939ParticipantPoints: 0Rank: Member
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
March 20, 2014 at 8:01 am #13945ParticipantPoints: 0Rank: Member
Thank you guys so much! the -expand did exactly what I needed! And I am taking the "distinct" advice as well.
The topic ‘invoke-sqlcmd output to variable’ is closed to new replies.