Red Gate forums :: View topic - CAST or CONVERT: 'ntext' error
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Compare 10
SQL Data Compare 10 forum

CAST or CONVERT: 'ntext' error

Search in SQL Data Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
developmentalmadness



Joined: 22 Mar 2014
Posts: 3
Location: United States

PostPosted: Sat Mar 22, 2014 10:32 pm    Post subject: CAST or CONVERT: 'ntext' error Reply with quote

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)
Back to top
View user's profile Send private message
developmentalmadness



Joined: 22 Mar 2014
Posts: 3
Location: United States

PostPosted: Mon Mar 24, 2014 2:07 pm    Post subject: Same problem with v10.0.1.119 Reply with quote

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
Back to top
View user's profile Send private message
Anu Deshpande



Joined: 20 Apr 2009
Posts: 691
Location: Cambridge

PostPosted: Mon Mar 24, 2014 4:10 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
developmentalmadness



Joined: 22 Mar 2014
Posts: 3
Location: United States

PostPosted: Thu Mar 27, 2014 2:24 pm    Post subject: Collation Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group