Post reply

Query to get Hostname,instancename,port number

  • March 21, 2011 at 10:38 am

    #236525

    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

    #1301582

    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 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply