| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-08-26 : 11:29:30
|
| Anderson writes "Hi...We have a stored procedure that when executed causes the following error:* BEGIN STACK DUMP:* 08/19/02 12:12:29 spid 54** Exception Address = 00428D97 (CSsNumeric::BActualPrec + 0000015F Line 0+00000000)* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION* Access Violation occurred reading address 00000002* Input Buffer 86 bytes -* grpGetSegmento '00990054276','45543915' * This is the proc:CREATE PROCEDURE grpGetSegmento(@Codigo_cliente char(11), @cgc_matriz decimal) as BEGIN SELECT COUNT(DISTINCT r.CODIGO_CLIENTE), S.NM_SEGMENTO FROM grpParticipantes p (nolock), grpGrupos G (nolock), grpCgcs C (nolock), grpRel_cgc_pj R (nolock), grpParticipantes Informantes (nolock), SEGMENTO S (nolock) WHERE P.Codigo_cliente = @Codigo_Cliente AND C.ID_grupo = P.ID_grupo AND C.Cgc_matriz = @cgc_matriz AND C.ID_grupo = G.ID_grupo AND C.ID_cgc = R.ID_cgc AND r.Codigo_Cliente = Informantes.codigo_cliente AND Informantes.fk_id_segmento = s.id_segmento AND Informantes.id_grupo IN(2,9,10,13,21) GROUP BY s.nm_segmentoENDSQL Server Enterprise Edition 8.00.534 (SP2)Windows 2000 - (NT 2195)8 ProcessorsThanks..." |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-08-26 : 11:56:08
|
| You need to post a lot more information than this ...1) Does SQL Server throw an AV every time you run the proc, or just infrequently?2) Did the procedure throw an AV from the moment it was written, or did it work consistently for some point in time and then started having problems?3) Have there been any material changes to the server, such as service pack installations?To give you a WAG, there is a knowledge base article describing possible side effects of using (nolock) I recall reading. Generally you should avoid it. Also I would recommend using the ANSI standard join syntax of table {join} table on {boolean clause} rather than the old-style WHERE clause joins you're using.Jonathan Boott, MCDBA{0} |
 |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-08-26 : 12:05:17
|
| Yes, that's the one, thanks!(nolock) causes many, many problems ... Onamuji you'll save yourself headache down the road by ripping it out of your procs now.Jonathan Boott, MCDBA{0} |
|
|
|
aalima
Starting Member
4 Posts |
Posted - 2002-08-26 : 12:58:55
|
| Thanks everybody for the help. Jonathan, here´s more information:1) Does SQL Server throw an AV every time you run the proc, or just infrequently? Infrequently. In the same server (and others), it runs sometimes.2) Did the procedure throw an AV from the moment it was written, or did it work consistently for some point in time and then started having problems? When I wrote the procedure, it worked well. But after a reinstallation of the database, it began to throw the AV.3) Have there been any material changes to the server, such as service pack installations? Now it´s difficult to say, but in the current database, the last SP avaiable is installed.To give you a WAG, there is a knowledge base article describing possible side effects of using (nolock) I recall reading. Generally you should avoid it. Also I would recommend using the ANSI standard join syntax of table {join} table on {boolean clause} rather than the old-style WHERE clause joins you're using. I´ll change the join sintaxes. About the (nolock) hint, I put it after the problem, in a try to correct the problem.Regards,Anderson |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-08-26 : 14:21:54
|
| lol that's what i found out on friday! luckily i keep all my procs in sourcesafe so i just got them ... updated them ... ran them... saved them back :) was easy to do ... replace all on WITH (NOLOCK) and then replace all (NOLOCK) ... :) since then everything has run well :) |
|
|
|
aalima
Starting Member
4 Posts |
Posted - 2002-08-27 : 06:35:15
|
| Folks,today I tried to do this and it caused the same AV error:"SELECT DISTINCT fk_id_segmento FROM grpParticipantes"This table has only 1100 records and it´s one that is used inthat procedure.Thanks... Anderson |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-08-27 : 08:01:45
|
| have you updated all the latest service packs for win2k?have you run DBCC integrety check on your database?have you tried rebooting the machine?have you checked the EventLog for other information? |
|
|
|
aalima
Starting Member
4 Posts |
Posted - 2002-08-27 : 09:28:46
|
According to the company´s DBA, yes to all questions... quote:
have you updated all the latest service packs for win2k?have you run DBCC integrety check on your database?have you tried rebooting the machine?have you checked the EventLog for other information?
|
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-08-27 : 10:03:06
|
| Does it only happen with distinct?Suggests there might be something wrong with tempdb (if sql server still uses tempdb to sevice distincts and aggregates).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-08-27 : 10:14:25
|
| Have you called Microsoft and given them a copy of the stack dump?Generally, SQL Server only AV's or 8624's when you give it a very complex query, or a query the optimizer has difficulty generating a plan for.Your SELECT DISTINCT query certainly does not qualify - now that you mention this, I suspect your disk subsystem is not on Microsoft's HCL, or potentially you're using network attached storage.And why has the company DBA offloaded this to you?Jonathan Boott, MCDBA{0} |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-08-27 : 10:17:06
|
quote:
Does it only happen with distinct?Suggests there might be something wrong with tempdb (if sql server still uses tempdb to sevice distincts and aggregates).
Couldn't you test that second part by trying it with:OPTION (HASH GROUP)andOPTION (ORDER GROUP)? |
|
|
|
aalima
Starting Member
4 Posts |
Posted - 2002-08-30 : 09:36:14
|
| Folks,after droping and recreating the FOREIGN KEY, the procedureis working well until now....I´d like to thank all of you for the help...Regards,Anderson. |
|
|
|
|
Access Violation