Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script for changing the port number & disable Named Pipes protocol Expand / Collapse
Author
Message
Posted Tuesday, October 14, 2008 10:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 3, 2008 12:42 AM
Points: 3, Visits: 31
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
Post #585944
Posted Wednesday, October 15, 2008 10:28 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
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
Post #586363
Posted Wednesday, October 15, 2008 11:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 12,903, Visits: 32,146
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #586380
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse