How to rename MS SQL Server Database in SSMS

This article describes how to rename database in Microsoft SQL Server Management Studio.

Problem:

I wanted to rename one MS SQL database in MS SQL Management Studio via the context menu.

Renaming database via the context menu

After typing the new name and pressing Enter I got the following error message:

Error message during renaming the database
TITLE: Microsoft SQL Server Management Studio
------------------------------
Unable to rename TESTDB_1. (ObjectExplorer)
------------------------------
ADDITIONAL INFORMATION:
Rename failed for Database 'TESTDB'.  (Microsoft.SqlServer.Smo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)
------------------------------

Then I tried to rename with the following SQL statement:

ALTER DATABASE TESTDB
MODIFY NAME = TESTDB_1

In this case I got this error:

The database could not be exclusively locked to perform the operation.

Solution:

After some search on the Internet I found information, that I got this error, because the database is in multi-user mode and has some open connection.

Here is the solution which worked for me:

  1. At first need to change the database to single-user mode:

    ALTER DATABASE TESTDB
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    

  2. Then try to rename the database:

    ALTER DATABASE TESTDB
    MODIFY NAME = TESTDB_1
    

  3. Finally, set back the database to multi-user mode:

    ALTER DATABASE TESTDB_1
    SET MULTI_USER WITH ROLLBACK IMMEDIATE
    


6x faster WordPress Hosting6x faster WordPress Hosting

The Distributed Transaction doesn’t working

Problem:

System.Runtime.InteropServices.COMException (0x8004D00A): New transaction cannot enlist in the specified transaction coordinator.

Solution:

This error occurs when distributed transactions are not allowed over network.

By modifying the MSDTC security settings, you control how MSDTC communicates with remote computers over the network.

To access the MSDTC security configuration options follow these steps:

On Windows Server 2003 SP1 and Windows XP SP2:

  1. Click Start, click Run, and type dcomcnfg to launch the Component Services Management console.
  2. Click to expand Component Services and click to expand Computers.
  3. Right-click My Computer, and click Properties.
  4. Click on the MSDTC tab of the My Computer Properties dialog and click on the Security Configuration button to display the Security Configuration dialog box.

On Windows Server 2008, Windows 7 and Windows 10:

  1. Click Start, click Run, and type dcomcnfg to launch the Component Services Management console.
  2. Click to expand Component Services and click to expand Computers.
  3. Click to expand My Computer, click to expand Distributed Transaction Coordinator, right-click Local DTC, and click Properties.
  4. Click the Security tab of the Local DTC Properties dialog.
Local DTC Properties

Network DTC Access check box – Select this check box if you want to allow any network traffic for the Distributed Transaction Coordinator (DTC).

If this check box is not selected, the DTC will not flow any transactions to the network, and it will not accept any incoming traffic. Remote administration of this DTC will also be disabled.

Allow Inbound: Select this check box to allow a remote computer to flow transactions to the local computer. Typically, this option is needed on the computer that is hosting the DTC for a resource manager such as Microsoft SQL Server.

Allow Outbound: Select this check box to allow the local computer to flow transactions to a remote computer. Typically, this option is needed on the client computer, where the transaction is initiated.

Click OK.

Finally, restart the application which works with distributive transactions.


6x faster WordPress Hosting6x faster WordPress Hosting

Can’t build solution in Visual Studio

Problem:

When I wanted to build the solution in Visual Studio, I got message: Rebuild All failed.

Description:

I have 2 projects in the solution. MainApplication and ClassLibrary. The MainApplication has reference to ClassLibrary.

Project hierarchy in Solution Explorer in Visual Studio
Project hierarchy in Solution Explorer in Visual Studio

The Error List shows no error. There is also no error in code.

No error in Error List
No error in Error List

Then I switched to Output window and here I got more informations.

Output Window in Visual Studio
Output Window in Visual Studio

From the Output you see, that the ClassLibrary was successfully built. Problem was during the building of the MainApplication.

warning MSB3274: The primary reference "D:\Temp\13\MySolution\ClassLibrary\bin\Debug\ClassLibrary.dll" 
could not be resolved because it was built against the ".NETFramework,Version=v4.7.2" framework. 
This is a higher version than the currently targeted framework ".NETFramework,Version=v4.6.1".
2>D:\Temp\13\MySolution\MainApplication\Form1.cs(25,23,25,35): error CS0103: 
The name 'ClassLibrary' does not exist in the current context

From this information we know that the MainApplication has targeting Framework v4.6.1 but the ClassLibrary has higher version v4.7.2.

Solution:

I changed the Target Framework from v4.6.1 to v4.7.2 in MainApplication Project Properties.

Target Framework in Project Properties
Target Framework in Project Properties

After this change the Visual Studio successfully rebuilt the projects.

Summary:

The given project’s Target Framework version should be higher or equal than the target Framework version of referenced projects.


6x faster WordPress Hosting6x faster WordPress Hosting

Can’t read XML document if contains ampersand symbol

Problem:

System.Xml.XmlException: An error occurred while parsing EntityName.


Description:

I had the following C# code snippet and got error on the line 6 at doc.Load(…):

System.Xml.XmlException: An error occurred while parsing EntityName. Line 4, position 18.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
private void LoadXML()
{
     try
     {
          XmlDocument doc = new XmlDocument();
          doc.Load("data.xml");

          //...
     }
     catch (Exception ex)
     {
          string msg = ex.ToString();
     }
}

I checked the XML document and on the line 4 and position 18 I had & (ampersand) sign.

<?xml version="1.0" standalone="yes"?>
<Items>
  <Item>
    <Text>Black & White</Text>
  </Item>
</Items>

So the question is: how to parse XML file with & (ampersand) sign? 🙂

Solution:

Then I replaced the ampersand sign in the XML document with the appropriate escape sequences: &amp; and the problem was solved.

Here is the modified XML document:

<?xml version="1.0" standalone="yes"?>
<Items>
  <Item>
    <Text>Black &amp; White</Text>
  </Item>
</Items>

6x faster WordPress Hosting6x faster WordPress Hosting