Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
 Posted - 2012-10-29 : 02:59:22
|
| Hi experts,Can we have the powershell script to find out the below.Given a host name of a windows box, an example task is to find out what version of SQL Server is running on it and on which port the DB is running.Thanks,Gangadhara MSSQL Developer and DBA |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-29 : 06:57:46
|
| [code]Invoke-sqlcmd -ServerInstance "YourServerNameHere" -Query "select value_data AS Port from sys.dm_server_registry WHERE value_name = 'TcpPort'" [/code]This works only on SQL 2008 R2 or later though. I don't know where or if registry values are accessible in prior versions of SQL Server from T-SQL.You can probably query the registry of the remote machine. The path you want to look under is HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL<<YOURVERSIONNUMBER>>.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\ |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-10-30 : 11:34:25
|
| @sunitabeck - Does the sys.dm_server_registry work on SQL Server 2008 R2 or just 2012?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-30 : 13:14:02
|
| Jack, I have it in my 2008 R2 (SP1) - 10.50.2500.0 Developer edition. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-10-31 : 12:23:03
|
| sunitabeck , Interesting - I don't have it on - Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) . Looking at the Release Notes - it was introduced at Service Pack 1Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
powershell script