Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Unanswered: Nvarchar(max) is breaking full text search

    Hi

    We have a table that has a full text index on a bunch of ntext columns that are on it. For our next release we are trying to change those columns to nvarchar(max). We have a third party tool that generates our upgrade scripts. The script it generated basically drops the full text index, creates a new temp table, copies the records into the temp table, deletes the old table, renames the temp table and creates the full text index.

    The only problem is that after this change all full text searches on the table return no results. Every record has a rank of 0 where previously the ranking was over 500.

    Any idea what the problem could be or a different way to go about doing the upgrade?

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Why don't you write a script to alter the table and change the data type of the ntext columns to nvarchar (max) ?

    Code:
    ALTER TABLE table_1 ALTER COLUMN Anything NVARCHAR(MAX)
    This way full text indexes are preserved.

    Have a nice day!

  3. #3
    Join Date
    Mar 2009
    Posts
    2
    Hi

    Thanks for the response. Gave this a shot and received the following error:

    Cannot alter or drop column 'xxxxxxx' because it is enabled for Full-Text Search.

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    I doing the same thing
    I tryed
    ALTER TABLE Messages ALTER COLUMN Message NVARCHAR(max)
    also tryed
    ALTER TABLE Messages ALTER COLUMN Message NVARCHAR(MAX) null
    it error

    Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'max'.

    my system is
    Microsoft SQL Server Management Studio 9.00.1399.00
    Microsoft Analysis Services Client Tools 2005.090.1399.00
    Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
    Microsoft MSXML 2.6 3.0 5.0 6.0
    Microsoft Internet Explorer 6.0.3790.3959
    Microsoft .NET Framework 2.0.50727.1433
    Operating System 5.2.3790
    Last edited by myle; 03-20-09 at 02:44.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is strange. I ran the following script and it worked without any problem:

    Code:
    CREATE DATABASE [AccSQLMeta] ON  PRIMARY 
    ( NAME = N'AccSQLMeta', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AccSQLMeta.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'AccSQLMeta_log', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AccSQLMeta_log.ldf' , SIZE = 3072KB , FILEGROWTH = 10%)
    GO
    EXEC dbo.sp_dbcmptlevel @dbname=N'AccSQLMeta', @new_cmptlevel=90
    GO
    EXEC [AccSQLMeta].[dbo].[sp_fulltext_database] @ACTION = 'enable'
    GO
    USE [AccSQLMeta]
    GO
    IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') 
    	ALTER DATABASE [AccSQLMeta] MODIFY FILEGROUP [PRIMARY] DEFAULT
    GO
    
    USE [AccSQLMeta]
    GO
    CREATE FULLTEXT CATALOG [T_I_AccSQLMeta]
    IN PATH N'C:\AccSQLMeta'
    WITH ACCENT_SENSITIVITY = ON
    AS DEFAULT
    AUTHORIZATION [dbo]
    GO
    
    USE [AccSQLMeta]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Table_1](
    	[SysCounter] [int] IDENTITY(1,1) NOT NULL,
    	[Anything] [ntext] NULL,
    CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
    (
    	[SysCounter] ASC
    )WITH (PAD_INDEX  = OFF, 
    STATISTICS_NORECOMPUTE  = OFF, 
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    
    USE [AccSQLMeta]
    GO
    CREATE FULLTEXT INDEX ON table_1
    KEY INDEX PK_Table_1
    ON T_I_AccSQLMeta
    WITH CHANGE_TRACKING AUTO 
    GO
    
    USE [AccSQLMeta]
    GO
    ALTER TABLE table_1 ALTER COLUMN Anything NVARCHAR(MAX)
    GO
    Configuration:

    USER-14F7BFF6D6
    Les composants suivants sont installés*sur ce serveur

    MSSQLSERVER
    Analysis Services
    [Version*: 9.3.4035.00 Édition*: Developer Edition Niveau de correctif logiciel*: 9.3.4035 Langue*: Français (France)]

    Moteur de base de données
    [Version*: 9.3.4035.00 Édition*: Developer Edition Niveau de correctif logiciel*: 9.3.4035 Langue*: Français (France)]

    Composants de la station de travail
    [Version*: 9.3.4035.00 Édition*: Developer Edition Niveau de correctif logiciel*: 9.3.4035 Langue*: Français (France)]

    Integration Services
    [Version*: 9.3.4035.00 Édition*: Developer Edition Niveau de correctif logiciel*: 9.3.4035 Langue*: Français (France)]

    Notification Services
    [Version*: 9.3.4035.00 Édition*: Developer Edition Niveau de correctif logiciel*: 9.3.4035 Langue*: Français (France)]

    Microsoft SQL Server Management Studio: 9.00.4035.00
    Outils clients Microsoft Analysis Services: 2005.090.4035.00
    Microsoft Data Access Components (MDAC): 2000.085.1132.00 (xpsp.080413-0852)
    Microsoft MSXML: 2.6 3.0 4.0 5.0 6.0
    Microsoft Internet Explorer: 6.0.2900.5512
    Microsoft .NET Framework: 2.0.50727.3082
    Système d'exploitation: 5.1.2600
    I'll try next Monday at work on an Enterprise edition of SQL Server.

    Have a nice day !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •