CAST or CONVERT: 'ntext' error

Compares and synchronizes SQL database content.

CAST or CONVERT: 'ntext' error

Postby developmentalmadness » Sat Mar 22, 2014 10:32 pm

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.

I forgot to mention here's @@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
 
Posts: 3
Joined: Sat Mar 22, 2014 10:26 pm
Location: United States

Same problem with v10.0.1.119

Postby developmentalmadness » Mon Mar 24, 2014 2:07 pm

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
developmentalmadness
 
Posts: 3
Joined: Sat Mar 22, 2014 10:26 pm
Location: United States

Postby Anu Deshpande » Mon Mar 24, 2014 4:10 pm

Thanks for your post!

We have logged a support ticket for you and will email you shortly!
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Collation

Postby developmentalmadness » Thu Mar 27, 2014 2:24 pm

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
developmentalmadness
 
Posts: 3
Joined: Sat Mar 22, 2014 10:26 pm
Location: United States


Return to SQL Data Compare 10

Who is online

Users browsing this forum: No registered users and 1 guest