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)

SQL SERVER – Errors and Limitations of Working with SPARSE Columns in SQL Server

I make a visit to the local user group whenever I get and keeping the community juices up and running for me. I get an opportunity to share some of the learnings and meet tons of new people. In our recent meetup at the SQLBangalore UG, I met a lot of young talent joining the IT field. Many take time to walk up to me and share a moment now and then. It is in this continued journey that inspired me to write this blog.

When I was at the UG meet, I said I write on a lot of topics and showed up this blog. It is rarely that someone fails to stumble onto this space. But one kid walked up to me and asked, what is SPARSE columns? After I explained what it was, he immediately asked me – is there any catch to it? Are there restrictions and limitations? Though there were few that I mentioned, I thought I will write few as part of this blog.

NULL Effect

Sparse columns MUST be nullable. Trying to create a sparse column as not NULL will fail with an error.

CREATE TABLE noNullSparse (Name VARCHAR(20) SPARSE NOT NULL)

Msg 1731, Level 16, State 1, Line 1

Cannot create the sparse column ‘Name’ in the table ‘noNullSparse’ because an option or data type specified is not valid. A sparse column must be nullable and cannot have the ROWGUIDCOL, IDENTITY, or FILESTREAM properties. A sparse column cannot be of the following data types: text, ntext, image, geometry, geography, or user-defined type.

Though the error is not explicit, you can see that the columns marked as SPARSE cannot be NOT NULL fields.

Datatypes to Watch

The above error gives away a lot of information on what are the datatypes that will cause you possible errors if marked as SPARSE.

  • geography
  • geometry
  • image
  • ntext
  • text
  • timestamp
  • user-defineddatatypes(UDT)

The other attributes like IDENTITY, FILESTREAMS and ROWGUIDs are also not allowed.

SPARSE with Computed Column

Lesser known is that SPARSE column’s cannot be used with Computed Columns. But, we can use a SPARSE a column inside a Computed Column. Below is a classic example:

-- Trying to mark a computed column as SPARSE will fail with incorrect syntax error
CREATE TABLE Sales1 (MRP INT, Loss TINYINT, Profit AS (MRP - Loss) SPARSE)
-- However, including an existing SPARSE column in a computed column is allowed
CREATE TABLE Sales2 (MRP INT, Loss TINYINT SPARSE, Profit AS (MRP - Loss))
-- Cleanup
DROP TABLE Sales2

Final Note

The other times we are likely to get an error is to mark a SPARSE column with default values, Primary key, clustered Index, partition key, user defined table type etc. These are some of the restrictions when working with SPARSE columns and will raise an error if used.

Would be great to know if anyone uses SPARSE columns in your designs anywhere? What are the scenario’s you found it useful? Let me know via your comments as it would be a great learning for all.

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

SQL SERVER – Restore Error: Specified cast is not valid (SqlManagerUI)

As I always say, Errors are a great source of learning and I am fortunate to have you as my readers. Because one of my blog reader reported an interesting issue which is worth blogging. This is his initial email:

Hi Pinal,
Your blogs have been very helpful to me to find solution of almost any SQL problem so far. This time, I am writing directly to see if you have time and provide some help to me.
I have a backup from database in SQL Server 2008 R2. When I want to restore this backup to SQL Server, I get this error: “Error: Specified cast is not valid. (SqlManagerUI)” How to I resolve this error?
Thanks!

We then exchanged various mails with screenshots, query, output and with his patience, we were able to find the cause of the issue. Here we go to what happened behind the scenes:

Here were the steps to restore the database:

cast error 01 SQL SERVER   Restore Error: Specified cast is not valid (SqlManagerUI)

As soon as OK was clicked, this was the error.

cast error 02 SQL SERVER   Restore Error: Specified cast is not valid (SqlManagerUI)

If we click on red cross icon at left bottom, we would see below

cast error 03 SQL SERVER   Restore Error: Specified cast is not valid (SqlManagerUI)

Here is the partial text of the message.

Specified cast is not valid. (SqlManagerUI)
------------------------------
Program Location:
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.PopulateGridWithBackupSetsFromDevices()
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.GetBackupSetsFromDevices()
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.textDeviceSelected_TextChanged(Object sender, EventArgs e)

As we can see above that SSMS is trying to populate the grid with the details about the backup. When I put profiler on my machine, it was running Restore Headeronly command on the selected file. So, I asked to run the command manually. You can refer my earlier blog for this SQL SERVER – Restore Database Backup using SQL Script (T-SQL)

When we ran the command, we found below

cast error 04 SQL SERVER   Restore Error: Specified cast is not valid (SqlManagerUI)

Above could be because of two reasons (which I found)

  1. Backup taken on SQL 2012 and Restore Headeronly was done in SQL 2008 R2
  2. Backup media is corrupted.

Solution of 1st one is easy – restore it on same or higher version. 2nd one is a difficult situation because you need to look for another good/restorable backup. In my case, it was first situation so we were good.

Another possible reason of “Specified cast is not valid” would be password protected backups. In such backups, headeronly would return “*** PASSWORD PROTECTED ***” in the first column as shown below.

cast error 05 SQL SERVER   Restore Error: Specified cast is not valid (SqlManagerUI)

So, next time you ever see any UI error, find the T-SQL statement by using profiler and run that directly to see “real” error message.

Have you ever come across situation where UI was giving some misleading error? Please comment and share your knowledge.

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

SQL SERVER – Error Msg 511 using SQL Server Sparse columns

When I wrote the previous post around SQL SERVER – CREATE TABLE Failed Because Column ‘C1024′ in Table ‘myTable’ Exceeds the Maximum of 1024 Columns, I thought it was a simple concept. Lesser did I know that there can be something wrong. One of my blog reader said they were getting some sort of an error when adding a sparse column to a table. I was surprised to why one should get an error? I asked for further details because this was strange.

The mail stated they are getting:

Msg 511, Level 16, State 1, Line 6
Cannot create a row of size 10019 which is greater than the allowable maximum row size of 8060.
The statement has been terminated.

Just like a strange error gets me going. I started to dig into why are they were getting this error. This lead to an interesting learning for me with sparse columns.

Using sparse columns reduces the maximum size of a row from 8060 bytes to 8018 bytes because of the additional overhead used when storing non-NULL values. However, for most of the normal scenarios this should not be a concern, since by definition, most of the sparse columns in a row will contain NULL values. Therefore, size reduction obtained by not storing any data for NULL values in a sparse column easily compensates for this fixed overhead. This still doesn’t tell us why we are getting the above error. Let us understand the basics.

When you convert a nonsparse column to a sparse column, the sparse column will consume more space for nonnull values. Therefore, when the row size is near 4,009 bytes, the operation can fail. The reason is when we modify an existing table to add our space column, the data pages need to have enough space to make a copy of the original row, make changes and then delete the original row. The error is because of insufficient space on the data pages. Let us do a simple script to get the error:

--Create a column of size 6000 (note this is NCHAR)
CREATE TABLE sparse_tbl (c1 NCHAR(3000))
INSERT INTO sparse_tbl VALUES (REPLICATE ('z',100))
--Modify the column to sparse
ALTER TABLE sparse_tbl ALTER COLUMN c1 ADD SPARSE

msg511 sparse 01 SQL SERVER   Error Msg 511 using SQL Server Sparse columns

A similar error is encountered if you go ahead and remove a sparse column from an existing table with data where the rows have more than 4009 bytes. In case of a new table, this is not encountered in general because there is no data to work with or move.

So the next question was, how can I alter or add or remove sparse columns which have data more than 4009 bytes then? In this case:

  • Create a new table for the operation
  • Insert into new table using the old table
  • Delete the old table and rename the new table

If we follow a process to do the same, there is nothing difficult. I have always felt there are ways to work with data and work around things. I am hope in future versions Microsoft might change this behavior but this is the current workaround. Hope you have never encountered this error before in your code. Let me know your thoughts.

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