sql server mseq: Hi,

I am getting an "8624 - Internal SQL Server Error" on SQL Server 2000
SP3 when inserting into a table which is used as the source of an
indexed view. Without the indexed view being defined, the INSERTs work
fine.

I believe this is a bug in SQL Server. How can it be reported to
Microsoft?

A reproduce script is available at
http://www.iips.mq.edu.au/SQL_Server_Bug.txt

Regards
Simon Kissane
Web Developer/Programmer
Internet Information Projects & Services

It seems like the reference constraint on the column BasedOn is being
flakey. I can report it to MS through the private newsgroups and see if this
is a bug & post the status back to you.

--
Anith

Simon,

I picked this up when Anith Sen passed this on to the private
newsgroup. Based on the stripped-down repro below, Microsoft says that
it is a bug (SQL Server 2000 bug #469636).

If you don't have a satisfactory workaround and are seriously affected
by this bug, you can pursue the possibility of getting a hotfix from
Microsoft. (No hotfix is presently available, so they would have to
create one.) If you would like to do that, I can put you in touch with
the person who looked into this. Just let me know. Given that your
e-mail address is in Australia, it may easier for you to open a case
with local Microsoft product support and refer to the bug number.

I don't speak for Microsoft officially, but if you pursue a product
support case about a documented bug, I believe Microsoft will generally
waive the usual product support fees. Hotfixes are generally released
with less comprehensive testing than are service packs, and this bug
will most likely be fixed in either the next SQL Server 2000 service
pack or the next SQL Server major release (Yukon).

Here's the simplified repro. The error seems to require an indexed view
on a table that includes a foreign key reference to the same table.

set
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,
ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS
on
GO

set NUMERIC_ROUNDABORT OFF
GO

CREATE DATABASE IndexViewBug COLLATE Latin1_General_CI_AS
GO

use IndexViewBug
GO

CREATE TABLE T (
i int not null primary key,
x int,
j int null,
)
GO

ALTER TABLE T ADD CONSTRAINT FK_T_j
FOREIGN KEY (j) REFERENCES T(i)
GO

CREATE VIEW V WITH SCHEMABINDING AS
SELECT
x,
COUNT_BIG(*) As n
FROM dbo.T
GROUP BY x
GO

CREATE UNIQUE CLUSTERED INDEX V_uci ON V(x)
GO

INSERT INTO T (i,j) VALUES (3,NULL)
go

-- This fails
INSERT INTO T (i,j) VALUES (4,3)
GO

use master
go

DROP DATABASE IndexViewBug


Steve Kass
Drew University

[quoted text, click to view]