I have an installation of SQL Server Express 2005 installed on a Windows 2003 server. Through the forums on the board I have been able to turn on and make sure that port 1433 and port 1434 are turned on and listening. However, both ports show that they are listening on IP address 0.0.0.0. and I need for port 1433 to either listen on the server's main IP address or at the very least on 127.0.0.1. I have set them to enabled in the Configuration manager but they still don't appear to be listening on either of those IP addresses. Could some one tell me if there's another configuration change I might need to make?
We could use charindex, substring and len to cut the 4 parts of the ip and rebuild it in the right format.
Starts with SELECT CHARINDEX('.',mycolumn) ,LEFT(mycolumn, CHARINDEX('.',mycolumn) - 1) as part_four_of_ip ,CHARINDEX('.', SUBSTRING(mycolumn,CHARINDEX('.',mycolumn) - 1),len(mycolumn) as position_of_second_dot ........ and so on.....
This will cause an awful long code. How to shorten that code?
I'm usning xp_sendmail to send e-mails to customers. In the From filed of e-mail it shows the user id which runs this process. Can I mask or change this like FROM: Customer service
Hi all,I have recently changed IP addresses on my MS SQL cluster (the new IPaddresses belongs to a different network). Both physical node's IPaddresses and resource's IP addresses have been changed.Everything seems to work fine. The cluster is up and respondingnormally. The resources failover to another node when needed correctly.However, I have noticed that the time it takes for the resource tofailover to another node is much longer than in the past (before IPaddresses change). I have noticed that "Network Name" is the resourcetype that causes the delay. Could you please advise if there issomething that I overlooked and needs to be changed.updated as well, sothat groups fail over faster?Thanks,Aleu
In our lab we have 2 clustered instance of Sql server 2018R2 as follows
Virtual Server Name IP SqlVir 10.1.1.6 SqlVirDr 10.1.1.12
The Data is SqlVir is replicated manually every day to SqlVirDr.
We had to change the Virtual Server name of SqlVirDr to SqlVir so that all dot net applications accessing SqlVir could continue to access the database without changing the application string.
For that purpose I deleted the computer name SqlVir from the domain and its IP 10.1.1.6 from DNS. Then I went to the failover cluster manager of SqlVIrDr right clicked the Sql services selected the properties and changed the DNS name from SqlVirDr to SqlVir.The applications then could access the data.However when I changed the network IP address from 10.1.1.12 to 10.1.1.6 the Sql services was found to be down.
Perhaps the procedure I followed in deleting the computer name from domain and the IP from DNS was wrong.What exactly is the steps that I should follow to achieve the above objective.
I just moved our SSRS server from one machine to another. It was sending our report emails from a service account. So when my end users get the emailed report it was from a service account (reports@blah.com) .
Now that I moved the DB to another server - the reports get emailed successfully - but they are being sent from my email address!
I have checked the rsreportserver config file. I also checked in the report server smtp setup and they both show the service account. I even checked the subscription owners and accounts.
SSRS is using my account, but I can't find where it's getting it from. Where else can I look?
We have an Sql Server 2008R2 Clustered production instance by name 'ProdVir' configured in 2 nodes(Active-passive) withWIndows Server 2008 R2. We also have another clustered instance as disaster recovery by name 'VirDr' configured again in another 2 nodes of Windows Server 2008 R2. Every day morning there is mainatenace plan which backups all the database in production and another maintenace plan in the disaster recovery server 'VirDr' which restores the backups into the VirDr instance.
I would like to know that in an eventuality of a disaster in the clustered production instance of 'ProdVir'. could we rename the the instance VirDr(meant for disaster recovery) to ProdVir and also change the Ip addresses accordingly so that the application programs do not have to change the details for the datasource in the connection strings.
IF you need to change the IP Address of a SQL Server Failover Cluster instance, here is the steps:
1. Open up Failover Cluster manager. 2. Expand the MS Cluster Instance that your SQL Cluster instance resides on. 3. Expand Services and applications. 4. Select the SQL Server Service. 5. In the main window pane (Middle Screen), you should see your SQL Server Cluster name, expand the plus to the left of it. 6. Under your SQL Server Cluster name, you should now see an 'IP address' section, right click on it and go to properties. 7. In the properties pane, you can change your IP address by entering a static IP.....or assigning a DHCP enabled IP.
I am asking about a virtual IP for SQL Server, is there a way we can assign a different IP to SQL Server other than the server's(host) IP address? like the same what we do in a clustered env.
I'm working on a school project on web and mobile applications, and had decided to use MS SQL Server for my backend database.
However, after I've installed MS SQL Server 2000, I ran a port scan and I couldn't find the server running on any ports (MySQL is running on my port 3306, but I guess it doesn't matter) Can something drop some hints on how to debug the problem?
I'm running on Athlon XP 2000+ w/ Windows XP Professional SP2
I have SQL Server in NT 4.0 which has two IP addresses. How to force the SQL Server to listen to specific IP address ? We can make client to listen to specific port & IP and let me know the way to configure SQL Server to listen only to specific port that will make to ensure that SQL Server can be communicated throught that specific port.
Your answer would be highly appreciated. regards, Nana
I am running SQL Server 2000 on windows XP pro. I have configured (enabled) TCP/IP protocol and left the default port 1433, but SQL server still does does not listen for client connections.
Can anyone tell me what might stop SQL server from listening???
With the server running, here is what I see (nothing on 1433...):
2005-10-25 23:23:24.31 server Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
2005-10-25 23:23:24.31 server Copyright (C) 1988-2000 Microsoft Corporation. 2005-10-25 23:23:24.31 server All rights reserved. 2005-10-25 23:23:24.31 server Server Process ID is 3792. 2005-10-25 23:23:24.31 server Logging SQL Server messages in file 'c:MS SQL ServerMSSQLlogERRORLOG'. 2005-10-25 23:23:24.34 server SQL Server is starting at priority class 'normal'(2 CPUs detected). 2005-10-25 23:23:24.71 server SQL Server configured for thread mode processing. 2005-10-25 23:23:24.71 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks. 2005-10-25 23:23:24.74 spid3 Starting up database 'master'. 2005-10-25 23:23:24.92 server Using 'SSNETLIB.DLL' version '8.0.194'. 2005-10-25 23:23:24.92 spid5 Starting up database 'model'. 2005-10-25 23:23:24.96 spid3 Server name is 'GS-DELL'. 2005-10-25 23:23:24.96 spid3 Skipping startup of clean database id 4 2005-10-25 23:23:24.96 spid3 Skipping startup of clean database id 5 2005-10-25 23:23:24.96 spid3 Skipping startup of clean database id 6 2005-10-25 23:23:24.96 spid3 Skipping startup of clean database id 7 2005-10-25 23:23:25.10 spid5 Clearing tempdb database. 2005-10-25 23:23:25.28 server SQL server listening on Shared Memory, Named Pipes, Rpc. 2005-10-25 23:23:25.28 server SQL Server is ready for client connections 2005-10-25 23:23:25.59 spid5 Starting up database 'tempdb'. 2005-10-25 23:23:25.71 spid3 Recovery complete. 2005-10-25 23:23:40.37 spid51 Using 'xpstar.dll' version '2000.80.194' to execute extended stored procedure 'xp_MSADEnabled'. 2005-10-25 23:23:40.71 spid51 Error: 15457, Severity: 0, State: 1 2005-10-25 23:23:40.71 spid51 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.. 2005-10-25 23:23:40.89 spid51 Using 'xplog70.dll' version '2000.80.194' to execute extended stored procedure 'xp_msver'. 2005-10-25 23:23:40.93 spid51 Starting up database 'msdb'. 2005-10-25 23:24:54.20 spid51 Starting up database 'gpldb'. 2005-10-25 23:24:54.26 spid51 Analysis of database 'gpldb' (7) is 100% complete (approximately 0 more seconds) 2005-10-25 23:24:54.46 spid51 Starting up database 'Northwind'. 2005-10-25 23:24:54.60 spid51 Starting up database 'pubs'.
One of my production SQL Server 2000 systems is listening on TCP and Named Pipes, but not on Shared Memory.
This server has a lot of scheduled jobs that are internal to this box. I assume these jobs would benefit from using shared memory instead of TCP/IP, but I can't figure out why it doesn't use shared memory already and how to correct that.
Hi. I'm a SQL Server novice, so apologies if any of this sounds simple.I am running Windows XP SP2, and have just installed SQLServer 2000. Ineed another application to connect to SQLServer, and am specifying itto do so via localhost:1433, but keep getting an error whenever I trydoing so saying it cannot connect to the database. A colleague of minehas the exact same set up on his machine, and he can connect to SQLServer fine. Running 'netstat -a' at the command line on his machinereveals that the system is listening to port 1433/ms-sql-s. Runningnetstat on my machine shows that the system is not listening to1433/ms-sql-ms. I have checked in Network Config in SQLServerEnterprise Manager, and TCP/IP is set to be using 1433.To confirm this, my application can connect over the network to mycolleague's SQL Server. but he cannot connect over the network to mine.So I'm pretty sure the issue is related to this 1433/ms-sql-s problem.Does anyone know how to resolve this? Many thanks.
i setup a 2 node sql cluster but 1433 is not listening. I check sql configuration manager and entered 1433 for all ip addresses restarted services but still not 1433
I have a named sql instance on SQL 2012. The issue is that the clients is having problem connecting to it when the firewall is on, this is not consistent. The connection is fine when I disabled the firewall. When I turned the Windows firewall back on, it will work for awhile and it fails. I checked the SQL configuration manager and saw that a dynamic port is assigned to the named instance. I did put that in the firewall rule and unblock it. I also put TCP 1433 and 1434 as well as the browser service port, TCPUDP 2382 and it still didn't work.What is the best way to determine what port does the named instance are listening?
I have been using Sql server Reporting services 2000 and i never had these problems I tried firing up my Sql server Reporting services 2005 today i went through the reporting services conf manager and then got to the "database setup" stage and bingo can't get through this error below
* On my PC i have Reporting services 2000 and Reporting services 2005 installed and my
is working ok
ReportServicesConfigUI.WMIProvider.WMIProviderException: An error occurred when attempting to connect to the report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the operation.
---> System.Runtime.InteropServices.COMException (0x800706B3): The RPC server is not listening. (Exception from HRESULT: 0x800706B3) --- End of inner exception stack trace --- at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo) at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ListReportServersInDatabase(RSReportServerInfo[]& serverInfos)
Looked for some info and got a hint here at: http://blogs.msdn.com/bimusings/archive/2005/08/23/455263.aspx
But still can't get it to fox it.. Also with the Encription Keys i anytine i try to delete it BINGO again i get the error above.. These error has been given people nigtmares and it isn't new does anyone know the fix for this error.. Hope someone from Microsoft could help
Hi All I want to know that if different users have access to a database with same loginName and password then how can I get the IP address of a particular user through a query. Thanks in advance Anisha
Hello,I want to block certain mac address to access my MS-SQL server. for that what can i do. for exampleI want to block the mac address [abc]. how is it possible to block the mac address [abc]
I want to know that if different users have access to a database with same loginName and password then how can I get the IP address of a particular user through a query.
Hi All,We have an MS SQL server that is within the company firewall. We want aparticular database to be availabe to a vendor to build a website.When we had to develop a website on another vendors machine they gaveus following details.SQL Address: testdata.data.comSQL Port: 1234SQL testUsr Password: testPassword1. How do we go about doing this?*******The SQL Server has 5 other databses. I have already created a databaseuser for just that database.2. So If the vendor registers their enterprise manager with ourinformation, can I make them see only their database? Or does all theother database also get displayed. They are not able to access anyinformation on the other databases. But they can see it.Is it possible to prevent them from seeing the other databases?Tx in advance
We ran into weird/interesting issue with below details.
Version: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200):
We are using SQLCMD to run DDL script on our product database in below order. That script has below content.
step # 1 - database collation change (case -sensitive) statement as very first statement of the script step # 2 - Actual DDL SQL statements step # 3 - database collation change back to original (case insensitive)
When we execute all above 3 steps in single script using SQLCMD on our test_server#1 , it is successful but when same is being implemented on test_ server#2 , it is failing.We ensured that there is no other user accessing the db and setting on both the server are all default/basic. Separating out all 3 steps in 3 different script working fine. This is only problem when we combine them into single script and fire it using SQLCMD. If it is something related to session/transaction then we should hit same issue on our test_server#1 server as well but that is not the case.test_server#1 and test_server#2 has exact same database/data, just two different physical machine & SQL Server instance.
I'm migrating data from three (third-party, propietary) systems that my company has been using into one common system. The problem with this is that address information in these systems is about as standardized as a bag of rocks. One product simply has 6 address lines, and does not segregate the information (city, state, zip, etc...). Another one has dedicated lines, but users have taken it upon themselves to "invent" ways to enter data (international customers/vendors have data in weird fields, but no rhyme, reason, or cohesion to the madness). The third one is just as bad.
I'm looking for a script-based, SQL implemenation (stored procedures?) for standardizing the addresses into a common format. Does anyone know of anything?
Hi I want to know that is there a system sp or table or somewhere in sql server that I can get ip address or mac address of clients that connect to sql server? thanx
