FYI: AV caused by calculated field + index thereon in MSSQL2000  
Author Message
Starboykb





PostPosted: Top

SQL Server Developer >> FYI: AV caused by calculated field + index thereon in MSSQL2000

Win2k SP4, SQLserver 2k ver 8.00.760

The following causes an AV ('ODBC: Msg 0, Level 19, State
1//SqlDumpExceptionHandler: Process 59 generated fatal exception
c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this
process.//Connection Broken').

Script to reproduce (somewhat munged from original. For which I was
not responsible):


CREATE TABLE dbo.icj2 (
oid int IDENTITY (1, 1) NOT NULL ,
error_msg text NULL
CONSTRAINT PK_icj2 PRIMARY KEY CLUSTERED
(
oid
) WITH FILLFACTOR = 90
)
GO

alter table icj2 add error_msg_to_use as
(
case when
(icj2.error_msg like 'guggle%' OR
icj2.error_msg like 'goggle%')
then 'y' else 'n' end
)


create index IX_error_msg_to_use
on icj2(error_msg_to_use)


insert into icj2(error_msg)
values (null)

The 'insert into' kills it, but only when there's an index present.

Tried casting the error_msg to a varchar before like(something like:
...cast(icj2.error_msg as varchar(8000)) like 'guggle%'...).
After that I could manually insert and get without AV, so apparently
no problem, but programmatically it would fail by not inserting data
into the table (via an insert statement). If the index was dropped,
programmatic inserts would start working, if index recreated it would
start not inserting again. No errors showed up on profiler.

If anyone's interested I can try to find what components we're using
for interfacing via code to sql server.

cheers

jan

SQL Server259