October 14, 2008 at 10:42 pm
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
October 15, 2008 at 10:28 am
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
October 15, 2008 at 11:04 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply