CAST or CONVERT: 'ntext' error

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)

Comments

  • edited August 1, 2017 9:28AM
    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
    
  • 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: [email protected]
  • 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
Sign In or Register to comment.