Error: 644, Severity: 21, State: 6

Hi All

Ignore the error below, It's got to do with isolation levels (nolock in this case): refer http://support.microsoft.com/?kbid=834290 for details.

Error: 644, Severity: 21, State: 6
Could not find the index entry for RID '1631393920202020202020202020202033392020202020202 020202020202019bcd3113230303037333120202020202020' in index page (1:420134), index ID 0, database 'DBName'..

What I'd like to know is how to get the table name that was affected out of this error message. Do I query sysindexes, sysobjects, use a function or something else?

Thanks in advance.

GrahamNo need to answer the question:

dbcc traceon(3604)
dbcc page ('DBName', 1, 420134, 0)
select name from sysobjects where id =1141579105--m_objId from page header
dbcc traceon(3604)
dbcc page ('DBName', 1, 305272,0)
select name from sysobjects where id =1141579105--m_objId from page header
dbcc traceon(3604)
dbcc page ('DBName', 1, 369029, 0)
select name from sysobjects where id =2021582240--m_objId from page header

/*
TABLE A: DBCC PAGE Parameters
Parameter Description
dbid ID of the database containing the page
dbname Name of the database containing the page
filenum File number containing the page
pagenum Page number within the file
printopt Optional print option; takes one of these values:
0 Default; print the buffer header and page header
1 Print the buffer header, page header, each row separately, and the row offset table
2 Print the buffer and page headers, page as a whole, and the offset table

*/

Error with COUNT(CASE WHEN...)

I have a strange problem.
I have reduced the problem to this:
SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
,COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
FROM (SELECT 'A') AS X (x0)
I exppect this to give me the result a-tally = 1 and b-tally = 0.
However I get
a_tally b_tally
-- --
1 1
(1 row(s) affected)
How in earths name is this possible?
/kCOUNT doesn't care if the result is 1 or 0, only that there is a result. I
think you meant to use SUM as the aggregate, not COUNT.
"kurt sune" <apa@.apa.com> wrote in message
news:uCDjPki2FHA.3136@.TK2MSFTNGP09.phx.gbl...
>I have a strange problem.
> I have reduced the problem to this:
> SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
> ,COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
> FROM (SELECT 'A') AS X (x0)
>
> I exppect this to give me the result a-tally = 1 and b-tally = 0.
> However I get
> a_tally b_tally
> -- --
> 1 1
> (1 row(s) affected)
>
> How in earths name is this possible?
>
> /k
>|||Run this:
SELECT count(CASE WHEN x0 = 'A' THEN 10 ELSE 0 END) AS a_tally
,count(CASE WHEN x0 = 'B' THEN 10 ELSE 0 END) AS b_tally
FROM (SELECT 'A') AS X (x0)
It will still return the same results as COUNT returns the number of rows.
Change your COUNTs to SUMs and your query works just fine.
"kurt sune" <apa@.apa.com> wrote in message
news:uCDjPki2FHA.3136@.TK2MSFTNGP09.phx.gbl...
>I have a strange problem.
> I have reduced the problem to this:
> SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
> ,COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
> FROM (SELECT 'A') AS X (x0)
>
> I exppect this to give me the result a-tally = 1 and b-tally = 0.
> However I get
> a_tally b_tally
> -- --
> 1 1
> (1 row(s) affected)
>
> How in earths name is this possible?
>
> /k
>|||Just remove the Else part and you'll be fine.
SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 END) AS a_tally
,COUNT(CASE WHEN x0 = 'B' THEN 1 END) AS b_tally
FROM (SELECT 'A') AS X (x0)
http://toponewithties.blogspot.com/...count-them.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"kurt sune" <apa@.apa.com> wrote in message
news:uCDjPki2FHA.3136@.TK2MSFTNGP09.phx.gbl...
>I have a strange problem.
> I have reduced the problem to this:
> SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
> ,COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
> FROM (SELECT 'A') AS X (x0)
>
> I exppect this to give me the result a-tally = 1 and b-tally = 0.
> However I get
> a_tally b_tally
> -- --
> 1 1
> (1 row(s) affected)
>
> How in earths name is this possible?
>
> /k
>|||I was trying this: http://www.dbazine.com/ofinterest/oi-articles/celko14
Seems like SQLserver and mr Celko disagrees about COUNT.
Thanks everyone.
/k
"kurt sune" <apa@.apa.com> wrote in message
news:uCDjPki2FHA.3136@.TK2MSFTNGP09.phx.gbl...
> I have a strange problem.
> I have reduced the problem to this:
> SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
> ,COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
> FROM (SELECT 'A') AS X (x0)
>
> I exppect this to give me the result a-tally = 1 and b-tally = 0.
> However I get
> a_tally b_tally
> -- --
> 1 1
> (1 row(s) affected)
>
> How in earths name is this possible?
>
> /k
>|||I am trying do do like Celko does in this article
http://www.dbazine.com/ofinterest/oi-articles/celko14
Do you any advice regarding the usage of the count in the HAVING clause?
My solution was this
HAVING COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE null END)
<> COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE null END)
However that means I have to set ansiwarnings off, which I dont like.
/k
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uOdJqmi2FHA.1576@.TK2MSFTNGP15.phx.gbl...
> COUNT doesn't care if the result is 1 or 0, only that there is a result.
I
> think you meant to use SUM as the aggregate, not COUNT.
>
>
> "kurt sune" <apa@.apa.com> wrote in message
> news:uCDjPki2FHA.3136@.TK2MSFTNGP09.phx.gbl...
>|||> Do you any advice regarding the usage of the count in the HAVING clause?
> My solution was this
> HAVING COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE null END)
> <> COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE null END)
Yes, use SUM, just like what was suggested in the SELECT clause?

Error while trying to execute an SP in my CASE STATEMENT

Hi all,
How do i execute a stored procedure in the THEN CLAUSE of my CASE STATEMENT? Av been getting errors since.

Here is my code:

Alter PROCEDURE sp_getTxn (
@.m1 int = Null,
@.txn int = Null,
@.p2 int = Null,
@.amt int = Null,
@.pAccountno varchar(50) = 'Null',
@.DAcct int = Null,
@.Balance Decimal(19,4) = NULL OUTPUT,
@.pBalance Decimal(19,4) = NULL OUTPUT,
@.RowsReturned smallint = NULL OUTPUT )
AS
SET NOCOUNT ON

select CASE
WHEN @.m1 = 200 THEN case
when @.txn = 00 then ('exec dbo.CustOrderHist (@.CrAcct int)')

when @.txn = 01 then ('exec dbo.Sp_withdrawal')

when @.txn = 31 then exec dbo.CheckBalance(@.pAccountno varchar(50), @.pBalance Decimal(19,4) OUTPUT)

when @.txn = 38 then ('exec dbo.Sp_StatementOfAcct')
END
END
WHEN @.m1 = 420 THEN case
when @.txnType = 00 then ('exec dbo.Sp_reversal')

when @.txnType = 01 then ('exec dbo.Sp_reversal2')

when @.txnType = 31 then ('exec dbo.Sp_reversal3')
END
END

SET @.Balance = @.pBalance
Print @.Balance

Or is there an alternative to the above CASE statement that is easier and faster?

ThanksUse IF instead of CASE:
IF @.m1 = 200
BEGIN
IF @.txn = 00
BEGIN
exec dbo.CustOrderHist (@.CrAcct int)
END
ELSE IF @.txn = 01
BEGIN
exec dbo.Sp_withdrawal
END
ELSE IF @.txn = 31
...
END
ELSE IF @.m1 = 420
BEGIN
...|||Thanks for the response, the if--else--if works.
...