How do I find which port SQL2000 Named Instance runs on?

Question

Post reply

How do I find which port SQL2000 Named Instance runs on?

Melissa Kadiri

Default port

Points: 1435
More actions
January 6, 2004 at 1:05 pm

#162937

I installed the instance *without* doing a custom install, and now I don't know the port. It's not 1433, 1434 or 1207. Auto-discover in the ODBC works, but I still don't know the port. Thanks!
Regards, Melissa

Viewing 6 posts - 1 through 5 (of 5 total)

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

me_balaji SSC Eights! Points: 923 More actions · Answer 1

Goto SQL Log and find out which port the SQL instance is listening to.

ThomasH SSCertifiable Points: 7437 More actions · Answer 2

You can set and get the info in SQL's Server Network Utility.

Some SQL installs it's in the Start-Programs-SQL Server section,

otherwise its "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\SVRNETCN.exe".

Look at TCP/IP Properties on General tab.

There is a instance dropdown.

Once you understand the BITs, all the pieces come together

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

Don't know where I got it from, but I 've modified it a bit to be suitable for virtual servers.

-- This script will get the listening port of the

-- SQL Server, useful for multiple instance servers

-- Vijay Anisetti

set nocount on

CREATE TABLE #GetPort

(

token varchar(100),

value varchar(20))

go

DECLARE @inst varchar(200)

DECLARE @inst1 varchar(100)

--Determine registry path and key

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'

SELECT @inst1 = 'TcpPort'

END

ELSE

BEGIN

if SUBSTRING(@@VERSION,23,1) = '7'

begin

SELECT @inst = 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\VIA'

SELECT @inst1 = 'DefaultServerPort'

end

else

begin

SELECT @inst = 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\Tcp'

SELECT @inst1 = 'DefaultPort'

end

END

print @inst + '\\\\////'+ @inst1

INSERT #GetPort

EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', @inst, @inst1

SELECT substring(@@servername,1,25) as ServerName, value as port FROM #GetPort

DROP TABLE #GetPort

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Melissa Kadiri Default port Points: 1435 More actions · Answer 4

Wow! Thanks to everyone for posting responses. These are great answers.

Regards, Melissa

P.M. Visser SSC-Addicted Points: 430 More actions · Answer 5

You could use the "sqlping.exe" utility. You can ping a host and it returns all instances with versionnumbers and tcp portnumber.