EXCEPTION_ACCESS_VIOLATION in SQL Server 2000 (SP3) on multiple CPU system
Now this is a strange one. The following stored procedure crashes the SQL worker process that is executing it:
CREATE PROCEDURE usp_UpdateDeviceEnabled
@idDettaglio int,
@sDevicelist textAS
UPDATE CP_Valori
set CP_Valori.sCPValore = @sDevicelist
FROM
Colonne_Personalizzate INNER JOIN CP_Valori ON Colonne_Personalizzate.idColonna = CP_Valori.idColonna INNER JOIN Dettaglio ON CP_Valori.idDettaglio = Dettaglio.idDettaglio
WHERE
(CP_Valori.idDettaglio = @idDettaglio) AND (Colonne_Personalizzate.sType = 'CMB_DEVICE')
It bombs with the following errors:
Event Type: Error
Event Category: (2)
Event ID: 17052
Description:
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 59 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.Event Type: Error
Event Source: MSSQL$DB
Event Category: (2)
Event ID: 17052
Error: 0, Severity: 19, State: 0
language_exec: Process 59 generated an access violation. SQL Server is terminating this process.
It works on a single CPU system (Win2k3) but bombs on all multiple CPUs systems we tried it on (Win2k, Win2k3). Tired most tricks in the book like recompiling the stored, dbcc, recreating the db on another machine etc without luck.
Most of the EXCEPTION_ACCESS_VIOLATION hits on Google refer to text fields, so we changed
@sDevicelist text
to
@sDevicelist varchar(8000)
And, believe it or not, the stored procedure doesn't bomb anymore.
It explains why we could not reproduce the problem by running the query in query analyzer, as it doesn't support local variables of type text. But it does not explain why it bombs in the first place. Suggestions?
