Finally Recovered

Friday, May 3, 2002

Whew. A couple weeks ago I tried to upgrade a couple key components on this web server, SQL Server and Exchange. They didn't work, and I had a really hard time getting everything back to normal again. Today everything finally is. I've managed to get SQL Server upgraded to 2000, but haven't had the guts yet to give Exchange another try. I might just wimp out, stuff everything into PSTs and install from scratch . . . .

Anyway, two SQL Server-related bugs kept us from 100% for the last couple weeks. Here they are, along with their fixes, for my own record and your benefit (thanks, Google!):

NAT Shared Connection is annoyingly intermittent and slow

I connect my home network through Windows 2000's NAT connection sharing. It's worked fine for years, until SQL Server 2000 came along. Most times web pages would load very slowly, taking forever to even start appearing, and most times either none or few of the images would load, or the page wouldn't load at all.

I happened upon a post about the problem in Microsoft's Routing newsgroup, and here's the easy answer, courtesy of Microsoft's Jeremy Smith:

Delete the following Reg key and reboot:

HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Services \ Tcpip \ Parameters \ ReservedPorts

Cleared the problem right up. Good thing, too, because I was getting resigned to competely rebuilding the server, thinking something had gone wrong with Windows. It wouldn't have helped!

Unexpected NULL value returned (OLE DB error 80040E14)

This one was a tough nut to crack. Nothing much turned up on Google, and the most common solution (rebuild the catalog) didn't help. Here's my posted description of the problem:

I've just upgraded from SQL 7 SP3 to SQL 2000 SP2, and am having trouble with my full-text indexes.

First of all when I upgraded, I had to re-enable the database for full-text. I can add/delete catalogs, enable full-text indexing on the tables I want, and I get an expected Item count (4539) and Unique Key Count.(32696), but I'm suspicious that the Catalog size is only 1 MB.

Anyway, here's my statement:

SELECT n.NewsID AS ID, n.Title, Left(n.NewsBlurb,3000) AS Comments, 'News' AS Source, n.NewsDate AS DateStamp, r.Rank, '/item.asp?ID=' + CAST(n.NewsID AS varchar) AS Link FROM News n INNER JOIN FREETEXTTABLE(News, *, 'FORMSOF(INFLECTIONAL, "david")') r ON n.NewsID = r.[Key] ORDER BY Rank DESC, DateStamp DESC

The full error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC SQL Server Driver][SQL Server]Unexpected NULL value returned for column '[News].KEY' from the OLE DB provider 'Full-text Search Engine'. This column cannot be NULL.

Some more troubleshooting steps I took:

After rebuilding and a full population, I get the same error. 

If I rebuild and don't populate, of course it works "fine", not returning anything.

The Event log reports no errors and nothing skipped. The Gatherer starts its crawl, find documents, and the Indexer reports that "the catalog was not propagated, because no new files were detected for the project." 

I reset the MSSQLServer service to use LocalSystem, then back to the appropriate account as described in http://support.microsoft.com/default.aspx?scid=kb;EN-US;q277549, and rebuilt and populated, again with no success. I don't get any login failure errors. I've tried with Local System and with the SQL Service account. I've seen many messages in the newsgroup about this issues, and I am not having any problems with accounts, as far as I can tell; no login errors, nothing. Just NULL key values.

Enabling/disabling fulltext on the database, rebuilding, repopulating the catalog - nothing worked. Finally got instructions from Hillary Cotter on how to force a reinstall of the Full Text component of SQL Server, and voila! We're back up and running full-text. Aaaaah. Here's the solution:

It gets complex if you have a service pack already installed. Look in c:\winnt\temp for searchsetup.log

run the command that looks like this 2/28/2002 21:16:16 INFO: ParseCommandLine, Command="D:\SQL2000\x86\FullText\MSSearch\Search\SearchStp.exe" /s /a:SQLServer

If that doesn't work,

  1. Load RegEdit.exe
  2. Goto this registry key
    HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Tracking Find the item named E07FDDA7-5A21-11d2-9DAD-00C04F79D434 and
  3. rename it (I put an X in the front of it)
    This tricks SQL server into thinking that the Fulltext service is not installed.
  4. Then load the SQL 2000 install program from the CD and run through it until you get to a dialog that asks if you want to "Add components to your existing installation" and select "Server Components" and the sub- component "Full Text Search" should not be check marked. Please, "check mark" it and click on Next to continue the installation taking all other defaults you previously selected during your initial installation.
    If the sub-component "Full Text Search" is already "check marked", we may need to delete a different Registry tracking key to "re-install" FTS.

If you have an sp installed load your service pack cd.

3 Comments