导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

执行全文索引的SQL脚本报错

wxcnl 2007-12-07 02:09:54
declare @FTCPath3 varchar(200)
set @FTCPath3=N'C:\Full Text Catalog'

if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
exec sp_fulltext_database N'enable'

if not exists (select * from dbo.sysfulltextcatalogs where name = N'FT_NewsLetter')
EXEC dbo.sp_fulltext_catalog @ftcat=N'FT_NewsLetter', @action=N'create', @path=@FTCPath3

IF (OBJECTPROPERTY(OBJECT_ID(N'[dbo].[NewsLetters]'), 'TableFullTextCatalogId') = 0)
EXEC dbo.sp_fulltext_table @tabname=N'[dbo].[NewsLetters]', @action=N'create', @keyname=N'PK_NewsLetters', @ftcat=N'FT_NewsLetter'

GO
declare @lcid int
select @lcid=lcid from master.dbo.syslanguages where alias=N'English'
EXEC dbo.sp_fulltext_column @tabname=N'[dbo].[NewsLetters]', @colname=N'Content', @action=N'add', @language=@lcid
GO
EXEC dbo.sp_fulltext_table @tabname=N'[dbo].[NewsLetters]', @action=N'start_change_tracking'
GO
EXEC dbo.sp_fulltext_table @tabname=N'[dbo].[NewsLetters]', @action=N'start_background_updateindex'
GO
执行第一便
Warning: Table '[dbo].[NewsLetters]' does not have the option 'text in row' enabled and has full-text indexed columns that are of type image, text, or ntext. Full-text change tracking cannot track WRITETEXT or UPDATETEXT operations performed on these columns.
什么意思?怎么解决?

执行第二编
Msg 15631, Level 16, State 1, Procedure sp_fulltext_table, Line 239
Full-text change tracking is currently enabled for table '[dbo].[NewsLetters]'.
Msg 15633, Level 16, State 1, Procedure sp_fulltext_table, Line 368
Full-text auto propagation is currently enabled for table '[dbo].[NewsLetters]'.
什么意思?怎么解决?
...全文
65 点赞 收藏 12
写回复
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
rouqu 2007-12-07
楼主 你可以这样去做个小测试

--create tables for comparing
create table tbA (
ID int identity(1,1),
coln ntext,
primary key (ID)
)

create table tbB (
ID int identity(1,1),
coln ntext,
primary key (ID)
)

--对A表充填测试5W行
declare @i int
set @i = 0
if @i < 50000
begin
insert into tbA (coln) select N'y.................................................' --可以长一些
set @i = @i + 1
end

--对B表充填测试5W行(略去)

--对表A和表B都添加全文索引 均设置trace change和update index in background为ON

--创建测试存储过程

create proc p1 as
begin tran
declare @p1 binary(16)
select @p1 = TEXTPTR(coln) from tbA
updatetext tbA.coln @p1 10 3 N'xxxxxxxxxxxxxxxxxxxxxxxxxx' --可以长一些
commit tran
go

create proc p2 as
begin tran
declare @p2 binary(16)
select @p2 = TEXTPTR(coln) from tbB
updatetext tbB.coln @p2 10 3 N'xxxxxxxxxxxxxxxxxxxxxxxxxx' --可以长一些 但保持相同
commit tran
go

exec p1
go
exec p2
go

--观察...\Program Files\Microsoft SQL Server\MSSQL$CRM\FTDATA\SQL______\Build\Indexer\NlFiles文件的变化情况
回复
rouqu 2007-12-07
然后 对这个warning我的理解
Warning: Table '[dbo].[NewsLetters]' does not have the option 'text in row' enabled and has full-text indexed columns that are of type image, text, or ntext. Full-text change tracking cannot track WRITETEXT or UPDATETEXT operations performed on these columns.

BLOB列在'text in row'开关为ON的情形下能够track到update并作自动更新;若开关为OFF 即BLOB独立存储 相应的updatetext等操作FT不能自动跟踪到

回复
rouqu 2007-12-07
BOL
===
text in row supports the TEXTPTR, WRITETEXT, UPDATETEXT, and READTEXT functions.
回复
rouqu 2007-12-07
默认text in row开关为OFF 即表中的BLOB数值不跟其他数据占用共同的data pages 如果打开之后根据可用空间(列空间分配)和sp_tableoption指定的数值进行计算 在范围之内的将和data pages共同占用data pages空间 一如char/varchar等 即不用寻找额外的Blob页面

回复
wxcnl 2007-12-07
对了如何获取text in row的状态?
默认的是多少?
回复
wxcnl 2007-12-07
第二个问题已经明白了,关键是第一个
执行第一便
Warning: Table '[dbo].[NewsLetters]' does not have the option 'text in row' enabled and has full-text indexed columns that are of type image, text, or ntext. Full-text change tracking cannot track WRITETEXT or UPDATETEXT operations performed on these columns.


查了一下帮助,说
启用 text in row 选项后,可以使用 TEXTPTR、READTEXT、UPDATETEXT 或 WRITETEXT 语句读取或修改表中存储的任何 text、ntext 或 image 值的部分
我可不可以理解为我使用了tracking,然后TEXTPTR、READTEXT、UPDATETEXT 就无法使用了,他这里只是一个警告而已,对我的全文检索没有影响?(我在实践中好像是没有什么影响的,就是担心会有,所以才问问的)
回复
WNASP 2007-12-07
declare @objid int
select @objid = object_id('NewsLetters', 'local')
select ObjectProperty(@objid, 'TableFulltextChangeTrackingOn'),
ObjectProperty(@objid, 'TableFulltextBackgroundUpdateIndexOn')

可以以此来判断在执行
回复
rouqu 2007-12-07
我这边测了一下 如果你的change tracking & update index in background已经开启 再次运行
exec dbo.sp_fulltext_table @tabname=N'[dbo].[NewsLetters]', @action=N'start_change_tracking'
or
....@action=N'start_background_updateindex'
就会有这个错误

回复
rouqu 2007-12-07
Warning: Table '[dbo].[NewsLetters]' does not have the option 'text in row' enabled and has full-text indexed columns that are of type image, text, or ntext. Full-text change tracking cannot track WRITETEXT or UPDATETEXT operations performed on these columns.
---------
sp_tableoption默认text in row选项是OFF的 你对text/imaga列做FT索引 如果这一选项是ON FT的change tracking能够跟踪到对应的变化(做更新) 否则不能触发相应索引的更新

Msg 15631, Level 16, State 1, Procedure sp_fulltext_table, Line 239
Full-text change tracking is currently enabled for table '[dbo].[NewsLetters]'.
Msg 15633, Level 16, State 1, Procedure sp_fulltext_table, Line 368
Full-text auto propagation is currently enabled for table '[dbo].[NewsLetters]'.
------
之前已经给NewsLetters启用了选项开关 再次运行应该就会有这个提示
回复
rouqu 2007-12-07
sorry 没看清sql语句 上面的回答错误
回复
rouqu 2007-12-07
全文索引没有选中"enable change tracking"和"auto propagation"
回复
fwacky 2007-12-07
不懂,期待高手!
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告