Changing the name of the computer with the installed instance of MS SQL Server is not a good idea, because after the renaming, some problems may arise in the operation of SQL. To avoid these problems, you must update the system metadata to reflect the computer name change.
This can be done using a simple SQL query. This is what the query looks like for the default instance of SQL Server:
sp_dropserver ″ old_name ″;
GO
sp_addserver ″ new_name ″, local;
GO
And so for the named one:
sp_dropserver ″ old_name \\ InstanceName ″;
GO
sp_addserver ″ new_name \\ InstanceName ″, local;
GO
For example, let's take a computer with an installed instance of MS SQL Server. To view the current server name, open SQL Management Studio and execute the query:
select @@ Servername
As you can see, the server name obtained by the query (2012R2Std_Temp) does not match the computer name (SQL_Test).
Since the example uses the default instance of SQL Server, we will run the following query to change the name:
sp_dropserver ″ 2012R2Std_Temp ″;
GO
sp_addserver ″ SQL_Test ″, local;
GO
After renaming, you must restart SQL Server. This can be done from the command line with the command:
net stop mssqlserver && net start mssqlserver
When installing Microsoft SQL Server, the internal name of the SQL Server instance is created based on the current hostname. However, when you rename a computer / server, the name of the SQL server in the metadata (stored in sys.servers) is not automatically updated, as a result of which various problems may arise when working with SQL. In this tutorial, we will show you how to correctly update the metadata in SQL and change the server name without completely reinstalling MSSQL. The instruction is applicable for stand-alone (not clustered) SQL Server versions from MS SQL 2000 to 2012.
Advice... You can change only the server name; you cannot change the name of the instance (instance) of SQL Server without reinstalling.
Open the console SQL Server Management Studio and with the following query check the current hostname and SQL instance name:
As you can see:
- Server name (HostName): DESKTOP-V2OE3P0
- Fully Qualified SQL Instance Name (ServerName): DESKTOP-V2OE3P0 \\ SQLDB
Let's rename the computer name and restart it:
Rename-Computer -NewName Sql-Srv1 -Restart
Advice... The computer name can also be changed from the My Computer properties.
After rebooting, try connecting to the old SQL server name: DESKTOP-V2OE3P0 \\ SQLDB. An error will appear because the server name has changed.
Cannot connect to DESKTOP-V2OE3P0 \\ SQLDB.
A network-related or Instant-specific error occurred when establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interface, error: 26 - Error Locating Server / Instance Specified) (Microsoft SQL Server)
Let's check the hostname and instance name again:
Select @@ ServerName ServerName, Host_name () HostName
In this case, the instance name does not match the computer name and refers to the old computer name DESKTOP-V2OE3P0.
To change the name for a named SQL instance, run the following TSQL command.
Remove old name:
sp_dropserver "DESKTOP-V2OE3P0 \\ SQLDB";
Go
Let's add a new name:
sp_addserver "Sql-Srv1 \\ SQLDB", local;
GO
If you need to change the server name for the default instance of SQL Server, the command is as follows:
sp_dropserver ″ OldName ″;
GO
sp_addserver ″ NewName ″, local;
GO
Additional steps
If you get an error while executing sp_dropserver command:
Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 56
Most likely, there are remote connections to the DESKTOP-V2OE3P0 server. You can reset them with such commands. For the default instance:
sp_dropremotelogin DESKTOP-V2OE3P0;
GO
For named:
sp_dropremotelogin DESKTOP-V2OE3P0 \\ SQLDB;
GO
Let's check that the server name in the metadata has been updated:
SELECT @@ SERVERNAME AS ‘ServerName’
SELECT * FROM SYS.SERVERS
As you can see, the server name has changed. It remains to restart the SQL Server service:
net stop MSSQL $ SQLDB && net start MSSQL $ SQLDB
Important!
- This method is only suitable for renaming a stand-alone SQL server. To rename SQL Server in a Failover Cluster, use the Cluster Management console.
- Cannot change the name of a server participating in replication or mirroring
- If Reporting services are installed, they will be unavailable after renaming (https://msdn.microsoft.com/en-us/library/ms345235.aspx)
I have installed Microsoft SQL Server 2008.
When I run SQL Server Management Studio (SSMS), I get a Connect to Server login window with a blank text field for Server name. I have tried many names but I could not solve it.
How can I find / get the server name?
Step 1: Make sure SQLEXPRESS and LocalDB are installed on your system. Go to SQL SERVER Configuration Manager \u003d\u003e SQL Server Service
If nothing is listed for SQL Server services, install below components (for 64-bit OS) 1. SqlLocalDB 2. SQLEXPR_x64_ENU 3. SQLEXPRADV_x64_ENU 4. SQLEXPRWT_x64_ENU
Step 2: Start commissioning. (Dot) as the server name and click Connect [enter image description here] Re-enter. \\ SQLEXPRESS as server name and click on connection
1.You can run the following command.
EXEC xp_cmdshell "reg query" HKLM \\ Software \\ Microsoft \\ Microsoft SQL Server \\ Instance Names \\ SQL "";
GO
2. using the built-in standard report.
select instance -\u003e right click -\u003e Reports -\u003e Standard Reports -\u003e Dashbords Server
There are many ways mentioned above. But I am using a rather simple way (well, not simple like SELECT @@ SERVERNAME). When you start SQL Server Management Studio you will see below GUI prompt
There Server name is the name of your server (there may be multiple servers according to your dev environment, choose the correct one). Hope this helps :)
given the following examples
- SQL Instance Name: MSSQLSERVER
- Port: 1433
- Hostname: MyKitchenPC
- IPv4: 10.242.137.227
- DNS Suffix: dir.svc.mykitchencompany.com
here are your possible server names:
- local \\ MSSQLSERVER
- localhost, 1433 \\ MSSQLSERVER
- MyKitchenPC, 1433 \\ MSSQLSERVER
- 10.242.137.227,1433 \\ MSSQLSERVER
- MyKitchenPC.dir.svc.mykitchencompany.com, 1433 \\ MSSQLSERVER
start -\u003e CMD -\u003e (Write comand) SQLCMD -L first line is server name if server name (local) Server name: YourPcName \\ SQLEXPRESS
Run this query to get the name
As mentioned by @ Khaneddy2013, cmd SQLCMD -L not returns the server name on startup. Bcz I just installed SSMS (no local db and server were installed). After attempts installations SqlLocaLDB and SQLEXPR32_x86_ENU (32-bit OS) I was able to connect. And now the cmd window also shows the server names.
the default server name is the name of your computer, but you can use "." (Dot) instead of the local server name.
one more thing you should consider, you may have installed SQL Server Express Edition. in this case, you must enter “. \\ sqlexpress "as the server name.
my problem was when connecting to a SQL database in the add link wizard find SERVERNAME. I found it by running a query (SELECT @@ SERVERNAME) inside SQL management studio and reusl is my server name. I put this in the server name field and everything went fine.
I also had this problem the first time.
In the Connect to Server dialog box, check the default settings and click Connect. To connect, the Server name field must contain the name of the computer on which SQL Server is installed. If the Database Engine is a named instance, the Server Name field must also contain the instance name in the format: computername \\ instancename.
So, for example, I solved the problem like this: I typed the server name: Alex-PC \\ SQLEXPRESS
10
My work company has an MSSQL 2005 server. I have two questions to find out the current user log and any way to send a warning message:
The first question is whether there is T-SQL or SP to find the current username and machine name. If the user is using the SQL server name to remotely access the SQL server, is there a way to find out the name of that user window (windows login name)?
My next question is, if I can get the username or id, is there a way to send a warning message like "the SQL server is currently being cleaned up or backed up, please don't log in at this time", Probably it could be complicated. I may have to send an email to the user.
SQL Server is only available at the company. SQL server has a list of users as login users: Windows users, SQL users and sa.
3 answers
Sorting:
Activity
30
SELECT SUSER_SNAME (), HOST_NAME ()
If the connection is "sa" (or any other SQL login), you cannot find the domain / window username. SQL Server knows that it is "sa" or it is a SQL login.
HOST_NAME can be unreliable and can be set in the connection string ("Application Name"). Or it might be vague like "Microsoft Office" for the default for Access, Excel, etc.
You can go back with client_net_address in sys.dm_exec_connections and match the MAC address for the IP and find out who is logged in ...
4
An The easiest way to find out how host and user is
EXEC sp_who2;
Where do you get some other information that might be good to know as if the user is active and so on ... that doesn't fix the problem, gbn announced.
3
Thanks for all your suggestions. I have tried all methods and I think that Joachim Backman's method satisfies my need. Here's a rundown of what I am learning.
- The sys.syslogins data request contains only log information. The account does not indicate the user's current timestamp. I tried to login from another application to my SQL and this query does not list login.
- SELECT SUSER_SNAME (), HOST_NAME () only displays one user on the SQL server. For example, I login as my name for SQL server. The result of this query contains only my name and the name of the machine. This query does not display the current users on the SQL server.
- exec sp_who2 contains the information I need. It lists the current username name, active state, db username access, and the command used.
In order to get the information I use in SP, I have to filter and join the information to other tables, such as emails. Here are the codes I am using:
DECLARE @retTable TABLE (SPID int not , Status varchar (255) not , Login varchar (255) not , HostName varchar (255) not , BlkBy varchar (10) not , DBName varchar (255) , Command varchar (255) not , CPUTime int not , DiskIO int not , LastBatch varchar (255) not , ProgramName varchar (255) , SPID2 int not , REQUESTID INT) INSERT INTO @retTable EXEC sp_who2 SELECT Status, Login , HostName, DBName, Command, CPUTime, ProgramName - * FROM @retTable --WHERE Login not like "sa%" - if not interested in sa ORDER BY Login, HostName