Determine the port number of a SQL instance through SQL Query

This little query can be very handy sometimes.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE @INSTANCENAME AS VARCHAR(20)
DECLARE @KeyString as VARCHAR(500)
SELECT @INSTANCENAME = RIGHT(@@SERVERNAME,LEN(@@SERVERNAME)-CHARINDEX('\',@@SERVERNAME))

SET @keystring = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @INSTANCENAME + '\MSSQLServer\SuperSocketNetLib\Tcp'

DECLARE @port varchar(5)

EXEC xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@keystring,
@value_name='TcpPort',
@value=@port OUTPUT

PRINT @port + '/' + @instancename

Add to Del.cio.us RSS Feed Add to Technorati Favorites Stumble It! Digg It!
    www.sajithmr.com

Related posts:

  1. Creating a tabular view of space used by all database tables
  2. Handy SQL Script to KILL PIDs for specific databases
  3. Querying Database Role Memberships & Members on SQL 2005 (III)

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>