Prev: Creating parameterized query in SSIS package
Next: Check Reporting Services Installation Programmitcally
From: John Carlson on 28 Jul 2008 23:19
Erland

I had the same issue.

It is important to note that the numeric(38,0) field in question is a
computed field. If you change this to a static numeric(38,0) field, and
populate the field with the exact same number that would be computed (which
is indeed in range), then the problem goes away. Also, if you delete the
index on that numeric field, the problem goes away.

It appears to be some quirky DBCC bug.

Below is the SQL you can use to re-create the problem.

use tempdb
GO

CREATE TABLE [dbo].[ObsTest] (
[ObsTime] [datetime] NOT NULL ,
[SeID] [int] NULL ,
[SoID] [int] NOT NULL ,
[TID] [int] NOT NULL ,
[OID] [int] NULL ,
[CSumID] AS (100000000000000000000000000000000 *
convert(numeric(6),(convert(int,((convert(numeric(7,2),[ObsTime],126) -
35000.00) * 100.0)) % 1000000)) + 1000000000000000000000000.0 * ([SoID] %
100000000) + 10000000000000000.0 * ([TID] % 100000000) + 1000000000.0 *
([SeID] % 10000000) + 1.0 * ([OID] % 10000))
) ON [PRIMARY]
GO

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

CREATE INDEX [Obs_AK1] ON [dbo].[ObsTest]([CSumID]) ON [PRIMARY]
GO

insert ObsTest (ObsTime, SeID, SoID, TID, OID)
values('4/26/2008 1:00:00 AM',500443,1806,2647,10022)
go

dbcc checktable (ObsTest)
GO

"Erland Sommarskog" wrote:

> Marlacoba (Marlacoba(a)discussions.microsoft.com) writes:
> >> Would it be possible to post the CREATE TABLE statement and
> >> CREATE INDEX statements for the table?
> >
> > It's a vendor solution I don't want to violate our NDA. I can provide this
> > information to MSFT. I was hoping one of the DBCC team members would
> > recognize the error. I still owe you results from restoring to 2005.
>
> Too bad. Looks like you will have to open a case with Microsoft. Which,
> assuming that this is a corruption, and not a bug, will cost you an
> arm and a leg.
>
> Feel free to mail me, and continue this thread offline, if that path
> is OK with you.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
From: Erland Sommarskog on 29 Jul 2008 06:36
John Carlson (JohnCarlson(a)discussions.microsoft.com) writes:
> It is important to note that the numeric(38,0) field in question is a
> computed field. If you change this to a static numeric(38,0) field, and
> populate the field with the exact same number that would be computed
> (which is indeed in range), then the problem goes away. Also, if you
> delete the index on that numeric field, the problem goes away.
>
> It appears to be some quirky DBCC bug.

Thanks for the repro, John! It seems that the error only appears on SQL
2000. I don't get any error on SQL 2005 or SQL 2008.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Marlacoba on 29 Jul 2008 18:25


"John Carlson" wrote:

> I had the same issue.
Thanks John, Importing the table into SQL 2005 revealed the data was not
corrupted.
I'll just need to convince the vendor to upgrade.

Dan

First  |  Prev  | 
Pages: 1 2 3 4
Prev: Creating parameterized query in SSIS package
Next: Check Reporting Services Installation Programmitcally