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!
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.
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’
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
Thank you guys so much! the -expand did exactly what I needed! And I am taking the “distinct” advice as well.