Category: Errors and Exceptions

Errors and Exceptions, SQL

The data type ntext cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.
Exception Message

With today’s post, we will deep dive into an error message that one of my friend encountered recently as below.
The data type ntext cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.
Sample Code

Let us first replicate the issue with the below sample code snippet. 
Drop Table  if exists T1,T2
create Table T1(id int,Col1 ntext)
Create Table T2(id int,Col1 ntext)

Insert into T1 Values(1,'Big value')
Insert into T2 Values(1,'Big value')

Select  * From (
Select * From T1
union 
Select * From T2)A

The execution of the above code results in the error message and the message is very clear that those specified operators are not allowed with ntext datatype.

Solution

1. Avoid NTEXT datatype

NTEXT datatype is a deprecated datatype in SQL Server. Try to avoid in future development and replace the existing with nvarchar(max).

2. Replace UNION with UNION ALL (if possible)

UNION ALL is a clear winner of this situation over UNION operator. So, if you can change the code to replace union with union all, that would be the easiest way. Then why is it so? – Simple, UNION operator does a sort operation internally which is forbidden with datatype ntext whereas UNION ALL does not require sort operation. 

Select  * From (
Select * From T1
union ALL
Select * From T2)A

3. Construct column list without ntext datatype columns 

Drop Table  if exists T1,T2
create Table T1(id int,Col1 ntext)
Create Table T2(id int,Col1 ntext)

Insert into T1 Values(1,'Big value')
Insert into T2 Values(1,'Big value')

--Construction of column list without ntext datatype columns

Declare @Col_list varchar(max)
Set @Col_list = (
SELECT  STUFF((SELECT  ',' + name FROM sys.columns EE
            WHERE   EE.object_id =E.object_id and system_type_id not in (99)
            ORDER BY column_id 
        FOR XML PATH('')), 1, 1, '') AS 'CommaseparatedString'
FROM sys.columns E where object_id = object_id('T1') 
and system_type_id not in (99) group by object_id) 

Declare @sql nvarchar(max) = ''
Set @sql = 'Select  * From (
Select ' + @Col_list + ' From T1
union
Select ' + @Col_list + ' From T2)A'

exec sp_executesql @sql

Sometime, we may not be able to avoid the ntext column depending on the requirement. In such situation, you may use an explicit conversion of those ntext datatype to nvarchar(max) as below. 

--Construction of column list with ntext datatype columns but with explicit conversion of datatype

Declare @Col_list varchar(max)
Set @Col_list = (
SELECT  STUFF((SELECT  ',' + case when system_type_id= 99  then 'Cast(' +  name + ' as nvarchar(max)) as ' + name Else name End FROM sys.columns EE
            WHERE   EE.object_id =E.object_id 
            ORDER BY column_id 
        FOR XML PATH('')), 1, 1, '') AS 'CommaseparatedString'
FROM sys.columns E where object_id = object_id('T1') 
group by object_id)

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Errors and Exceptions, SQL

SQL Server – Invalid Urn filter on server level: filter must be empty, or server attribute must be equal with the true server name

Problem Statement:

One of my colleague has reported an issue as below while he is trying to restore a database from backup.

SQL Server – Invalid Urn filter on server level: filter must be empty, or server attribute must be equal with the true server name

To my best knowledge, I was seeing this error first time and I was trying to understand about the issue with my google search and there are quite few good posts out there too.

http://blog.patricknielsen.net/2011/01/sql-server-invalid-urn-filter-on-server.html
https://debabratahowlee.blogspot.com/2016/09/sql-server-invalid-urn-filter-on-server.html

But somehow I was thinking something different as I was sure there were no activities at server level. Then I simply closed all open connections and restarted SSMS and tried and it worked perfectly this time. So, I do not have any clue yet what would have happened under the cover, however, am sure there is something gone bad it could be a failover/underlined network or anything that it could not resolve the server name.

If you have any different thoughts, please share in comment section.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Errors and Exceptions, SQL

Error: Changes to the state or options of database ‘dbname’ cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
One of my colleague had an issue in dropping a database in her testing environment. She was not able to drop a database as she gets an error message (as below). Let me try to provide what she tried and ended up for every ones understanding. 
alter database [dbname] set multi_user with rollback immediate
Drop database [dbname]

Error Message:

Msg 5064, Level 16, State 1, Line 1 Changes to the state or options of database ‘dbname’ cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

Solution:

When I analysed, I could find that the database has gone into single user mode and there was an open session on this database.Since, its a testing environment, I had killed the open session from the database and tried to drop the database by putting it multi user as first step as below and it was successful. 

USE master;

DECLARE @killSessions varchar(8000) = '';  
SELECT @killSessions = @killSessions + 'kill ' + CONVERT(varchar(5), spid) + ';'  
FROM master..sysprocesses  
WHERE dbid = db_id('dbname')
EXEC(@killSessions); 

alter database [dbname] set multi_user with rollback immediate
--Drop database [dbname] /*Only if need to be dropped*/

Hope this helps if you come across similar situations.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Errors and Exceptions, SQL

The transaction log for database ‘database name’ is full due to ‘REPLICATION’
Today, we are going to see an exception from SQL Server “The transaction log for database ‘dbname’ is full due to ‘REPLICATION'”.

Recently, we received a backup of a database for a troubleshooting purpose from the production. The database had configured with CDC in Production environment. While we get this database and restored in our local environments, it is observed that the size of the database is very huge and if you look at the size in details, its log that is majorly contributing the size.

Since its huge in size, we tried to shrink the file, please note this is a non-production environment, shrinkfile is not advised to run without a careful consideration and validation. Few references on shrink file written earlier. Since, in our case it was a testing environment, we were free to use this command to reclaim the space.

Msg 9002, Level 17, State 6, Line 10 The transaction log for database ‘Restoredbname’ is full due to ‘REPLICATION’.

It is also observed that log_reuse_wait_desc was showing “REPLICATION” for the database. 

Select log_reuse_wait_desc,* From sys.databases
As we know this db was enabled with CDC in Production environment, the first attempt was to disable CDC on restored database. 

use Restoredbname
sys.sp_cdc_disable_db

Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 262 [Batch Start Line 2] Could not update the metadata that indicates database Restoredbname is not enabled for Change Data Capture. The failure occurred when executing the command ‘(null)’. The error returned was 9002: ‘The transaction log for database ‘Restoredbname’ is full due to ‘REPLICATION’.’. Use the action and error to determine the cause of the failure and resubmit the request.

We also noticed checkpoint was also not successful due to serious disk issue. 
checkpoint

Could not write a checkpoint record in database Restoredbname because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files. Msg 5901, Level 16, State 1, Line 10 One or more recovery units belonging to database ‘Restoredbname’ failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure. Msg 9002, Level 17, State 6, Line 10 The transaction log for database ‘Restoredbname’ is full due to ‘REPLICATION’.

Finally, we decided to apply sp_repldone on the database as below. When xactid is NULL, xact_seqno is NULL, and reset is 1, all replicated transactions in the log are marked as distributed. This is useful when there are replicated transactions in the transaction log that are no longer valid and you want to truncate the log. sp_removedbreplication stored procedure removes all replication objects on the publication database on the Publisher instance of SQL Server or on the subscription database on the Subscriber instance of SQL Server. Once we executed the commands, we were able to shrink the file and the size has been reclaimed to os. 

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time= 0, @reset = 1
sp_removedbreplication

The solution discussed is NOT a general solution nor for Production environments. This should ONLY be used for non-production or lower environments where the restored database is used for testing purpose and truncating log is not a concern!

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!