Procedure to resolve the problem on SQL Servers running on Windows Server 2008 R2 and Windows 7 environments:
Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base: 322756 (http://support.microsoft.com/kb/322756/ ) How to back up and restore the registry in Windows
Note: You need to restart SQL Server Service after going through the following procedure for the changes to come into effect.
- Navigate to the following registry hive on your SQL Server machine and save it as SQLMSSearch.reg.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\MSSearch\CLSID
Note: Replace the "<instance>" in the path above with the relevant instance ID of SQL Server 2008/SQL Server 2008 R2.
E.g. "MSQL10.MSSQLSERVER" for a default instance or "MSSQL10.KATMAI" for a named instance "Katmai"
For further information refer to the following topic in SQL Server Books Online:
File Locations for Default and Named Instances of SQL Server (http://msdn.microsoft.com/en-us/library/ms143547.aspx)
- Edit SQLMSSearch.reg file with notepad and replace all occurrences of xmlfilt.dll
by C:\\Windows\\system32\\xmlfilter.dll and then save the changes.
- This assumes your windows folder is located at C:\Windows.
- You need to enter each backslash in the new path twice!
- Click the file SQLMSSearch.reg to import the content into the registry.
- Execute the following T-SQL commands to enable the new setting in SQL Server
exec sp_fulltext_service 'load_os_resources', 1
exec sp_fulltext_service 'verify_signature ', 0 exec sp_fulltext_service 'update_languages'exec sp_fulltext_service 'restart_all_fdhosts'
- Check the availability of the new settings with this T-SQL command:Notes: You need to adapt the above command to suit the correct Windows system path on your system. Also ensure that all the languages (respectively their lcid), that should show the new behaviour are listed in the column "componentname" in the output
EXEC sp_help_fulltext_system_components 'fullpath','c:\windows\system32\xmlfilter.dll'
- Create a simple table as follows:
CREATE TABLE [dbo].[tesFTS](
[idField] [int] PRIMARY KEY,
[xmlField] [xml] NOT NULL)
- Enable FTS on the xmlField field (the word breaking language that you select here is not relevant to the repro)
- Insert one record on that table, as follows:
insert into testFTS (idField, xmlField) values
(1, '<TopNode TopFirstAtt="TopAttValue" TopSecondAtt="Second Value">
<MiddleNode FirstAtt="ValueOne" SecondAtt="ValueTwo" ThirdAtt="ValueThree">
<MiddleNode FirstAtt="ValueFour" SecondAtt="ValueFive" ThirdAtt="ValueSix">
<BottomNode BottomAtt="OtherBottomValue" />
<MiddleNode FirstAtt="ValueSeven" SecondAtt="ValueEight" ThirdAtt="ValueNine">
<BottomNode BottomAtt="LastValue" />
<NodeWithValue>Testing whether this value will be indexed</NodeWithValue>
- By querying fts_index_keywords for this particular table - select * from sys.dm_fts_index_keywords(db_id(),object_id('testFTS')), the following results are obtained:These display terms appear to be restrict to:
keyword display_term column_id document_count 0x0069006E00640065007800650064 indexed 2 1 0x0069006E006E00650072006D006F00730074 innermost 2 1 0x007300650063006F006E0064 second 2 1 0x00740065007300740069006E0067 testing 2 1 0x0074006500780074 text 2 1 0x0074006F007000610074007400760061006C00750065 topattvalue 2 1 0x00760061006C00750065 value 2 1 0x0077006800650074006800650072 whether 2 1 0xFF END OF FILE 2 1
- Node values, regardless of how deeply they are nested within the XML document.
- Attribute values only for the top node and not from any of the inner nodes that would be expected.
Raksta ID: 2513181. Pēdējo reizi pārskatīts: 2011. gada 13. jūl.. Pārskatījums: 1