Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm having trouble getting Windows Search to index the contents of a text file that have a .sql extension. Is there something I'm doing wrong?

Background:

I use a program at work called Navicat which helps me to work with our MySQL database. When I use Navicat to edit and save queries, it stores them in .sql files which are located in Navicat's directory in Program Files.

Since Windows Search does not index files within Program Files by default, I added it to the list of indexed locations, as shown in the picture below:

enter image description here

I also went into the advanced section and added the .sql file extension and select the Index Properties and File Contents option. This sets the filter description to be Plain Text Filter:

enter image description here

However, whenever I do a search for text that I would expect to find within a .sql file, such as saw_order.Wo type:sql, it does not return any files. The search is successful if I'm searching for part of the name of a file, but never its contents with .sql files.

The .sql files are plain text files that can be easily opened and edited with notepad. Navicat simply adds some intellisense and a organization features, but other than that, it's plain text.

Why can't Windows Search see the contents of these files?

Do I need to add some special plug-in to Windows search for it to recognize these files?

NOTE: I am using Windows Search 4.0 and Windows XP SP3.

share|improve this question
    
See also superuser.com/questions/77772/… –  RJFalconer Dec 31 '09 at 13:37

7 Answers 7

up vote 5 down vote accepted
+100

It seems there is a problem with Windows Search reading the format of the file, even when set in plain text. It is a known problem and has even been brought up here on Superuser!

My best recommendation for now is to use a program that can search the contents of files. It may not be as fast as having them indexed, but hopefully it can get the job done for you.

Four great tools I recommend:

Agent Ransack (freeware):

alt text

NirSoft SearchMyFiles (freeware and portable):

alt text

Windows Grep:

alt text

Portable File Seeker (freeware):

alt text

share|improve this answer
    
Thanks for the suggestions. I'd really prefer an integrated solution that fixes Windows Desktop Search, but perhaps I'll look at some of the software you recommended. –  Ben McCormack Dec 28 '09 at 14:05
    
Though I would have preferred to just fix Windows Desktop search, I really appreciate the time you put into providing a detailed answer. I'll have to check out some of those tools. Much thanks! –  Ben McCormack Jan 1 '10 at 19:42
    
You're most welcome! –  John T Jan 1 '10 at 20:36

Make sure that you have the following keys in your registry, at this location:

Computer\HKEY_CLASSES_ROOT\.sql

[HKEY_CLASSES_ROOT\.sql]
"Content Type"="text/plain"
"PerceivedType"="text"

[HKEY_CLASSES_ROOT\.sql\PersistentHandler]
@="{5E941D80-BF96-11CD-B579-08002B30BFEB}"

Then reindex.

share|improve this answer

In case there is a problem with the indexing itself, I would try telling search to look at the contents of non indexed files.

Go to any Explorer window and press Alt then go to Tools > Folder Options and click the Search tab, tick the circled option:

enter image description here

share|improve this answer
    
I don't believe these options are viewable in this fashion in Windows XP (unless it was disabled on my PC by Group Policy, but I doubt it). –  Ben McCormack Dec 28 '09 at 14:06

I can only think of two things:

  1. The files haven't themselves been indexed yet (so they show up in a filename search bit not a content search)

  2. I also suggest a test... try putting some SQL files outside your Program Files folder and include that folder in the indexed locations list. Maybe there's a permission issue on the folders in Program Files?

share|improve this answer
    
Thanks for the suggestions. The file have been around for a long time and so has Windows Search on my PC. I'm going to rebuild the entire index and see if that helps. –  Ben McCormack Dec 17 '09 at 21:44
    
That didn't work :/. Indexing is complete and it's still not finding any matches. –  Ben McCormack Dec 18 '09 at 18:39

You may have some luck with the answers to windows-desktop-search-fails-to-search-inside-sql-files.

share|improve this answer

The article Windows XP Search Bug states:

Windows XP does not search all file to enhance the performance of searching and to avoid extraneous results. This behavior occurs whether or not you use the Indexing service when you search. Therefore, Windows XP has a known issue for not finding a number of File Types when you do a Search for Files "containing text" or using the "A word or phrase in the file" option.

This can be remedied for many file types, but not all. The cause is the lack of a PersistentHandler value in the Registry for this type of file.

Method #1

Download this VB Script (Written by Doug Knox). Save the file you just downloaded to your hard disk. Locate the file you just saved and double click it. Enter the file extension you wish to modify. If the PersistentHandler sub-key exists, no changes will be made. If it does not exist for the file type in question, the Registry will be updated accordingly. Reboot when you're finished with the file types you wish to change.

Method #2

MS KB 309173 has another fix for this bug. To configure Windows XP to search all files no matter what the file type, obtain the latest service pack for Windows XP (currently SP2) and then turn on the Index file types with unknown extensions option.

If you use this method, Windows XP searches all file types for the text that you specify.

This can affect the performance of the search functionality. To do this, see directions in the above article.

share|improve this answer
    
I appreciate your link to the article, but neither of the methods seemed to work. I ran the VB Script and the key already existed. I changed the registry value per the suggestion in method #2 and then re-indexed my folders, and it still isn't working. Perhaps what you are referring to is "Windows Search" instead of "Windows Desktop Search," which was developed for Vista but released for XP as well prior to Vista's release. –  Ben McCormack Jan 1 '10 at 19:18
    
I believe that the links I gave are in essence correct, in that the PersistentHandler for the .sql extension is wrong and needs to be replaced. However, much more research is required to nail it down. –  harrymc Jan 1 '10 at 19:44

Many search engines need some kind of module, frequently called a "connector" to figure out how to interpret a file.

I note that in the screenshot, the SQL file is listed as "Plain Text". Even though the file is just plain text, Windows Search may expect the .sql file to be something other than text and simply ignores it.

To test the hypothesis, make a copy of the file, change the extension to ".txt", and then force a re-index.

Something else to check, perhaps with a more powerful text editor (my favorite is gVim - http://www.vim.org/download.php#pc) to see if the initial "magic number" of the file is different from a normal text file.

share|improve this answer
    
I believe SharePoint uses something called an iFilter and WDS might use the same thing, though I'm not sure. I tried out gVim per your recommendation. From what I could tell, nothing seemed different in the .sql files vs. the .txt files. However, I maybe didn't look at something correctly. –  Ben McCormack Jan 1 '10 at 19:40
    
Ben McCormack, did you try the experiment I suggested? –  pcapademic Jan 2 '10 at 5:02

protected by harrymc Jan 1 at 15:23

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.