March 21, 2011 at 10:38 am
Hi Can any one shade light on getting the hostname,Instancename and portnumber for each instance for the following query? Thanks in advance
DECLARE @GetInstances TABLE
(Value VARCHAR(30),
InstanceNames VARCHAR(20),
Data VARCHAR(30))
INSERT INTO @GetInstances
EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name = 'InstalledInstances'
SELECT CAST(serverproperty('ComputerNamePhysicalNetBIOS')AS VARCHAR(10)) AS ServerName,
CASE
WHEN InstanceNames = 'MSSQLSERVER'
THEN CAST(serverproperty('ComputerNamePhysicalNetBIOS') AS VARCHAR(10))
ELSE (CAST(serverproperty('ComputerNamePhysicalNetBIOS') AS VARCHAR(10))+'\'+ InstanceNames)
END AS InstanceName from @GetInstances
March 21, 2011 at 2:25 pm
I generally use info from sys.dm_exec_connections to get that info:
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ServerName,
@@SERVERNAME AS FullInstanceName, @@SERVICENAME AS InstanceName,
local_net_address AS InstanceIPAddress, local_tcp_port AS InstancePort
FROM sys.dm_exec_connections WHERE session_id = @@spid
This query displays the IP address and port to which I connected in order to run the query.
Because the info is exposed as a result of an inbound TCP connection, do not run the query directly on the server, which would use Shared Memory to connect, instead of TCP/IP.
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy