SQL Server Q&A

Symptoms
Under rare conditions where connections concurrently modify and read data on a particular page, a query that performs a range scan on the page may return rows that do not qualify for the specified range. The end result is that a query may return extra rows that should not qualify for the query.
This problem may affect the distribution agents in transactional replication. When the Distribution Agent receives unexpected rows while querying the MSrepl_commands table it causes an access violation (AV) exception in Distrib.exe that shuts down the agent. The following text may appear in the ReplExceptDist.log:

Microsoft SQL Server Replication: Exception Stack Dump…An exception was caughtin DISTRIB.EXE* Exception Address = 7801166c* Exception Code = c0000005********************************************************Module Name Return Address LocationMSVCRT 00403a45 wcsncpy + 14distrib 0040104c 00403a45distrib 77e97d08 0040104cKERNEL32 00000000
Resolution
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
290211?(http://support.microsoft.com/kb/290211/EN-US/) INF: How to Obtain the Latest SQL Server 2000 Service Pack
HotfixNOTE: The following hotfix was created prior to Microsoft SQL Server 2000 Service Pack 2. The English version of this fix should have the following file attributes or later:

VersionFile nameDescription————————————————8.00.468Sqlservr.exeSP1-based hotfix Hotfix build 468 is designed to be applied to a server that is running SQL Server 2000 Service Pack 1.
NOTE: Due to file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.

Symptoms
Any string that is longer than 8000 bytes that is given as a second parameter to the xp_logevent stored procedure, causes the following error message to occur:

ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
ODBC: Msg 0, Level 20, State 1
Stored function ‘xp_logevent’ in the library ‘xplog70.dll’ generated an
access violation. SQL Server is terminating process 51.
Resolution
To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
290211?(http://support.microsoft.com/kb/290211/EN-US/) INF: How to Obtain the Latest SQL Server 2000 Service Pack

Symptoms
When you execute a statement in which an EXISTS clause contains a FULL OUTER JOIN, an access violation may be raised, and you may receive the following error message:

SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.Connection BrokenThe SQL Server error log may contain an error similar to the following:

********************************************************************************* BEGIN STACK DUMP:*02/28/01 21:58:41 spid 51**Exception Address = 0055A73F (COptExpr::DeriveGroupProperties(unsigned long) + 00000016 Line 0+00000000)*Exception Code= c0000005 EXCEPTION_ACCESS_VIOLATION*Access Violation occurred reading address 00000014
Resolution
To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
290211?(http://support.microsoft.com/kb/290211/EN-US/) INF: How to Obtain the Latest SQL Server 2000 Service Pack

Symptoms
If a transformation failure function returns DTSTransformstat_OK, the errors are logged to the transformation exception log and the transformation exception source log files.
NOTE: The DTS package itself ignores the errors and the Max Error Count value is not incremented.
Resolution
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
290211?(http://support.microsoft.com/kb/290211/EN-US/) INF: How to Obtain the Latest SQL Server 2000 Service Pack

Symptoms
When you use transactional replication in a republishing topology, the Distribution Agent may experience an Access Violation (AV), which leads to an abnormal termination of the Distribution Agent.
When the Access Violation occurs on a server that is running Microsoft SQL Server 2000 Service Pack 2, the following stack dump is generated:

*******************************************************An exception was caughtin DISTRIB.EXE* Exception Address = 7800209a* Exception Code = c0000005********************************************************Module Name Return Address LocationMSVCRT 00403a45 wcsncpy + 14distrib 0040104c 00403a45distrib 77e8d326 0040104cKERNEL32 00000000 CreateProcessW + 900*******************************************************
Resolution
With transactional replication, the Logreader Agent invokes the sp_MSadd_replcommands27hp stored procedure to insert the log records from the publisher to the distribution database. The Distribution Agent then takes these log records and distributes them to the subscriber.
In a republishing topology, the sp_MSadd_replcommands27hp stored procedure may incorrectly insert an empty command (0x) in the MSRepl_commands system table in the distribution database. As a result, the Distributor Agent may experience the Access Violation mentioned in the “Symptoms” section when it tries to handle the empty command.
Logreader Agent
The Logreader might send a transaction with no command to distribution, with the intent being to update the MSrepl_transactions system table properly so that the next scan does not repeat the same section of the log.
The Distribution Agent does not expect an empty command; therefore, the Logreader checks the length of “command” in the sp_MSadd_repl_commands27hp stored procedure to make sure that the xact_seqno column is inserted in the MSRepl_transactions system table, but not in the MSRepl_commands system table.
The problem is that the “length checking” logic does not take into account a republishing or bi-directional scenario, where the originator srv and the db fields are not empty. It only checks to see if datalength(@data) > 39 to determine if the command is empty. However, it does not test the actual command data length(@cmd_data_len) before it inserts data in the MSRepl_commands system table.
Distribution Agent
The following data was gathered from the command that was causing the Access Violation:

xact_seqnotypearticle_id originator_id command_id commandcmd_data_len———————————- ———– ———- ————- ———- ————————-0×000770C1000000F6000100×00000000 NULL0×00000001 0×0 The Distribution Agent uses the sp_MSget_repl_commands stored procedure to select the commands that it needs to process. The sp_MSget_repl_commands stored procedure only picks up a command with article_id=0 when all of the following conditions are true: There are no inactive subscriptions.Loopback detection is not turned on for any articles.The number of articles in the MSsubscriptions system table is the same as the number of articles in the MSarticles system table for this particular publication. When the record with article_id=0 is picked up, the command (0x) is passed to the Distribution Agent; however, it is not handled gracefully and the Access Violation occurs.
To summarize, the cause of the problem is that:
The Logreader inserts a command of 0x in the MSRepl_commands system table in the distribution database. -and-
The Distribution Agent picks up commands with article_id=0 from the MSRepl_commands system table in the Distribution database.

Symptoms
The DBCC CHECKDB command may produce an access violation (AV) when run on a database while other users are actively accessing the database.
Resolution
To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
290211?(http://support.microsoft.com/kb/290211/EN-US/) INF: How to Obtain the Latest SQL Server 2000 Service Pack