Prev: Creating parameterized query in SSIS package
Next: Check Reporting Services Installation Programmitcally
From: Erland Sommarskog on 25 Jul 2008 17:41
Marlacoba (Marlacoba(a)discussions.microsoft.com) writes:
> "Erland Sommarskog" wrote:
>
>> But does this query complete? That is, do you see a StmtCompleted for it?
> Doesn't get to StmtCompleted the error comes directly after the
> StmtStarting.
>
> SQL:BatchStarting DBCC CHECKTABLE (Observances)
> SQL:StmtStarting DBCC CHECKTABLE (Observances)
> SP:StmtStarting SELECT @BlobEater = CheckIndex(FactKeyA + FactKeyB +
> Facts)
> FROM
> (SELECT TOP 100 PERCENT FactKeyA, FactKeyB, Facts FROM { IRowset
> 0xB8C3943E }
> ORDER BY FactKeyA, FactKeyB) as SortedFacts
> GROUP BY FactKeyA OPTION(ORDER GROUP)
> Exception Error: 8115, Severity: 16, State: 1
> Exception Error: 8115, Severity: 16, State: 1
> Exception Error: 8115, Severity: 16, State: 1
> Exception Error: 8115, Severity: 16, State: 1
> SQL:BatchCompleted DBCC CHECKTABLE (Observances)

I presented this to a person with good insight in DBCC CHECKTABLE,
and he assumed there is an issue of corruption somewhere.

Would it be possible to post the CREATE TABLE statement and
CREATE INDEX statements for the table?

I'm sorry that this is progressing in quite slow pace, but this is not
really a standard situation.


--
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 25 Jul 2008 18:26
> 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.

Dan
From: Erland Sommarskog on 25 Jul 2008 19:14
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: Steve Kass on 28 Jul 2008 15:09
Hi Dan,

As Erland said, this doesn't appear to be a typical situation, so I'm still
guessing a bit. Here are a couple more thoughts to follow up on the numeric
column.

First, is there an index on this table that involves the numeric(38,0)
column? If not, it seems unlikely this is the problem. If there is, what
you've provided so far is at least consistent with the possibility of a
"bad value" scenarios for numeric data. I don't think checking MAX
and MIN on this column is sufficient to rule out a problem.

If you want to pursue this possibility, here are two queries I suggest you
try:

This first one should return no rows. If it does return anything,
you seem to have bad decimal values in your table. The first condition
shows "bad precision/scale information" (at least not the 38/0 you
indicate is the column's precision and scale). The second and
third check the sign bytes, and the last checks the data length.

select top 1
-- TOP, in case I erred and this returns millions of rows
-- include key columns here that identify the row,
cast(numeric_column as varbinary(30)) as bin_d
from T
where substring(cast(numeric_column as varbinary(30)),1,2) <> 0x2600
or substring(cast(numeric_column as varbinary(30)),3,1) <> 0x00
or substring(cast(numeric_column as varbinary(30)),4,1) not in(0x00,0x01)
or datalength(cast(numeric_column as varbinary(30))) > 20;
go

If the query above returns no rows, you might try this one. It
may return NULL, if you have no particularly large values in
your table, but if it does return something, it should return
something no larger than
0x4B3B4CA85A86C47A098A223FFFFFFFFF
in lexicographic order.

select
max(
substring(cast(numeric_column as varbinary(30)),20,1)
+ substring(cast(numeric_column as varbinary(30)),19,1)
+ substring(cast(numeric_column as varbinary(30)),18,1)
+ substring(cast(numeric_column as varbinary(30)),17,1)
+ substring(cast(numeric_column as varbinary(30)),16,1)
+ substring(cast(numeric_column as varbinary(30)),15,1)
+ substring(cast(numeric_column as varbinary(30)),14,1)
+ substring(cast(numeric_column as varbinary(30)),13,1)
+ substring(cast(numeric_column as varbinary(30)),12,1)
+ substring(cast(numeric_column as varbinary(30)),11,1)
+ substring(cast(numeric_column as varbinary(30)),10,1)
+ substring(cast(numeric_column as varbinary(30)),9,1)
+ substring(cast(numeric_column as varbinary(30)),8,1)
+ substring(cast(numeric_column as varbinary(30)),7,1)
+ substring(cast(numeric_column as varbinary(30)),6,1)
+ substring(cast(numeric_column as varbinary(30)),5,1)
) as maxb
from T
where len(numeric_column) >= 20
go

Hope this helps,

SK

Marlacoba wrote:

>There is a numeric with length 17 precision 38 and scale 0.
>The min and max values for that column fit within those constraints.
>
>And just so I'm clear on this, this error occurs from running the DBCC it's
>not an error reported by DBCC. DBCC fails to run! CHECKDB WITH PHYSICAL_ONLY
>does succeed.
>
>I can do a restore on 2005 and use WITH DATA_PURITY.
>
>Dan
>
>
>
From: John Carlson John on 28 Jul 2008 23:04


"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
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Creating parameterized query in SSIS package
Next: Check Reporting Services Installation Programmitcally