SQL SERVER – Error: Msg 245 – Conversion failed when converting the varchar value ‘Inactive’ to data type int

Using CASE statements is something I see a lot of developers use. They use it in complex environments and I have been fortunate to troubleshoot and look at code blocks that run for pages using the standard CASE statements. This blog post is inspired by one such errors using CASE statements. This is one of the common error which is seen by many developers while using case/when combination. To understand the reason for such error, here is one of the simplified repro of the error:

SET NOCOUNT ON
GO
DECLARE @TempTable TABLE (id INT PRIMARY KEY, STATUS INT, Score INT)
INSERT INTO @TempTable VALUES (1,1,200)
INSERT INTO @TempTable VALUES (2,2,200)
INSERT INTO @TempTable VALUES (3,1,99)
INSERT INTO @TempTable VALUES (4,1,10)
SELECT id
,CASE
WHEN STATUS = 2
THEN 'Inactive'
ELSE score
END 'Score'
FROM @TempTable

If we execute above in management studio, we would get below error

id          Score
----------- -----------
1           200
Msg 245, Level 16, State 1, Line 8
Conversion failed when converting the varchar value 'Inactive' to data type int.

Always remember the golden rule of case/when: “All branches – same type”. It is very important that all return values have the same data type. If we don’t do this, we would end up in conversion problems, like the one shown above. In our sample, score column is integer whereas value “Inactive’ is string. Due to datatype precedence rule, Data Type Precedence SQL Server is trying to implicitly convert Inactive to Integer data type. SQL Server will always return the same data type regardless of which branch of the case/when expression is actually returned. For CASE expressions all expressions should be of the same type or implicitly convertible types.

Here is another simple example which would fail randomly.

SELECT CASE WHEN RAND() < 0.5 THEN 200 ELSE 'SQlAuthority' END AS SomeColumnName;

Based on output of RAND() it would fail with error

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'SQlAuthority' to data type int.

Here is the fix for the original problem.

SET NOCOUNT ON
GO
DECLARE @TempTable TABLE (    id INT PRIMARY KEY, STATUS INT, Score INT)
INSERT INTO @TempTable VALUES (1,1,200)
INSERT INTO @TempTable VALUES (2,2,200)
INSERT INTO @TempTable VALUES (3,1,99)
INSERT INTO @TempTable VALUES (4,1,10)
SELECT id
,CASE
WHEN STATUS = 2
THEN 'Inactive'
ELSE CAST (score AS VARCHAR(10))
END 'Score'
FROM @TempTable

Here is the output:

case when 01 SQL SERVER   Error: Msg 245   Conversion failed when converting the varchar value Inactive to data type int

Have you been following this practice while coding? What are your workarounds for these sort of case statements? Let me know over comments.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – FIX – Server principal ‘Login Name’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal

There are parts of SQL Server where I always like to take expert advice. Sometimes a quick call to a friend can save you tons of time. When I see topics around AlwaysOn, though I know what the basic steps are to building and working with AlwaysOn, the hardcore troubleshooting is not the forte perse. But when I encounter questions around it, I put my learning hat to explore the possible reasons. There was a mail from one of the readers which has been on my Inbox for couple of weeks and I wanted to find the answer. His question goes like this:

While working with AlwaysOn availability group and cleaning it up, I am encountering below error while dropping a login.

DROP LOGIN Pinal
GO

Msg 15173, Level 16, State 1, Line 1
Server principal ‘Pinal’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.

drop error 02 SQL SERVER   FIX – Server principal ‘Login Name’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal

Even trying from UI also gives same error

drop error 01 SQL SERVER   FIX – Server principal ‘Login Name’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal

Next task was to find what is causing the error, so I made below query which can tell the exact objects that are dependent and making our DROP command to fail.

SELECT class_desc,*
FROM sys.server_permissions
WHERE grantor_principal_id = (
SELECT principal_id
FROM sys.server_principals
WHERE NAME = N'Pinal')

SELECT NAME
,type_desc
FROM sys.server_principals
WHERE principal_id IN (
SELECT grantee_principal_id
FROM sys.server_permissions
WHERE grantor_principal_id = (
SELECT principal_id
FROM sys.server_principals
WHERE NAME = N'Pinal'))

drop error 03 SQL SERVER   FIX – Server principal ‘Login Name’ has granted one or more permission(s). Revoke the permission(s) before dropping the server principal

Based on output, it means there is an endpoint on which Pinal has been granted permission. Second result shows that I have used my account i.e. Pinal to create endpoint and granted permission to AppUser account using below command:

CREATE ENDPOINT [hadr_endpoint]
STATE
=STARTED
AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
GRANT CONNECT ON ENDPOINT::[hadr_endpoint] TO AppUser
GO

As the error message suggested, I revoked permission using the below command:

REVOKE CONNECT ON ENDPOINT::[hadr_endpoint] TO AppUser
GO

After the above step, I was able to delete login. Yes, this was a great learning for me today. Do let me know if you ever encountered similar errors in your environments?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix – Error: Msg 468, Level 16, State 9, Line 1

Ever since I have started writing about Error Messages a number of you ping me from time to time to understand why you are getting an error. As I always say, Error messages are a great way to learn concepts and we become better if we understand the reason behind every error one gets. As I scan through almost every single mail that comes in, some of them catch my attention and I tend to write this experience back as a blog post.

Last week, one fresher who was working for an European client wrote back to me with an error mentioned below:

Msg 468, Level 16, State 9, Line 15
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “French_CS_AI” in the equal to operation.

Though the error looked simple and self-explanatory, this kid wanted to mimic this error in his environment. He also added that he was using temp tables while this error surfaced. This statement got me interested and I wanted to show him with a simple script to how this error can be achieved.

Let me start by knowing the server collation to start with:

-- Server Collation
SELECT SERVERPROPERTY('Collation')

——————————————————————–

SQL_Latin1_General_CP1_CI_AS

As you can see, I am using the default collation on my server at this moment. Now let us create an DB with a different collation (French) for example. We will also create two tables post that. First will be a normal table while the second one will be an Temp table.

-- Create database with different collation
CREATE DATABASE [DB_Not_Contained]
CONTAINMENT
= NONE
COLLATE French_CS_AI
GO

-- Create 2 tables with 1 being a temp table so it goes to tempdb and uses the server collation and not the database collation
USE [DB_Not_Contained]
GO
CREATE TABLE [DemoCollation]
(DemoCollationNM VARCHAR(100))
GO
CREATE TABLE #DemoCollation
(DemoCollationNM VARCHAR(100))

-- Insert records into both tables
INSERT dbo.DemoCollation
(DemoCollationNM)
VALUES ('Test Join');
INSERT #DemoCollation
(DemoCollationNM)
VALUES ('Test Join');

Now that the basic setup of data is over. Let us make a simple join between these two tables.

-- Now query and try and join both tables having 2 different collations
SELECT p.DemoCollationNM
FROM dbo.DemoCollation p
INNER JOIN #DemoCollation d ON
p.DemoCollationNM = d.DemoCollationNM

Woot, we will receive the error message:

Msg 468, Level 16, State 9, Line 35
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “French_CS_AI” in the equal to operation.

Whenever you encounter this error message, please go ahead and check the collation of tables under question to be clear they are not different.

collation conflict 01 SQL SERVER   Fix   Error: Msg 468, Level 16, State 9, Line 1

So what are the learnings from these simple steps?

  • We cannot join tables, columns when there is conflict in collation between the objects
  • Temp table uses the same collation as our Server by default, so are the objects that are created.

I know I have given you a hint on how you might potentially mitigate this, but let me see if anyone can give me the solution to this problem and how have you ever been able to solve these? Have you ever encountered this error?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – 2016 FIX: Install – Rule “Oracle JRE 7 Update 51 (64-bit) or higher is required” failed

I enjoy spending time with my daughter almost every single day. It keeps me busy at home and most importantly, some quality time with her allows me to relax after a long day at work. But there one thing that has caught my attention every single day I have been with her – she is curious about every single thing that she sees. With every new release of SQL Server, I sort of get into the same mode or feeling. I become a child as there is so much to explore and learn.

I heard about new a lot of new features in SQL Server 2016. So, I downloaded media from Microsoft site and tried installing on my VM. I have selected all the features and moved forward. This is common because I don’t want to miss out on anything that gets introduced in the new release. Later, during the installation process, I faced an error and I was not able to proceed.

Here is the Rule check failure.

Ora JRE 01 SQL SERVER   2016 FIX: Install – Rule Oracle JRE 7 Update 51 (64 bit) or higher is required failed

I was wondering what part of SQL Server required us to have Oracle JRE. If I click on hyperlink on failed, I get below:

Ora JRE 02 SQL SERVER   2016 FIX: Install – Rule Oracle JRE 7 Update 51 (64 bit) or higher is required failed

Here is the text of the message:

—————————
Rule Check Result
—————————
Rule “Oracle JRE 7 Update 51 (64-bit) or higher is required” failed.
This computer does not have the Oracle Java SE Runtime Environment Version 7 Update 51 (64-bit) or higher installed. The Oracle Java SE Runtime Environment is software provided by a third party. Microsoft grants you no rights for such third-party software. You are responsible for and must separately locate, read and accept applicable third-party license terms. To continue, download the Oracle SE Java Runtime Environment from http://go.microsoft.com/fwlink/?LinkId=526030.
—————————
OK
—————————

NOTE: On little exploring, found that you can skip this error if you don’t want to use PolyBase feature. You can go back and uncheck highlighted feature to avoid this check.

Ora JRE 03 SQL SERVER   2016 FIX: Install – Rule Oracle JRE 7 Update 51 (64 bit) or higher is required failed

If you want to use PolyBase then to solve above error, you can follow below steps:

  1. Go to http://www.oracle.com/technetwork/java/javase/downloads/index.html
  2. Click on Download on JRE as highlighted below

Ora JRE 04 SQL SERVER   2016 FIX: Install – Rule Oracle JRE 7 Update 51 (64 bit) or higher is required failed

  1. Then click on “Accept License Agreement” as shown below

Ora JRE 05 SQL SERVER   2016 FIX: Install – Rule Oracle JRE 7 Update 51 (64 bit) or higher is required failed

  1. Once accepted, I have downloaded the one which says Windows x64 because my operating system is windows 64 bit. Note that version (8 update 45) will change in future but steps would remain same.

Ora JRE 06 SQL SERVER   2016 FIX: Install – Rule Oracle JRE 7 Update 51 (64 bit) or higher is required failed

  1. After installing, I was able to move forward and install SQL Server 2016.

Hope this helps those trying to install SQL Server 2016 with Polybase features. I am sure you will be able to solve this.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX – Export Error – ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine

Working with Excel files can always be of great interest. I have not seen a reporting solution in the world that doesn’t give an option to export to excel. If you are talking about two separate solutions and you want to export and import, then such options can be of great use. In this blog, I was trying to import data from Excel sheet to SQL Instance by using Import Wizard available in SQL Server Management Studio. I am sure all of us have had this requirement once in a while and would have surely tried to get this working.

ace error 01 SQL SERVER   FIX   Export Error   Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine

After selecting the option, I selected Data Source as “Microsoft Excel” and selected file name which I wanted to import. Excel file was created with latest version of excel so I selected “Microsoft Excel 2007”.

ace error 02 SQL SERVER   FIX   Export Error   Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine

As soon as I hit next, I got below error

TITLE: SQL Server Import and Export Wizard
——————————
The operation could not be completed.
——————————
ADDITIONAL INFORMATION:
The ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine. (System.Data)
——————————
BUTTONS:
OK
——————————

ace error 03 SQL SERVER   FIX   Export Error   Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine

I checked management studio and as the message says, I didn’t see the provider as we can see below. We can also use below query to find the details of providers installed on the machine

EXECUTE MASTER.dbo.xp_enum_oledb_providers

ace error 04 SQL SERVER   FIX   Export Error   Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine

Few blogs have mentioned to download https://www.microsoft.com/en-us/download/details.aspx?id=23734 (2007 Office System Driver: Data Connectivity Components) but that didn’t work. So I tried installing http://www.microsoft.com/en-us/download/details.aspx?id=13255 (Microsoft Access Database Engine 2010 Redistributable) but it also failed because I am using 32 bit version of office on my 64 bit PC.

Note: If you are running 32-bit version of SQL then your issue will be solved by installing above. In my PC, I have to take below approach.

  1. Uninstall 32-bit version of Microsoft Office.
  2. Install the 64-bit version of MS Office (Make sure setup is run from “x64″ folder and run setup.exe in order to install the 64 bit of MS Office).
  3. Install the 64-bit version “Microsoft.ACE.OLEDB.12.0″ (download AccessDatabaseEngine_x64.exe from second link provided earlier).

Here is the location of DLL “ACEOLEDB.DLL”

  1. On a 32-bit operating system
    1. C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL
  2. On a 64-bit operating system
    1. If 32-bit office is installed then

C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL

  1. If 64-bit office is installed then

C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL

Production Server would not have complete office installed so it would not be a big deal to install correct version of driver.

Have you ever encountered this error in your environments? What did you do to solve them? Let me know.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Evaluation Period Has Expired – How to Activate SQL Server?

Sometimes we are in a hurry that we forget activities that are pending. A typical example would be like walking into a room and we keep thinking about why we were there in the first place. Isn’t that common? Or I think age is catching on me for sure. Some of the classic mistakes I have seen people doing is to bring up an environment in a hurry and choose the Evaluation Edition because they don’t have the key handy at that point in time. They plan to add the same at a later point in time and forget it. One fine day SQL starts to error out and on inspection, you hit the problem and all work comes to a stop.

If you have been running SQL Server and it is expired, there would be a need to activate it. The steps are a little different as compared to Windows activation. The edition terminology is also little different from the Windows world. In SQL Server, evaluation edition is actually an enterprise edition of SQL Server, having 180 days’ time limit – so it is called as Enterprise Evaluation Edition

If the SQL Server engine is expired, the SQL services would not start and you will find below error in SQL Server error log and event viewer. Here is the blog to find a SQL ERRORLOG file location

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

Here is the sample ERRORLOG. I have highlighted two things below – current version and error message. The same message will come in event log also.

2015-03-25 10:34:06.84 Server      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
2015-03-25 10:34:06.84 Server      (c) Microsoft Corporation.
2015-03-25 10:34:06.84 Server      All rights reserved.
2015-03-25 10:34:06.84 Server      Server process ID is 6584.
2015-03-25 10:34:06.84 Server      Authentication mode is MIXED.
2015-03-25 10:34:06.84 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2015-03-25 10:34:06.84 Server      The service account is 'NT Service\MSSQL$MSSQLSERVER'. This is an informational message; no user action is required.
2015-03-25 10:34:06.84 Server      Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2015-03-25 10:34:06.84 Server      Command Line Startup Parameters:
-s "MSSQLSERVER"
2015-03-25 10:34:07.15 Server      Error: 17051, Severity: 16, State: 1.
2015-03-25 10:34:07.15 Server      SQL Server evaluation period has expired.

On the other hand, if client tools are expired, then we would have trouble opening SQL Server Management Studio and other client tools. Here is the pop-up which would be received.

Evaluation period has expired. For information on how to upgrade your evaluation software, please go to http://www.microsoft.com/sql/howtobuy

sql expired 01 SQL SERVER   Evaluation Period Has Expired   How to Activate SQL Server?

In such situations, we need to perform an activation of SQL Server. This is called as edition upgrade in SQL Server installation terminology. To upgrade, you need to have installation media of the destination version. SQL Server evaluation edition can be upgraded to standard, developer, enterprise editions. The complete upgrade metrics can be found in SQL Server books online https://msdn.microsoft.com/en-us/library/ms143393(v=sql.120).aspx

Here are the steps:

  1. From SQL Server installation media, launch setup.exe.
  2. From the SQL Server Installation Center click Maintenance, and then select Edition Upgrade

sql expired 02 SQL SERVER   Evaluation Period Has Expired   How to Activate SQL Server?

  1. It would launch Wizard where you need to keep on pressing next, next and finally “Upgrade”. Make sure that edition shown in the license terms screen is what you purchased.

Hope this would help.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Startup Problem – FIX – Error 5118, Severity 16, State 1

I have been monitoring some of the error messages that SQL Server has been throwing in the recent versions and they are self-explanatory. If we understand the English, then half the problem is solved. It becomes easier and quicker for an administrator if they ever encounter such errors. This blog is an inspiration to one such error which one of my readers found it difficult to understand because he was just hired from college.

My blog reader contacted me via comments and told that he is not able to start SQL Service. Without any delay, I have asked to share the error message. He told that he is stating it from Services.msc and getting an error:

startup 01 SQL SERVER   Startup Problem – FIX    Error    5118, Severity 16, State 1
—————————
Services
—————————
Windows could not start the SQL Server (SQL2014) on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 3417.
—————————
OK
—————————

I told that this is not an error which can help much and I asked to share SQL Server ERRORLOG. Since he was not a SQL person, he was not aware of that file. I shared below blog:

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

When I looked into the ERRORLOG, found below messages.

2015-06-10 20:54:44.00 spid7s      Starting up database ‘master’.
2015-06-10 20:54:44.00 spid7s      Error: 5118, Severity: 16, State: 1.
2015-06-10 20:54:44.00 spid7s      The file “C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\master.mdf” is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

I asked the question – Did you make some changes with the file master.mdf? He said NO so I asked to share screenshot.

startup 02 SQL SERVER   Startup Problem – FIX    Error    5118, Severity 16, State 1

Blue color of the file indicates that files are compressed and that’s the precise error, we are seeing in the ERRORLOG also. I asked him to un-compress the file using below option.

startup 03 SQL SERVER   Startup Problem – FIX    Error    5118, Severity 16, State 1

After that he was able to start SQL Service. I asked him the changes which are done and he told that due to disk space issues, complete C:\ drive was compressed.

Have you ever enabled compression on SQL machines on the folder which has SQL database files? Never do that! Have you ever encountered these errors? Let me know some of the reasons you did so and faced this issue.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Server: Login failed for User ‘NT AUTHORITY\ANONYMOUS LOGON’

Errors related to logins are one of the most searched in this blog. Many of the blogs that talk about these errors are the ones that get viewed often too. This is one of the interesting error which you might see in your environments. One of my blog reader contacted me and told that their event log and ERRORLOG is full of below messages:

Error: 18456, Severity: 14, State: 11.
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. [CLIENT: x.x.x.x]

Very first thing which should catch your eye would be “ANONYMOUS LOGON”. It is also referred as NULL session. My first ask from him was if he was seeing any SPN related errors in SQL Server ERRORLOG?

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

Here is the message which we found in ERRORLOG

2015-06-09 16:25:59.86 Server      SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

In above lines, SQL is trying to register automatically. But below indicates that SQL was unable to do so.

2015-06-09 16:26:06.90 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SQLA.mydomain.com:SQL2014 ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

2015-06-09 16:26:06.90 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SQLA.mydomain.com:1500 ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

So, the fix of the problem would be to create SPN which are needed for this SQL Instance. We need to use SETSPN.exe and create SPNs which SQL was trying to register. How would one get the tool? As per https://technet.microsoft.com/en-us/library/cc731241.aspx
{
Setspn is a command-line tool that is built into Windows Server 2008. It is available if you have the Active Directory Domain Services (AD DS) server role installed. To use setspn, you must run the setspn command from an elevated command prompt. To open an elevated command prompt, click Start, right-click Command Prompt, and then click Run as administrator.
}

For a TCP/IP connection the SPN is registered in the format of MSSQLSvc/<FQDN>:<tcpport>. We should remember that both named instances and the default instance are registered as MSSQLSvc, but <tcpport> value to would be different for instances. Here are the commands if you want to create them manually.

  1. To List SPN we need -L parameter.

SetSPN -L domain\account

  1. To create/add SPN we need to use -A parameter

SetSPN –A MSSQLSvc/<SQL Server FQDN>:<port> <Domain\Account>

While searching for SETSPN.exe command I came across this nice tool which can help a lot of pains like syntax error etc. is called as “Microsoft® Kerberos Configuration Manager for SQL Server®” which can be downloaded from https://www.microsoft.com/en-us/download/details.aspx?id=39046

The good piece about this tool is that it can help in finding missing SPN and also provide script to run or fix it directly, if you have permission. Basically, it can

  • Gather information on OS and Microsoft SQL Server instances installed on a server.
  • Report on all SPN and delegation configurations on the server.
  • Identify potential problems in SPNs and delegations.
  • Fix potential SPN problems.

Once SPN was created, we were able to fix the issue.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX: Msg 3102, Level 16, State 1 – RESTORE cannot process database ‘Name’ because it is in use by this session

Some of the simple learnings can come from unexpected places and this blog post is a classic example to that statement. Recently I got an email from one of my readers about the below error message:

Msg 3102, Level 16, State 1, Line 2
RESTORE cannot process database 'DBProductDevelopment' because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

As I always do, I searched on my own blog using http://search.sqlauthority.com and found few earlier blogs where few of my readers reported the error which is the source of this blog.

SQL SERVER – FIX : Error 3154: The backup set holds a backup of a database other than the existing database

SQL SERVER – FIX : ERROR : Msg 3159, Level 16, State 1, Line 1 – Msg 3013, Level 16, State 1, Line 1

One of my reader has asked interesting question related to this error:

Does it mean that I need to restore master database first and then restore this? How is that possible? Is there something wrong with error message?

Answer: No, the error message is not asking to restore master. All it is saying is that the restore command is running in the same database for which restore is being attempted. Error message is asking to change context to master database before performing restore.

Here is the repro of the problem:

CREATE DATABASE SQLAuthority
GO
USE SQLAuthority
GO
BACKUP DATABASE SQLAuthority TO DISK = 'SQLAuthority.bak'
GO
RESTORE DATABASE SQLAuthority FROM DISK = 'SQLAuthority.bak' WITH REPLACE
GO

restore error 01 SQL SERVER   FIX: Msg 3102, Level 16, State 1   RESTORE cannot process database Name because it is in use by this session

As we can see, SQL Server can’t complete the restore operation because the user database has an active connection within our current session. We’ll need to change our database connection to a different database before we attempt to perform the restore. We can use the T-SQL USE command:

Here is the modified script where I have highlighted the change.

Here is the cleanup script.

restore error 02 SQL SERVER   FIX: Msg 3102, Level 16, State 1   RESTORE cannot process database Name because it is in use by this session

/* clean up*/
USE MASTER
GO
ALTER DATABASE SQLAuthority SET single_user WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE SQLAuthority
GO

Have you ever face similar error in your environments? Do you have any similar experience to share? Let me know over the comments section below.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX – Msg 3702, Level 16, State 3 – Cannot Drop Database “DB_Name” Because it is Currently in Use

We learn from mistakes and we improvise with experience. I couldn’t think of a better example than the one below for this. I have been watching many sessions by various speakers and found that many times they have struggled to get rid of below error message.

Msg 3702, Level 16, State 3, Line 1
Cannot drop database “DemoDB” because it is currently in use.

The meaning of the error message is pretty clear that someone is using the database and it can’t be deleted/dropped. First, make sure that it is not our own connection. To make sure, always change the context before dropping the database as shown below

kill 01 SQL SERVER – FIX – Msg 3702, Level 16, State 3   Cannot Drop Database DB Name Because it is Currently in Use

USE MASTER
GO
DROP DATABASE DemoDB
GO

If we are still getting above error, then there are two approaches to solve this:

  1. Long approach: Find the connections which are blocking me to drop the database. Essentially find those sessions which are using this database and then kill those sessions. Here is the script to generate kill command.

SELECT 'kill ' + CONVERT(VARCHAR(100), session_id)
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('DemoDB')
AND
session_id <> @@spid

This would give output as below.

We can run the kill command and try dropping database again. If it was successful, go ahead else repeat the loop.

  1. Short approach: SQL Server has inbuilt functionality where it can kick out all the connections. There is a cause called “WITH ROLLLBACK IMMEDIATE” which can be used to kill other connections and forcing rollback of their work.

USE [master]
GO
ALTER DATABASE [DemoDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [DemoDB]
GO

If you want to learn other rollback options available with ALTER DATABASE, you can read my earlier blog as well

SQL SERVER – Difference Between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE

Hope this helps and you will use the new technique in your presentations.

Reference : Pinal Dave (http://blog.SQLAuthority.com)