Windows Search Not Indexing Contents of Text Files With .SQL Extension

Posted on

QUESTION :

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.

ANSWER :

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

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

ComputerHKEY_CLASSES_ROOT.sql

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

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

Then reindex.

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

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.

Leave a Reply

Your email address will not be published.