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?
Ciao Egil, sono un tuo ex-collega di Iris. Ho incontrato anche io un problema del genere e sembra (guardando i log di SQL) che ci sia un limite alla lunghezza della query che si può eseguire. Sembra che sia proprio il parser della query che dà problemi quando deve gestire stringhe che rappresentano comandi SQL troppo lunghe (il campo text, infatti ha una dimensione maggiore rispetto ad un varchar(8000)). Dal log di SQL riesci anche a vedere a che punto della select si interrompe. Io ho risolto spezzando la query in modo che l'istruzione da eseguire fosse una stringa più corta. Non so se questa risposta sia sufficiente ma spero di si. Ciao. Matteo
ReplyDeletePS: scusami se non ho scritto in inglese ;-)
Hi Matteo.
ReplyDeleteGood idea, but I am not sure if that was the problem in my case. The query stayed the same, and the data in the text parameter was short (<100 char) so it's strange if the problem was the max length of the query. If it was a "max length" problem, I would have expected to see the same problem on a single CPU as well but it only happened on systems with 2 or more CPUs.
I guess it could be a problem with the update of the text field in a "complex query" with the "text in row option" enabled. We used the "text in row" option to keep a 100 or so characters of the (usually) short string in the table with the data. I did not try removing the option so I am not sure if that was the cause or not.
BTW: I lost your contact info so please send me your mail or web address via the contact form if you want to stay in touch
Interesting.
ReplyDeleteI have never modified the parallelism of queries before. I tried the workaround described in the knowledge base article and added "OPTION (MAXDOP 1)" to the query:
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')
OPTION (MAXDOP 1)
It still bombs. The only way I have found is to change "text" to "varchar(8000)".
More suggestions anyone?
The problem may be related but the KB article only talks about a SQL Mail problem:
ReplyDelete"When you try to use SQL Mail on a multiprocessor computer, SQL Mail may not call the MAPIInitialize function and the MAPIUninitialize function for every thread that uses MAPI API calls. Therefore, you may receive an access violation exception."
Thanks for the tip anyway!
I am getting this error when i tried to insert row into DB. I am using Sql Server 2000.
ReplyDeletePlease any one can help me.
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.
Hi,
ReplyDeletei have the same problem when i tried to modify a table. Using SQL server 2000, too. Is there any solution for this problem?
regards,
TiCar