• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

Internal SQL Server Error on Delete, Worked for Months

Hi There,

This delete statement is suddenly generating this error "Server: Msg 8624, Level 16, State 1, Line 1
Internal SQL Server error.". It has been working for months, though we did play with schemabinding about 3 weeks agao.. Any Ideas are appreciated. This is on a SQL Server SP 4 Build 8.00.2040:

delete from mainstuff.eval_payload_by_store2 where not exists (
 select 1 from (
  select * from mainstuff.v_eval_payload_by_store2_not2006p1
  union all
  select * from mainstuff.v_eval_payload_by_store2_2006p1
  ) A
  inner join mainstuff.eval_main D on D.eval_store_id = A.eval_store_id
 where mainstuff.eval_payload_by_store2.store_number = D.store_number
  and mainstuff.eval_payload_by_store2.form_type = D.form_type
  and mainstuff.eval_payload_by_store2.operating_year = D.operating_year
  and ((mainstuff.eval_payload_by_store2.date_period = D.date_period)
   or (mainstuff.eval_payload_by_store2.date_period is null and D.date_period is null))
  and ((mainstuff.eval_payload_by_store2.time_period = D.time_period)
   or (mainstuff.eval_payload_by_store2.time_period is null and D.time_period is null))
  and mainstuff.eval_payload_by_store2.attribute_index = A.attribute_index

Here is the code for the views:

CREATE    VIEW mainstuff.v_eval_payload_by_store2_2006p1
--WITH SCHEMABINDING
AS
SELECT t.eval_store_id,
      a.attribute_index,
    --COUNT_BIG(*) AS group_count,
    1 AS group_included, -- only count existance of items rather than their weight
    (CASE WHEN p.attribute_value IS NULL THEN 1 ELSE 0 END) AS group_score
FROM       
      mainstuff.eval_main t
INNER JOIN
      mainstuff.eval_payload p on p.eval_store_id = t.eval_store_id
INNER JOIN
      mainstuff.look_store_list ls ON ls.store_number = t.store_number
INNER JOIN
      mainstuff.look_province lp ON lp.province_cd = ls.province_cd
INNER JOIN
      mainstuff.look_eval_attributes_2006_period1 a
      ON a.attribute_index = p.attribute_index and a.operating_year = t.operating_year and a.form_type = t.form_type
WHERE (attribute_value is null OR attribute_value IN ('U','PU'))
      AND t.status_cd = 'A'
      AND (t.operating_year=2006 AND t.date_period=1 AND t.form_type IN ('AF_TRAD','AF_NON_TRAD')) )

CREATE   VIEW mainstuff.v_eval_payload_by_store2_not2006p1
--WITH SCHEMABINDING
AS
SELECT t.eval_store_id,
      a.attribute_index,
    --COUNT_BIG(*) AS group_count,
    1 AS group_included, -- only count existance of items rather than their weight, no category needed
    (CASE WHEN p.attribute_value IS NULL THEN 1 ELSE 0 END) AS group_score
FROM       
      mainstuff.eval_main t
INNER JOIN
      mainstuff.eval_payload p on p.eval_store_id = t.eval_store_id
INNER JOIN
      mainstuff.look_store_list ls ON ls.store_number = t.store_number
INNER JOIN
      mainstuff.look_province lp ON lp.province_cd = ls.province_cd
INNER JOIN
      mainstuff.look_eval_attributes a
      ON a.attribute_index = p.attribute_index and a.operating_year = t.operating_year and a.form_type = t.form_type
      AND ( a.country_cd IS NULL OR a.country_cd = lp.country_cd )
WHERE (attribute_value is null OR attribute_value IN ('U','PU'))
      AND t.status_cd = 'A'
      AND NOT (t.operating_year=2006 AND t.date_period=1 AND t.form_type IN ('AF_TRAD','AF_NON_TRAD'))




0
Primedius
Asked:
Primedius
  • 5
  • 4
1 Solution
 
imran_fastCommented:
Apply latest service pack of sql 2000

this article is from microsoft

CAUSE
This error can only occur when a member table of an indexed view is updated or deleted and there is a DRI CASCADE constraint between the table and other tables in the view. Check to see if the table is defined with either a cascading delete or update constraint. Then check to see if there is a view with an index that depends on the tables.

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 the Microsoft Knowledge Base:
290211 (http://www.kbAlertz.com/Feedback.aspx?kbNumber=290211/EN-US/) INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND
To work around this problem, remove the DRI CASCADE constraint or the index on the view.
Back to the top

STATUS
Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.
0
 
imran_fastCommented:
0
 
PrimediusAuthor Commented:
Hi Imran,

I have seen both of these articles. The first was fixed in service pack 4, which we have installed and the second does not apply to us as we are not using JOIN hints.

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
imran_fastCommented:
Can you simply recreate the view and then try.
0
 
PrimediusAuthor Commented:
Tried to recreate both views with no luck but deleting the cluster attribute from the table indexes used by the views has solved the problem. Very weird.
0
 
imran_fastCommented:
>>but deleting the cluster attribute

You mean cascade attribute
0
 
PrimediusAuthor Commented:
No.Maybe attibute removal was the wrong expression. I deleted the cluster indexes on the tables invloved and recreated them as non-cluster indexes and everything started working fine.
0
 
imran_fastCommented:
Huh..

Do you have primray key on your machine and if yes what is its type is it clustered or nonclustered ?

Try recreating the clustered index... and see if that works.
0
 
PrimediusAuthor Commented:
I am afraid this is a production server and I cannnot experiment on it at the moment. Thanks for your input. I am assigning you the points as a thank you.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now