Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL Scripts & Samples

Posted Oct 2, 2002

Script to Get Dynamic Port on Which the SQL Server Instance Listens

By DatabaseJournal.com Staff


As every SQL Server DBA might be aware, the default port for SQL Server on which it listens is 1433. But this holds only for the default instance. In a multi-instance environment, the port allocation is dynamic and is potentially different every time SQL Server is restarted. This script can be used to find the dynamic port of an instance, by scanning the registry.

This script gets the current instance name on the fly and needs NO customization. The script supports both SQL Server 7.x and SQL Server 2000.

Author: Vijay Anisetti


-- This script will get the listening port of the 
-- SQL Server, useful for multiple instance servers
-- Vijay Anisetti

CREATE TABLE #GetPort
(
token varchar(100),
value varchar(20))
go

DECLARE @inst varchar(200)
DECLARE @inst1 varchar(100)
IF(charindex('\',@@servername) > 0) 
BEGIN
  SELECT @inst = substring(@@servername,charindex('\',@@servername),50)
  SELECT @inst = 'SOFTWARE\Microsoft\Microsoft SQL
Server'+@inst+'\MSSQLServer\SuperSocketNetLib\Tcp'
  SELECT @inst1 = 'TcpDynamicPorts'
END
 ELSE
BEGIN
  SELECT @inst =
'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\Tcp'
  SELECT @inst1 = 'DefaultPort'
END

INSERT #GetPort
    EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', @inst, @inst1 

SELECT value FROM #GetPort
DROP TABLE #GetPort


Back to Database Journal Home



SQL Scripts & Samples Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM