Trying To Get The Right Registry Key Through Sql Script To Change Listen Port
Mar 8, 2007
Hi,
I want the change the SQL listen port of a named 2005 sql instance.
Is there a way I can run an sql query against the named instance which
gives me back which registry setting I need to use.
so MSSQL.1 MSSQL.2 or MSSQL.3
Or is there another way I can set the listenport through script or
vbscript for only a specific named instance so if there are other sql
server instances running they don't get affected.
Been having SQL Server 2000 running for some time now, but recently it stopped listening on TCP port 1433, the log reports its listening on shared memory, Named pipes and Rpc, but no sign of 127.0.0.1 port 1433 or any errors to say why it won't listen.
I've done a netstat -na and nothing else is listening on that port, tried restarting using the enterprise manager, gonna try restarting the entire Server when everyone has gone home, but I'm pretty sure its been restarted recently.
All the other archive logs going back a few days show its not listening.
Yes, I have used the Server network utility to make sure TCP/IP is enabled and set to 1433, even added a comma and 1434 to see if it will listen on multiple ports, no go.
I want to know how to check whether SQL Server 2000 can listen to port 1433 or not. If I run netstat -a, am I suppose to see port 1433 regardless of what service pack I have applied to SQL Server 2000?
Setting: sql 2000 default instance installed. Installed a sql 2005 named instance.
I want to configure the sql 2005 instance to listen on a partcular TCP port. (I followed the instructions in KB article 177440.)
"SQL Config Manager" > Protocols for "Instance" > Right-click TCP/IP > Properties, I found about 5 IP addresses and an "IPALL" address (pretty good for a home machine with one NIC!).
Per the article, I removed the 0 from TCP Dynamic Ports and put the desired port number ("4567") in the port value, restarted the sql instance, and found this using "xp_readerrorlog" this:
...... ..... Server is listening on [ 'any' <ipv6> 4567]. Server is listening on [ 'any' <ipv4> 4567]. ..... ..... Server is listening on [ ::1 <ipv6> 49277]. Server is listening on [ 127.0.0.1 <ipv4> 49277].
It looks like my instance is now listening on the desired port (success) but also, still, using a dynamic port. Did I do something wrong or can the dynamic port simply not be eliminated?
I have installed MS server sql 7.0 and 2000 both on my PC. The default port i know is 1433. If sql 7.0 is already installed. Which port does sql 2000 listen on.
I installed a new instance and changed the default tcp/ip port and configured the applications. based on request i have changed the port to default 1433. now the problem is, the instance is not coming up. Then I restored the port now its working fine. But the application user wants 1433. could you please tell me why the instances is not comming up if the port is 1433? Thanks in advance
We have default instance "MSSQLSERVER" with Port number 1433. We Installed another Instance "MGMT" on the same windows server, it came as same Port number "1433".
We decided to Change the Port number for Instance "MGMT".
1. We changed the port number for "MGMT" to like "1234"
2. After the change we did telnet to "MGMT" Instance with "1433", it worked.
3. Then we changed the Port number for Instance "MSSQLSERVER" also to like "4321".
4. After change of these setting and followed with rules framed in link as said: http://www.databasejournal.com/features/mssql/article.php/3689846
5. Now we did telnet with changed port number, it works fine.
6. We can not do telnet with Port number 1433 for both Instances "MSSQLSERVER" and "MGMT".. This is fine, what we want, and what we intent to do...
Question to MVP OR SQL Gurus:
A) If you want to change port number to Second Instance, shall we need to change for Default Instance?
B) How the port numbers will be configured? please advice.
Hi im trying to install Sql Server 2005 but it says i need to Increment the Counter Registry Key for Setup in SQL Server 2005 before doing so. Im following the guide at http://msdn2.microsoft.com/en-us/library/ms143215.aspx but in my registry the folder HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindows NTCurrentVersionPerflibdoesnt contain any keys"Last Counter"=dword:00000ed4 (5276)"LastHelp"=dword:00000ed5 (5277) or any key for that matter.How can i fix this?
Hi there, I want to change the default sql-server port number 1433 to a new one. Can i do that? If yes, how? Also, i have noticed that there is no port number option in the sql server 2005 management studio login window. So, what if the server has changed its port number? Any suggestions would be appreciated. thanks
How can i define a different TCP/IP port number , other than the default 1433; to replicate the database over a firewall on the source and the destination servers ? My source servers are SQL SERVER 7.0 with SP3 on Windows NT Server 4.0 and the destination servers are SQL 2000 with SP2 on Windows 2000 .
Where do i have to make changes in SQL server and Windows NT ? And further how secured it is to use this way for security ?
hai all, I need to change the default port number of the SQL server from 1433 to some other port number. i know that it can be done thru SERVER NETWORK UTILITY. Can some one confirm that. It will be helpful. Also i want to make sure that port to given is free port. how can i find that particular port is free.
Version: SQL SERVER 2012 enterprise edition and SP2.
Due to one of the security reason, I have changed default port 1433 to another port number in configuration manger tools->protocols for MSSQLSERVER->TCP/IP --> IP ALL section mention another port number.
After restarted service, SQL SSMS able to connect server itself and new port number also LISTENING.
But SQL client SSMS not connected to one of the PC side after changing default port.
Hi, I hope someone can help me! I changed the TCP port that the default instance of SQL 2005 x64 SP1 is listening on and now SQL server won't start as the port is being used - no problem I say to myself, I'll just change it again, however I can't!
My problem is that I can not change the TCP Port back again using Configuration Manager - no matter what values I put in, after a restart of the instance it comes up with this in the IP Addresses tab for TCP/IP Properties and SQL Server won't start:
IP1 Active Yes Enabled No IP Address aaa.bbb.ccc.111 TCP Dynamic Ports TCP Port 0
IP2 Active Yes Enabled No IP Address aaa.bbb.ccc.222 TCP Dynamic Ports TCP Port 0
IP3 Active Yes Enabled No IP Address aaa.bbb.ccc.333 TCP Dynamic Ports TCP Port 0
IP4 Active Yes Enabled No IP Address 10.1.1.1 TCP Dynamic Ports TCP Port 0
IP5 Active Yes Enabled No IP Address 127.0.0.1 TCP Dynamic Ports TCP Port 0
IPALL TCP Dynamic Ports 2020 TCP Port 2020
Interestingly, the ip address that the server is listening on aaa.bbb.ccc.444 is not reported in the list of IP addresses in the configuration manager for this instance, yet it is when I view the available IP addresses for the other two instances - is this significant?
I have tried failing to the other node, changing it there, changing it at the console and also while logged onto the the virtual machine remote desktop, rebooting both nodes, etc, etc. There are two other instances on this cluster that are still working fine but I hvae not attempted to change the TCP ports they listen on.
I used the same method to change this default instance as I did for a named instance on a similarly configured cluster and it worked fine. How to fix this?
Regards, Mike
2007-02-27 12:03:17.69 Server Server is listening on [ aaa.bbb.ccc.444<ipv4> 2020]. 2007-02-27 12:03:17.71 spid9s Starting up database 'tempdb'. 2007-02-27 12:03:17.72 Server Error: 26023, Severity: 16, State: 1. 2007-02-27 12:03:17.72 Server Server TCP provider failed to listen on [ aaa.bbb.ccc.444<ipv4> 2020]. Tcp port is already in use. 2007-02-27 12:03:17.75 Server Error: 17182, Severity: 16, State: 1. 2007-02-27 12:03:17.75 Server TDSSNIClient initialization failed with error 0x2740, status code 0xa. 2007-02-27 12:03:17.75 Server Error: 17182, Severity: 16, State: 1. 2007-02-27 12:03:17.75 Server TDSSNIClient initialization failed with error 0x2740, status code 0x1. 2007-02-27 12:03:17.75 Server Error: 17826, Severity: 18, State: 3. 2007-02-27 12:03:17.75 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log. 2007-02-27 12:03:17.77 Server Error: 17120, Severity: 16, State: 1. 2007-02-27 12:03:17.77 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
I use SQL Server 2000 as my database. I use WinXP and my computer does not connect to any network. But I can't telnet localhost 1433. When I open the file ERRORLOG, it displays: "2005-01-06 22:36:22.43 server SQL server listening on Shared Memory, Named Pipes." I checked the enable protocols, they are Named Pipes and TCP/IP and default port is 1433. But I can't telnet to it. I can't understand why. Anybody can help me? Thanks.
Hi all!I just tried to enable TCP/IP on SQL Server 2000 (trial, SP3a). TheServer Network Utility shows it's already turned on, but afterrestarting the server doesn't listen to any TCP port, and in theeventlog it reports "SuperSocket info: gethostbyname(MSAFD Tcpip[TCP/IP]) : Error 11004." and "SuperSocket info: (SpnRegister) : Error2102.". I googled but found nothing helpful. Does anyone know what'swrong??The OS is Windows 2003 Server Enterprise Edition.
We have a customer who is trying to get sql server 2005 express edition installed on their pc to continue using our application but the sql server instance is not starting.
If we have him go into services and try and start the service for the named instance of sql server express it gives us a "The sql server [sqlexpress] service on local computer started then stopped" error message.
We looked into the event log and the error message we saw there was "server failed to listen on [ 'any' <ipv4> 0 ]"
Anyone have any idea? I can provide further details if necessary, but they are hard to obtain since I do not have access to the computer, and our customer is not very computer savvy.
One detail i can add is they have office small business accounting installed, and it has created an instance of sql server 2000. I was thinking there may be a conflict here but i was unable to reproduce anything on my machines with msde and sql server 2005 express.
The computer is windows xp home edition, and has service pack 2 installed.
I am getting the following error when creating a endpoint (that will be used for allwayson) The Database Mirroring endpoint cannot listen for connections due to the following error: '10049(The requested address is not valid in its context.)'.
My physical network card is configured with:
10.9.255.170
and iam creating my endpoint with 10.9.255.82
The creation of endpoint is being done with;
CREATE ENDPOINT [Hadr_endpoint] AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.9.255.82) FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES) GO IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0 BEGIN ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED END GO use [master] GO GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [account] GO
How to restore registry for sql server. I read once in discussion board when the sql agent not starting he said that after restoring registry its fine. So how to restore and how to backup?
I need to make a system dsn connection on 50 pc.It's a system dsn connection with sql usersI have though about to make a registry key, but I don't know where user andpassword are in the registry.My question is where in the registry can I find these keys?CD
I would like to know what is the registry key where is written that SQL Server 2000 is installed and the registry key where is the path to the data folder (master database) on Windows Server 2003. thanks Irina
I am installing my application thru Installshield Professional 5. Howto know whether MS SQL Server is installed or not on the machine onwhich i am installing my application. What is the registry key thruwhich we can find this.
Hi, I have an SSIS Package which reads the properties values from Registry. As per the constraint of SSIS I have created the keys under HKCU in the registry and things work fine from the designer.
But, when I schedule the SSIS Package to run under SQL Agent (which is running under Local System) the values of configuration do not seem to be picked up from Registry.
How I tested was: I am picking up the server name for the Connection Manager from Registry and I set that value to an invalid servername. But, the job seems to be executing fine. Using DTEXEC it fails as expected since, this is running under my credentials.
Also, SYSTEM has full control on the new regsitry keys. Any information on this?
I have the following problem... In my table I store calling registries...and when a call is transfered to another branch the last brach have to receive all the time... Só I have the following table
Code Snippet CREATE TABLE [BILHETES_PROCESSAR] ( [COD_CLIENT] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL , [DATE_TIME] [datetime] NOT NULL , [NRTELEFONE] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL , [BRANCH] [varchar] (255) COLLATE Latin1_General_CI_AS NULL , [TIPO] [varchar] (255) COLLATE Latin1_General_CI_AS NULL , [TIPO_ORIGINAL] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,-- Tells if is a originate call "2" or received a tranfer "6" [TRONCO] [varchar] (255) COLLATE Latin1_General_CI_AS NULL , -- The port of the router through the call have been made [TRANSF] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,--The branch that is going to receive this call [ORIGEM_TRANSF] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,--The branch that sent the call [VALOR_TEMPO] [money] NULL , [ID_TRANSF] [uniqueidentifier] NULL ,--ID for the branch that originated the call [ID_REGISTRO] [uniqueidentifier] NOT NULL --ID of the registry ) ON [PRIMARY] GO
UPDATE P SET P.TRANSF=P2.BRANCH, P.ID_TRANSF= CASE WHEN P.TIPO_ORIGINAL = '2' THEN P.ID_REGISTRO WHEN P2.ID_TRANSF IS NOT NULL THEN P2.ID_TRANSF ELSE '1C4CCB4E-9229-4A6C-A6A4-B881A01EEACF' END FROM BILHETES_PROCESSAR P INNER JOIN BILHETES_PROCESSAR P2 ON P.COD_CLIENTE=P2.COD_CLIENTE AND P.NRTELEFONE=P2.NRTELEFONE AND P.TRONCO=P2.TRONCO WHERE P2.DATE_TIME >= P.DATE_TIME AND ( (P.TIPO_ORIGINAL IN (1, 5, 7) AND P2.TIPO_ORIGINAL IN (5, 7)) OR (P.TIPO_ORIGINAL IN (2, 4, 6, 8) AND P2.TIPO_ORIGINAL IN (4, 6, 8)) ) AND P2.BRANCH <> P.BRANCH AND P.TRANSF IS NULL
UPDATE P2 SET P2.ORIGEM_TRANSF=P.BRANCH, P2.ID_TRANSF= CASE WHEN P.ID_TRANSF IS NOT NULL THEN P.ID_TRANSF ELSE P.ID_REGISTRO END FROM BILHETES_PROCESSAR P INNER JOIN BILHETES_PROCESSAR P2 ON P.COD_CLIENTE=P2.COD_CLIENTE AND P.NRTELEFONE=P2.NRTELEFONE AND P.TRONCO=P2.TRONCO WHERE P2.DATE_TIME >= P.DATE_TIME AND ( (P.TIPO_ORIGINAL IN (1, 5, 7) AND P2.TIPO_ORIGINAL IN (5, 7)) OR (P.TIPO_ORIGINAL IN (2, 4, 6, 8) AND P2.TIPO_ORIGINAL IN (4, 6, 8)) ) AND P2.BRANCH <> P.BRANCH AND P2.ORIGEM_TRANSF IS NULL
UPDATE P2 SET P2.ID_TRANSF=P.ID_TRANSF FROM BILHETES_PROCESSAR P INNER JOIN BILHETES_PROCESSAR P2 ON P.COD_CLIENTE=P2.COD_CLIENTE AND P.NRTELEFONE=P2.NRTELEFONE AND P.TRONCO=P2.TRONCO WHERE P2.DATE_TIME >= P.DATE_TIME AND ( (P.TIPO_ORIGINAL IN (5, 7) AND P2.TIPO_ORIGINAL IN (5, 7)) OR (P.TIPO_ORIGINAL IN (4, 6, 8) AND P2.TIPO_ORIGINAL IN (4, 6, 8)) ) AND P2.BRANCH <> P.BRANCH AND P2.ID_TRANSF='1C4CCB4E-9229-4A6C-A6A4-B881A01EEACF'
This query has only one problem if the user has made a 4º transfer th2 two last registry will have wrong "transf" and "origem_transf" values... but this is not my big problem...
The big problem is that I need to sum all it "valor_tempo" put it on the last registry... that is the one with the code "24B3073B-F45F-4E87-9796-55F37847473D" and then set the "valor_tempo" to 0 to the other registry...
Quite new to SSIS am currently trying to set up a registry configuration which will hold a connection string which will enable the main SQL configurations for a package to load. I wish to put the registry entry under the HKEY_LOCAL_MACHINE directory or more precisely in a key such as below:
Have set this key up with a default value of the connection string and an associated registry configuration with the above as the value in the regsitry entry box. This configuration is listed as the first configurartion for the package to ensure it gets applied before the SQL configuration loads.
However on load or execute I get a warning of the type
A configuration entry specifies a registry key that is not available. Check the registry to ensure that the key is there.
Are there any known problems with regsitry configurations or am I just missing something obvious here ?
