Script for changing the port number & disable Named Pipes protocol

Question

Script for changing the port number & disable Named Pipes protocol

vikas_chawla

SSC Enthusiast

Points: 121
More actions
October 14, 2008 at 10:42 pm

#192229

Hi All,
(1) Is there any command or script to disable the named pipes protocol without making it disable from configuration manager?
(2) Is there any script to change the default port number of SQL Server 2005. Though i can change it from configuration manager but i want a script.
If anyone knows please help me. Thanks in advance

Viewing 3 posts - 1 through 2 (of 2 total)

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

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

You can make use of xp_regread and xp_regwrite stored procedures to do this but I don't think its recommended. These procs. will directly make update in the registry.

You need to post this in SQL 2005 forum.

MJ

Lowell SSC Guru Points: 323504 More actions · Answer 2

i've pasted a script below that shows how to get the listening port by reading the registry, and as the previous poster said, you could write to that spot as well, but even if you changed it, in a script, you'd need to start and stop the server in order for the changes to be reloaded into the running values of the server.

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

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!