CAST or CONVERT: 'ntext' error

developmentalmadness

I have two copies of a database, one just created from a restore of a backup minutes before.

When I try and run Data Compare, it doesn't seem to matter which options I specify I get some form of the following error:

The following error message was returned from the SQL Server:

[291] CAST or CONVERT: invalid attributes specified for type 'ntext'
CAST or CONVERT: invalid attributes specified for type 'ntext'
CAST or CONVERT: invalid attributes specified for type 'ntext'

The following SQL command caused the error:

SELECT [AutoNumber], convert(nvarchar(1),[Type]) COLLATE Latin1_General_BIN , [NumericKey], [ItemNumber], convert(nvarchar(100),[AlphaKey]) COLLATE Latin1_General_BIN , [EntryDate], [EntryTime], convert(ntext(16),[Notes]) COLLATE Latin1_General_BIN , convert(nvarchar(255),[Event]) COLLATE Latin1_General_BIN , convert(nvarchar(255),[FollowupAction]) COLLATE Latin1_General_BIN , convert(nvarchar(50),[AssignedTo]) COLLATE Latin1_General_BIN , [ScheduledDate], [ScheduledTime], [ActualDate], [ActualTime], [Completed], convert(nvarchar(50),[EnteredBy]) COLLATE Latin1_General_BIN , convert(nvarchar(20),[Priority]) COLLATE Latin1_General_BIN , convert(nvarchar(20),[Status]) COLLATE Latin1_General_BIN , convert(nvarchar(250),[Keywords]) COLLATE Latin1_General_BIN , convert(ntext(16),[EmailOutgoing]) COLLATE Latin1_General_BIN , convert(ntext(16),[EmailIncoming]) COLLATE Latin1_General_BIN , convert(nvarchar(1),[ParentType]) COLLATE Latin1_General_BIN , convert(nvarchar(100),[ParentKey]) COLLATE Latin1_General_BIN , [TimeStamp]
FROM [dbo].[Notes] WITH (NOLOCK) ORDER BY [AutoNumber]

The columns are "text" not "ntext" (same goes for "varchar"/"nvarchar") and I can't seem to find any options to control any of this.

I'm evaluating Data Compare for a client. I've used it in the past and never run into this kind of thing before. Any help would be appreciated.

@VERSION:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)
Jun 11 2012 16:41:53
Copyright (c) Microsoft Corporation
Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

developmentalmadness

I tried an older version of Data Compare and am getting the same problem.

If I remove the text column from the comparison, then it works. The problem is that Data Compare is trying to pass a "size" argument to the convert function for text data types.

convert(ntext(16),[EmailIncoming]) COLLATE Latin1_General_BIN

There doesn't seem to be anything I can do about this on my side and I find it hard to believe that this problem even exists. I can duplicate the problem with the following script:

USE DatabaseA;

CREATE TABLE MyTest (
	[Id] INT PRIMARY KEY IDENTITY(1,1),
	[MyValue] TEXT
)

INSERT INTO MyTest (MyValue) VALUES ('some text')

USE DatabaseB;

CREATE TABLE MyTest (
	[Id] INT PRIMARY KEY IDENTITY(1,1),
	[MyValue] TEXT
)

And the database settings seem pretty vanilla as well:

USE [master]
GO

/****** Object:  Database [DatabaseA]    Script Date: 03/24/2014 07:10:33 ******/
CREATE DATABASE [DatabaseA] ON  PRIMARY 
( NAME = N'DatabaseA', FILENAME = N'c:\SQLServer\.....\DatabaseA.mdf' , SIZE = 5316608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB )
 LOG ON 
( NAME = N'DatabaseA_log', FILENAME = N'c:\SQLServer\.....\DatabaseA_log.ldf' , SIZE = 419776KB , MAXSIZE = 2048GB , FILEGROWTH = 35840KB )
GO

ALTER DATABASE [DatabaseA] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [DatabaseA].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [DatabaseA] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [DatabaseA] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [DatabaseA] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [DatabaseA] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [DatabaseA] SET ARITHABORT OFF 
GO

ALTER DATABASE [DatabaseA] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [DatabaseA] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [DatabaseA] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [DatabaseA] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [DatabaseA] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [DatabaseA] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [DatabaseA] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [DatabaseA] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [DatabaseA] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [DatabaseA] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [DatabaseA] SET  DISABLE_BROKER 
GO

ALTER DATABASE [DatabaseA] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [DatabaseA] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [DatabaseA] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [DatabaseA] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [DatabaseA] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [DatabaseA] SET READ_COMMITTED_SNAPSHOT ON 
GO

ALTER DATABASE [DatabaseA] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [DatabaseA] SET  READ_WRITE 
GO

ALTER DATABASE [DatabaseA] SET RECOVERY SIMPLE 
GO

ALTER DATABASE [DatabaseA] SET  MULTI_USER 
GO

ALTER DATABASE [DatabaseA] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [DatabaseA] SET DB_CHAINING OFF 
GO

Anu D

Thanks for your post!

We have logged a support ticket for you and will email you shortly!

developmentalmadness

Turns out the "MyTest" table script only partially replicates the problem. The key is the collation. I also learned that changing the collation on a database doesn't automatically change the collation on the existing tables. So there are two ways to reproduce the issue:

1) Create a database with the default collation set to Latin1_General_100_CI_AI

CREATE DATABASE RedGate1
COLLATE Latin1_General_100_CI_AI
GO

USE RedGate1;

CREATE TABLE MyTest (
[Id] INT PRIMARY KEY IDENTITY(1,1),
[MyValue] TEXT
)

INSERT INTO MyTest (MyValue) VALUES ('some text')
GO

Then create a duplicate database with the same name - the collation on the 2nd database won't matter.

2) OR create a database with the default collation, then create a table whose TEXT column has its collation set to Latin1_General_100_CI_AI

CREATE DATABASE RedGate1
GO

USE RedGate1;

CREATE TABLE MyTest (
[Id] INT PRIMARY KEY IDENTITY(1,1),
[MyValue] TEXT COLLATE Latin1_General_100_CI_AI
)

INSERT INTO MyTest (MyValue) VALUES ('some text')
GO