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=@FTCPath3IF (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]'.
什么意思?怎么解决?
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=@FTCPath3IF (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]'.
什么意思?怎么解决?
解决方案 »
- 如何更新TEXT类型字段
- 要查询所有姓李且全名为3个字的学生的全部信息
- 如何通过存储过程,返回多条记录
- 怎么附加数据?
- SQL Table 怎样导出到xml文件?
- 怎样把tTest 表里的数据全部整合到一个tmp表的text字段中
- 一条SQL语句问题,请大家帮忙,急,在线等待~~~~!
- 数据库维护计划中的问题!请帮助!
- sql 语句 between... and ...
- 为什么我远程连接sql server总不成功呢?我已经把1433端口开了啊
- 20071206141114这种VARCHAR的如何转换成日期型的2007-12-06 14:14:14
- 请问怎样把一个表的整型转换成别一个表的日期?谢谢
---------
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启用了选项开关 再次运行应该就会有这个提示
exec dbo.sp_fulltext_table @tabname=N'[dbo].[NewsLetters]', @action=N'start_change_tracking'
or
....@action=N'start_background_updateindex'
就会有这个错误
select @objid = object_id('NewsLetters', 'local')
select ObjectProperty(@objid, 'TableFulltextChangeTrackingOn'),
ObjectProperty(@objid, 'TableFulltextBackgroundUpdateIndexOn')可以以此来判断在执行
执行第一便
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 就无法使用了,他这里只是一个警告而已,对我的全文检索没有影响?(我在实践中好像是没有什么影响的,就是担心会有,所以才问问的)
默认的是多少?
===
text in row supports the TEXTPTR, WRITETEXT, UPDATETEXT, and READTEXT functions.
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不能自动跟踪到
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
gocreate 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
goexec p1
go
exec p2
go--观察...\Program Files\Microsoft SQL Server\MSSQL$CRM\FTDATA\SQL______\Build\Indexer\NlFiles文件的变化情况