473,320 Members | 1,600 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

easily repeatable index corruption in SQL Server 2000 (8.00.2050,post-sp4)

One of the developers where I work found that some of his code would
regularly cause index corruption in SQL Server 2000, post-sp4.
@@version is 8.00.2050 and I can repeat this, so I can't (re-)apply
the hotfix for KB article 929440.

I know the code is not a good way to do what he's doing, which I
explained to him. But corrupting the index on the table probably
isn't the right reaction for SQL Server.

A script to recreate the error follows (it's a slightly generic
version of what he was doing). You can create the table in tempdb and
drop it when done. You will get a pile of "Missing or invalid key in
index..." errors from the DBCC CHECKTABLE.

USE tempdb
SET NOCOUNT ON
--select @@version
GO

/* RUN ONCE
CREATE TABLE dbo.Claim_Payment_Simulation (
file_no int NOT NULL,
row_no int NULL,
related_claim_seq int NULL,
claim_id int NULL,
patient_control_no varchar(30) NULL,
patient_id varchar(100) NULL
-- CONSTRAINT XPKHClaim_Payment_Simulation PRIMARY KEY CLUSTERED
(file_no, row_no, related_claim_seq)
)

ALTER TABLE dbo.Claim_Payment_Simulation ADD CONSTRAINT
XPKClaim_Payment_Simulation
UNIQUE CLUSTERED (file_no, row_no, related_claim_seq) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX XIE1Claim_Payment_Simulation ON
dbo.Claim_Payment_Simulation (claim_id)
GO

*/

DECLARE @file_no int, @claim_id int, @count int
SET @file_no = 1
SET @count = 1
WHILE @count < 10000 BEGIN
INSERT dbo.Claim_Payment_Simulation (file_no, related_claim_seq,
patient_control_no)
VALUES (@file_no, @count * 2, CAST (@count * 3 AS varchar (8)) + 'A.'
+ CAST (@count * 4 AS varchar) )

SET @count = @count + 1
END
--DBCC checktable('dbo.Claim_Payment_Simulation')
GO
DECLARE @file_no int, @claim_id int, @count int
SET @file_no = 1
UPDATE dbo.Claim_Payment_Simulation
SET @claim_id =
CASE WHEN CHARINDEX ('.', patient_control_no) = 0
THEN SUBSTRING (patient_control_no, 9, 30)
ELSE SUBSTRING (patient_control_no, CHARINDEX ('.',
patient_control_no) + 1, 30)
END,
claim_id =
CASE WHEN ISNUMERIC (@claim_id) = 1
THEN
CASE WHEN CAST (@claim_id AS bigint) <= 2147483647
THEN @claim_id
ELSE -99999
END
ELSE -88888
END
WHERE file_no = @file_no -- HOWEVER, if the where clause is NOT
included in the update statement, there will be no index corruption

DBCC checktable('dbo.Claim_Payment_Simulation')
GO

TRUNCATE TABLE dbo.Claim_Payment_Simulation
--SELECT * FROM dbo.Claim_Payment_Simulation
--DROP TABLE dbo.Claim_Payment_Simulation
GO
Sep 29 '08 #1
1 3329
(br************@gmail.com) writes:
One of the developers where I work found that some of his code would
regularly cause index corruption in SQL Server 2000, post-sp4.
@@version is 8.00.2050 and I can repeat this, so I can't (re-)apply
the hotfix for KB article 929440.

I know the code is not a good way to do what he's doing, which I
explained to him. But corrupting the index on the table probably
isn't the right reaction for SQL Server.
I was able to reproduce the error with SQL 2000 SP4. I did not happen
on SQL 2005 SP2. I don't know if there is a later build of SQL 2000
which addresses this. But since there appears to be a workaround, and
SQL 2000 has fallen out of mainstream support, I doubt that this will
ever be fixed in SQL 2000.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Sep 29 '08 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Ken Lindner | last post by:
I have a need to become familiar with SQL Server 2000 for work. Needless to say I am new to SQL Server any version, but not IT in general. My employer has provided me with the SQL Server 2000...
4
by: M Wells | last post by:
Hi All, I have a table that currently contains approx. 8 million records. I'm running a SELECT query against this table that in some circumstances is either very quick (ie results returned in...
0
by: Chris Halcrow | last post by:
Hi I've spent ALL DAY trying to re-install SQL Server 2000 on Windows XP. I continually get the error 'cannot configure server' just at the end of the installation. I've tried the following: ...
14
by: uli2003wien | last post by:
Dear group, we are running a SQL-Server Database which is about 30 GB large. The purpose of this database is to contain periodic data from automatic devices which insert values into some tables....
8
by: Woody Splawn | last post by:
I am asking this question here because I asked this question in the Reporting Services Newsgroup and did not get an answer. Does anyone know if Reporting Services is intended to work in a...
6
by: Todd Brewer | last post by:
Windows Server 2000 ASP.NET 2.0 SQL Server 2000 (on a physically seperate server) I moved an ASP.NET 2.0 application from a development server to production, and am getting the following error:...
3
by: 001frien | last post by:
How do i find indexes of the columns of all the tables of the datbase...........most importantly in SQL server 2000 Thanks a lot
2
by: Sam Shaw | last post by:
I have been looking after an MS Access database, using table links to access data in a back-end MDB database. We have recently micrated to a SQL Server 2000 back-end atabase, once again accessing...
3
by: Jaros³aw Kozik | last post by:
my database status is suspect - what can I do to rebuilt it ( using enterprise manager or something else ) ? regards IJKK
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.