SQL Server Performance Forum – Threads Archive
c0000005 EXCEPTION_ACCESS_VIOLATION
Hi, I have some sql code that gets kicked off from a visual basic program each night. This sometimes works but it seems after a while i get an error in the Log. See below: ERRROR:2005-04-24 13:13:17.17 spid1 LazyWriter: Process 1 generated an access violation. SQL Server is terminating this process.
2005-04-24 14:34:00.56 spid51 Using ‘sqlimage.dll’ version ‘4.0.5’
Stack Dump being sent to C
rogram FilesMicrosoft SQL ServerMSSQLlogSQL00032.dmp2005-04-24 14:34:00.62 spid51 Error: 0, Severity: 19, State: 0
2005-04-24 14:34:00.62 spid51 SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
*******************************************************************************
*
* BEGIN STACK DUMP:
* 04/24/05 14:34:00 spid 51
*
* Exception Address = 0040AB4D (CSlotPageMgr::ReleaseAll(class CFreePageMgr *) + 0000000F Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 08000008
* Input Buffer 2026 bytes –
SQL:
delete from agg_plu_date_store_trans insert into agg_plu_date_store_tr
ans (time_index,store_index, primary_plu_index,trans_type_index,units,va
lue) select date_key, store_key, primary_plu_key, trans_type_key, count
(primary_Plu_key), sum(selling_price) From fact_sales, receipt Where r
eceipt_key = receipt_index group by date_key, store_key, primary_plu_ke
y,trans_type_key order by date_key Update dbo.agg_plu_date_store_trans
Set xVAT = Value where primary_plu_index in (select primary_plu.pri
mary_plu_index From product, primary_plu, tax_code Where product.vat_c
ode_key = tax_code.tax_code_index and primary_plu.product_code_key = pr
oduct.product_index and tax_code.tax_code = ‘0’) update dbo.agg_plu_d
ate_store_trans Set xVAT = Value * 0.8510638 where primary_plu_index
in (select primary_plu.primary_plu_index From product, primary_plu, ta
x_code Where product.vat_code_key = tax_code.tax_code_index and primar
y_plu.product_code_key = product.product_index and tax_code.tax_code =
‘1’)
If anyone could shed some light on this i would be very gratefull. Thanks Dave
ALSO: This is running on SQL server 2000 with service pack3. The table is only updated with the SQL statement. It is only used then for reading. Thanks Dave
Hi dave, do u run this sql query in just one command from VB or u have bundled
all the queries and posted here.[?]
If yes then try this query in QA with batches.
Separate each batch with Go statement.
quote:Originally posted by izeko2004
SQL:
delete from agg_plu_date_store_trans insert into agg_plu_date_store_tr
ans (time_index,store_index, primary_plu_index,trans_type_index,units,va
lue) select date_key, store_key, primary_plu_key, trans_type_key, count
(primary_Plu_key), sum(selling_price) From fact_sales, receipt Where r
eceipt_key = receipt_index group by date_key, store_key, primary_plu_ke
y,trans_type_key order by date_key Update dbo.agg_plu_date_store_trans
Set xVAT = Value where primary_plu_index in (select primary_plu.pri
mary_plu_index From product, primary_plu, tax_code Where product.vat_c
ode_key = tax_code.tax_code_index and primary_plu.product_code_key = pr
oduct.product_index and tax_code.tax_code = ‘0’) update dbo.agg_plu_d
ate_store_trans Set xVAT = Value * 0.8510638 where primary_plu_index
in (select primary_plu.primary_plu_index From product, primary_plu, ta
x_code Where product.vat_code_key = tax_code.tax_code_index and primar
y_plu.product_code_key = product.product_index and tax_code.tax_code =
‘1’)
SQL:
delete from agg_plu_date_store_trans insert into agg_plu_date_store_tr
ans (time_index,store_index, primary_plu_index,trans_type_index,units,va
lue) select date_key, store_key, primary_plu_key, trans_type_key, count
(primary_Plu_key), sum(selling_price) From fact_sales, receipt Where r
eceipt_key = receipt_index group by date_key, store_key, primary_plu_ke
y,trans_type_key order by date_key Update dbo.agg_plu_date_store_trans
Set xVAT = Value where primary_plu_index in (select primary_plu.pri
mary_plu_index From product, primary_plu, tax_code Where product.vat_c
ode_key = tax_code.tax_code_index and primary_plu.product_code_key = pr
oduct.product_index and tax_code.tax_code = ‘0’) update dbo.agg_plu_d
ate_store_trans Set xVAT = Value * 0.8510638 where primary_plu_index
in (select primary_plu.primary_plu_index From product, primary_plu, ta
x_code Where product.vat_code_key = tax_code.tax_code_index and primar
y_plu.product_code_key = product.product_index and tax_code.tax_code =
‘1’)
I feel this is a memory exception.
The code u r running is creating situation which is unrecoverable.
Hi, Thanks for your speedy reply. Yes this statement is kicked off as is from VB with the execute command. Do you think creating a sotred_procedure would be better? Dave
Check:
http://www.mssqlserver.com/faq/troubleshooting-av.asp
I feel u have the answer.
Simply create a stored procedure and call it with execute statement in VB.
create procedure exec_allsql()
As
set nocount on
delete from agg_plu_date_store_trans
go
insert into agg_plu_date_store_tr
ans (time_index,store_index, primary_plu_index,trans_type_index,units,va
lue) select date_key, store_key, primary_plu_key, trans_type_key, count
(primary_Plu_key), sum(selling_price) From fact_sales, receipt Where r
eceipt_key = receipt_index group by date_key, store_key, primary_plu_ke
y,trans_type_key order by date_key
go
Update dbo.agg_plu_date_store_trans
Set xVAT = Value where primary_plu_index in (select primary_plu.pri
mary_plu_index From product, primary_plu, tax_code Where product.vat_c
ode_key = tax_code.tax_code_index and primary_plu.product_code_key = pr
oduct.product_index and tax_code.tax_code = ‘0’)
go
update dbo.agg_plu_d
ate_store_trans Set xVAT = Value * 0.8510638 where primary_plu_index
in (select primary_plu.primary_plu_index From product, primary_plu, ta
x_code Where product.vat_code_key = tax_code.tax_code_index and primar
y_plu.product_code_key = product.product_index and tax_code.tax_code =
‘1’)
set nocount off
go then from vb connectionobject.execute("exec_allsql"),,adCmdStoredProc
Thankyou very much for your time, i will now go away and try this. Cheers Dave
Hi, I now kick the sql off from a stored procedure, but have just got another error.
This error is for the same bit of sql. Any Ideas. 2005-04-29 10:12:57.60 spid51 Using ‘sqlimage.dll’ version ‘4.0.5’
Stack Dump being sent to C
rogram FilesMicrosoft SQL ServerMSSQLlogSQL00035.dmp2005-04-29 10:12:57.89 spid51 Error: 0, Severity: 19, State: 0
2005-04-29 10:12:57.89 spid51 SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
*******************************************************************************
*
* BEGIN STACK DUMP:
* 04/29/05 10:12:57 spid 51
*
* Exception Address = 00431F15 (CIhashBkt::Unlink(class CCacheObject *) + 0000001C Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred writing address 65B1C0C0
* Input Buffer 2026 bytes –
Hi dave ,
have u checked the link provided by argyle.
the link has got answer for q.
checkDB
]]>