count records in a top 10 query

Hi

Im trying to make a top 10 list of col1 and and at the 11:th place it should show a number of record that dosent make it to the top 10 list...

i have this so far, and it dosent give me anything...

col1 is varchar 254

SELECT COL1, COUNT(*) AS number
FROM MYTABLE
WHERE (NOT EXISTS
(SELECT TOP 10 COL1
FROM MYTABLE))
GROUP BY COL1
ORDER BY COUNT(*) DESC)

ex of output

place1 100
place2 50
place3 25
...
place11 500

a query that only gives me the place11 number is enough

thx in advance //MrHere is the number of records that are not in the top 10 list

select count(*) number
from myTable
where col1 not in
(select top 10 col1 from myTable)
group by col1
order by count(*) desc
)

Count record from table incorrect

When I count records from tableA, I got results as about
11M records . But the table actually has only 2M records.
I did count(*), or count (ID), or count(distinct ID), all
give me the same 12M.
But I'm sure the table has only 2M. Thanks,
It is SQL Server 2000. I did it in Query analyzer.
The exact queries are:
select count(ID)
from tableA
select count(*)
from tableA
select count(Field1)
from tableA
select count(field2)
from tableA
select count(*)
from tableA
select distinct count(ID)
from tableA
comment: ID is the identity field.
I rebuilt/recreated the indexes.
They all showed as about 12M
select sum(1) from tableA
I got about 12M
I know the records in 2M for sure. Also when I did
select *
into temptableA
from tableA
about 2M rows affected.
What could be wrong?
Thanks,Hi,
Execute the below command :-
sp_spaceused <table_name>,@.updateusage='true'
THis will return the exact row count. After the successful execution of the
above command try executing the
select count(*) from table_name
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
> When I count records from tableA, I got results as about
> 11M records . But the table actually has only 2M records.
> I did count(*), or count (ID), or count(distinct ID), all
> give me the same 12M.
> But I'm sure the table has only 2M. Thanks,
> It is SQL Server 2000. I did it in Query analyzer.
>
> The exact queries are:
> select count(ID)
> from tableA
> select count(*)
> from tableA
> select count(Field1)
> from tableA
> select count(field2)
> from tableA
> select count(*)
> from tableA
> select distinct count(ID)
> from tableA
> comment: ID is the identity field.
> I rebuilt/recreated the indexes.
> They all showed as about 12M
> select sum(1) from tableA
> I got about 12M
> I know the records in 2M for sure. Also when I did
> select *
> into temptableA
> from tableA
> about 2M rows affected.
> What could be wrong?
> Thanks,|||> But I'm sure the table has only 2M. Thanks,
> ...
> I know the records in 2M for sure.
You keep saying that, but how do you know that "for sure"?
Have you updated statistics recently?
http://www.aspfaq.com/
(Reverse address to reply.)|||Hari,
sp_spaceused returns me the right number as 2M,
but then I did "select count(*) from table_name"
That still gives me 11M.
Thanks,

>--Original Message--
>Hi,
>Execute the below command :-
>sp_spaceused <table_name>,@.updateusage='true'
>THis will return the exact row count. After the
successful execution of the
>above command try executing the
>select count(*) from table_name
>Thanks
>Hari
>MCDBA
>
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
records.[vbcol=seagreen]
all[vbcol=seagreen]
>
>.
>|||Hi,
Did you run @.updateusage='true' along with sp_spaceused. This will correct
the inconsistencies in sysindexes.
use <dbname>
go
sp_spaceused <table_name>,@.updateusage='true'
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:439501c47330$bd689a30$a601280a@.phx.gbl...[vbcol=seagreen]
> Hari,
> sp_spaceused returns me the right number as 2M,
> but then I did "select count(*) from table_name"
> That still gives me 11M.
> Thanks,
>
>
> successful execution of the
> records.
> all|||Hari,
As far as I know, select count(*) from T doesn't use information in
sysindexes at all, but refers to the actual data. I can think of a few
explanations of what's going on. From possible to very speculative,
here they are:
1. There is an open transaction. If the transaction isolation level is
read uncommitted, could someone have inserted 9 million rows but not
committed? I don't know what sysindexes or sp_spaceused with
update-usage does in this case, but some combination of isolation level
and open transaction could be at work..
2. There are 11 million rows, and sysindexes is wrong (as it can be).
TableA has a unique constraint with ignore_dup_key set on it, and 9
million rows are being discarded by the insert.
3. The rowcount from the insert is wrong because of a trigger (I have
seen this when a distributed transaction is involved, but I think it's
been fixed).
4. The COUNT(*) query plan involves an indexed view, and something odd
is going on there. Or there is some other issue to do with a view.
5. There is a trigger on temptableA that affected the rowcount.
5. There are two tableA tables, with different owners, and something
funny is going on with ownership resolution.
I suspect count(*) is correct, and would try this:
select 1 as One
into #counter
from tableA
select sum(One) as ct from #counter
or
declare @.i int
set @.i = 0
select @.i = @.i + 1
from tableA
select @.i
or maybe
declare @.i int
set @.i = 0
select top 99.999999999 percent @.i = @.i + 1
from tableA
order by OrderID
select @.i
Steve Kass
Drew University
Hari Prasad wrote:

>Hi,
>Did you run @.updateusage='true' along with sp_spaceused. This will correct
>the inconsistencies in sysindexes.
>use <dbname>
>go
>sp_spaceused <table_name>,@.updateusage='true'
>Thanks
>Hari
>MCDBA
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:439501c47330$bd689a30$a601280a@.phx.gbl...
>
>
>|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT
" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT
" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT
" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT
" se to 2M?
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>

Count record from table incorrect

When I count records from tableA, I got results as about
11M records . But the table actually has only 2M records.
I did count(*), or count (ID), or count(distinct ID), all
give me the same 12M.
But I'm sure the table has only 2M. Thanks,
It is SQL Server 2000. I did it in Query analyzer.
The exact queries are:
select count(ID)
from tableA
select count(*)
from tableA
select count(Field1)
from tableA
select count(field2)
from tableA
select count(*)
from tableA
select distinct count(ID)
from tableA
comment: ID is the identity field.
I rebuilt/recreated the indexes.
They all showed as about 12M
select sum(1) from tableA
I got about 12M
I know the records in 2M for sure. Also when I did
select *
into temptableA
from tableA
about 2M rows affected.
What could be wrong?
Thanks,
Hi,
Execute the below command :-
sp_spaceused <table_name>,@.updateusage='true'
THis will return the exact row count. After the successful execution of the
above command try executing the
select count(*) from table_name
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
> When I count records from tableA, I got results as about
> 11M records . But the table actually has only 2M records.
> I did count(*), or count (ID), or count(distinct ID), all
> give me the same 12M.
> But I'm sure the table has only 2M. Thanks,
> It is SQL Server 2000. I did it in Query analyzer.
>
> The exact queries are:
> select count(ID)
> from tableA
> select count(*)
> from tableA
> select count(Field1)
> from tableA
> select count(field2)
> from tableA
> select count(*)
> from tableA
> select distinct count(ID)
> from tableA
> comment: ID is the identity field.
> I rebuilt/recreated the indexes.
> They all showed as about 12M
> select sum(1) from tableA
> I got about 12M
> I know the records in 2M for sure. Also when I did
> select *
> into temptableA
> from tableA
> about 2M rows affected.
> What could be wrong?
> Thanks,
|||> But I'm sure the table has only 2M. Thanks,
> ...
> I know the records in 2M for sure.
You keep saying that, but how do you know that "for sure"?
Have you updated statistics recently?
http://www.aspfaq.com/
(Reverse address to reply.)
|||Hari,
sp_spaceused returns me the right number as 2M,
but then I did "select count(*) from table_name"
That still gives me 11M.
Thanks,

>--Original Message--
>Hi,
>Execute the below command :-
>sp_spaceused <table_name>,@.updateusage='true'
>THis will return the exact row count. After the
successful execution of the[vbcol=seagreen]
>above command try executing the
>select count(*) from table_name
>Thanks
>Hari
>MCDBA
>
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
records.[vbcol=seagreen]
all
>
>.
>
|||Hi,
Did you run @.updateusage='true' along with sp_spaceused. This will correct
the inconsistencies in sysindexes.
use <dbname>
go
sp_spaceused <table_name>,@.updateusage='true'
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:439501c47330$bd689a30$a601280a@.phx.gbl...[vbcol=seagreen]
> Hari,
> sp_spaceused returns me the right number as 2M,
> but then I did "select count(*) from table_name"
> That still gives me 11M.
> Thanks,
>
> successful execution of the
> records.
> all
|||Hari,
As far as I know, select count(*) from T doesn't use information in
sysindexes at all, but refers to the actual data. I can think of a few
explanations of what's going on. From possible to very speculative,
here they are:
1. There is an open transaction. If the transaction isolation level is
read uncommitted, could someone have inserted 9 million rows but not
committed? I don't know what sysindexes or sp_spaceused with
update-usage does in this case, but some combination of isolation level
and open transaction could be at work..
2. There are 11 million rows, and sysindexes is wrong (as it can be).
TableA has a unique constraint with ignore_dup_key set on it, and 9
million rows are being discarded by the insert.
3. The rowcount from the insert is wrong because of a trigger (I have
seen this when a distributed transaction is involved, but I think it's
been fixed).
4. The COUNT(*) query plan involves an indexed view, and something odd
is going on there. Or there is some other issue to do with a view.
5. There is a trigger on temptableA that affected the rowcount.
5. There are two tableA tables, with different owners, and something
funny is going on with ownership resolution.
I suspect count(*) is correct, and would try this:
select 1 as One
into #counter
from tableA
select sum(One) as ct from #counter
or
declare @.i int
set @.i = 0
select @.i = @.i + 1
from tableA
select @.i
or maybe
declare @.i int
set @.i = 0
select top 99.999999999 percent @.i = @.i + 1
from tableA
order by OrderID
select @.i
Steve Kass
Drew University
Hari Prasad wrote:

>Hi,
>Did you run @.updateusage='true' along with sp_spaceused. This will correct
>the inconsistencies in sysindexes.
>use <dbname>
>go
>sp_spaceused <table_name>,@.updateusage='true'
>Thanks
>Hari
>MCDBA
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:439501c47330$bd689a30$a601280a@.phx.gbl...
>
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>

Count record from table incorrect

When I count records from tableA, I got results as about
11M records . But the table actually has only 2M records.
I did count(*), or count (ID), or count(distinct ID), all
give me the same 12M.
But I'm sure the table has only 2M. Thanks,
It is SQL Server 2000. I did it in Query analyzer.
The exact queries are:
select count(ID)
from tableA
select count(*)
from tableA
select count(Field1)
from tableA
select count(field2)
from tableA
select count(*)
from tableA
select distinct count(ID)
from tableA
comment: ID is the identity field.
I rebuilt/recreated the indexes.
They all showed as about 12M
select sum(1) from tableA
I got about 12M
I know the records in 2M for sure. Also when I did
select *
into temptableA
from tableA
about 2M rows affected.
What could be wrong?
Thanks,Hi,
Execute the below command :-
sp_spaceused <table_name>,@.updateusage='true'
THis will return the exact row count. After the successful execution of the
above command try executing the
select count(*) from table_name
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
> When I count records from tableA, I got results as about
> 11M records . But the table actually has only 2M records.
> I did count(*), or count (ID), or count(distinct ID), all
> give me the same 12M.
> But I'm sure the table has only 2M. Thanks,
> It is SQL Server 2000. I did it in Query analyzer.
>
> The exact queries are:
> select count(ID)
> from tableA
> select count(*)
> from tableA
> select count(Field1)
> from tableA
> select count(field2)
> from tableA
> select count(*)
> from tableA
> select distinct count(ID)
> from tableA
> comment: ID is the identity field.
> I rebuilt/recreated the indexes.
> They all showed as about 12M
> select sum(1) from tableA
> I got about 12M
> I know the records in 2M for sure. Also when I did
> select *
> into temptableA
> from tableA
> about 2M rows affected.
> What could be wrong?
> Thanks,|||> But I'm sure the table has only 2M. Thanks,
> ...
> I know the records in 2M for sure.
You keep saying that, but how do you know that "for sure"?
Have you updated statistics recently?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Hari,
sp_spaceused returns me the right number as 2M,
but then I did "select count(*) from table_name"
That still gives me 11M.
Thanks,
>--Original Message--
>Hi,
>Execute the below command :-
>sp_spaceused <table_name>,@.updateusage='true'
>THis will return the exact row count. After the
successful execution of the
>above command try executing the
>select count(*) from table_name
>Thanks
>Hari
>MCDBA
>
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
>> When I count records from tableA, I got results as about
>> 11M records . But the table actually has only 2M
records.
>> I did count(*), or count (ID), or count(distinct ID),
all
>> give me the same 12M.
>> But I'm sure the table has only 2M. Thanks,
>> It is SQL Server 2000. I did it in Query analyzer.
>>
>> The exact queries are:
>> select count(ID)
>> from tableA
>> select count(*)
>> from tableA
>> select count(Field1)
>> from tableA
>> select count(field2)
>> from tableA
>> select count(*)
>> from tableA
>> select distinct count(ID)
>> from tableA
>> comment: ID is the identity field.
>> I rebuilt/recreated the indexes.
>> They all showed as about 12M
>> select sum(1) from tableA
>> I got about 12M
>> I know the records in 2M for sure. Also when I did
>> select *
>> into temptableA
>> from tableA
>> about 2M rows affected.
>> What could be wrong?
>> Thanks,
>
>.
>|||Hi,
Did you run @.updateusage='true' along with sp_spaceused. This will correct
the inconsistencies in sysindexes.
use <dbname>
go
sp_spaceused <table_name>,@.updateusage='true'
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:439501c47330$bd689a30$a601280a@.phx.gbl...
> Hari,
> sp_spaceused returns me the right number as 2M,
> but then I did "select count(*) from table_name"
> That still gives me 11M.
> Thanks,
>
> >--Original Message--
> >Hi,
> >
> >Execute the below command :-
> >
> >sp_spaceused <table_name>,@.updateusage='true'
> >
> >THis will return the exact row count. After the
> successful execution of the
> >above command try executing the
> >
> >select count(*) from table_name
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >
> >
> >"ISD_ERD" <lxwang@.pa1call.org> wrote in message
> >news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
> >>
> >> When I count records from tableA, I got results as about
> >> 11M records . But the table actually has only 2M
> records.
> >> I did count(*), or count (ID), or count(distinct ID),
> all
> >> give me the same 12M.
> >> But I'm sure the table has only 2M. Thanks,
> >>
> >> It is SQL Server 2000. I did it in Query analyzer.
> >>
> >>
> >> The exact queries are:
> >>
> >> select count(ID)
> >> from tableA
> >>
> >> select count(*)
> >> from tableA
> >>
> >> select count(Field1)
> >> from tableA
> >>
> >> select count(field2)
> >> from tableA
> >>
> >> select count(*)
> >> from tableA
> >>
> >> select distinct count(ID)
> >> from tableA
> >>
> >> comment: ID is the identity field.
> >>
> >> I rebuilt/recreated the indexes.
> >>
> >> They all showed as about 12M
> >>
> >> select sum(1) from tableA
> >> I got about 12M
> >>
> >> I know the records in 2M for sure. Also when I did
> >> select *
> >> into temptableA
> >> from tableA
> >>
> >> about 2M rows affected.
> >>
> >> What could be wrong?
> >>
> >> Thanks,
> >
> >
> >.
> >|||Hari,
As far as I know, select count(*) from T doesn't use information in
sysindexes at all, but refers to the actual data. I can think of a few
explanations of what's going on. From possible to very speculative,
here they are:
1. There is an open transaction. If the transaction isolation level is
read uncommitted, could someone have inserted 9 million rows but not
committed? I don't know what sysindexes or sp_spaceused with
update-usage does in this case, but some combination of isolation level
and open transaction could be at work..
2. There are 11 million rows, and sysindexes is wrong (as it can be).
TableA has a unique constraint with ignore_dup_key set on it, and 9
million rows are being discarded by the insert.
3. The rowcount from the insert is wrong because of a trigger (I have
seen this when a distributed transaction is involved, but I think it's
been fixed).
4. The COUNT(*) query plan involves an indexed view, and something odd
is going on there. Or there is some other issue to do with a view.
5. There is a trigger on temptableA that affected the rowcount.
5. There are two tableA tables, with different owners, and something
funny is going on with ownership resolution.
I suspect count(*) is correct, and would try this:
select 1 as One
into #counter
from tableA
select sum(One) as ct from #counter
or
declare @.i int
set @.i = 0
select @.i = @.i + 1
from tableA
select @.i
or maybe
declare @.i int
set @.i = 0
select top 99.999999999 percent @.i = @.i + 1
from tableA
order by OrderID
select @.i
Steve Kass
Drew University
Hari Prasad wrote:
>Hi,
>Did you run @.updateusage='true' along with sp_spaceused. This will correct
>the inconsistencies in sysindexes.
>use <dbname>
>go
>sp_spaceused <table_name>,@.updateusage='true'
>Thanks
>Hari
>MCDBA
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:439501c47330$bd689a30$a601280a@.phx.gbl...
>
>>Hari,
>>sp_spaceused returns me the right number as 2M,
>>but then I did "select count(*) from table_name"
>>That still gives me 11M.
>>Thanks,
>>
>>
>>--Original Message--
>>Hi,
>>Execute the below command :-
>>sp_spaceused <table_name>,@.updateusage='true'
>>THis will return the exact row count. After the
>>
>>successful execution of the
>>
>>above command try executing the
>>select count(*) from table_name
>>Thanks
>>Hari
>>MCDBA
>>
>>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>>news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
>>
>>When I count records from tableA, I got results as about
>>11M records . But the table actually has only 2M
>>
>>records.
>>
>>I did count(*), or count (ID), or count(distinct ID),
>>
>>all
>>
>>give me the same 12M.
>>But I'm sure the table has only 2M. Thanks,
>>It is SQL Server 2000. I did it in Query analyzer.
>>
>>The exact queries are:
>>select count(ID)
>>from tableA
>>select count(*)
>>from tableA
>>select count(Field1)
>>from tableA
>>select count(field2)
>>from tableA
>>select count(*)
>>from tableA
>>select distinct count(ID)
>>from tableA
>>comment: ID is the identity field.
>>I rebuilt/recreated the indexes.
>>They all showed as about 12M
>>select sum(1) from tableA
>> I got about 12M
>>I know the records in 2M for sure. Also when I did
>>select *
>>into temptableA
>>from tableA
>>about 2M rows affected.
>>What could be wrong?
>>Thanks,
>>
>>.
>>
>
>

count of records greater than?

Hello All,

Trying to set up a column in a grouped matrix that displays a count of all record over a specificed number.

The field I am counting are response time of transaction and I want to count how many were over 500 milliseconds. I though it would be something like this...

Code Snippet

=Count(Fields!ResponseTime.Value > "500")

However, this appears to just return the count of all rows and ignores the "500" part.

Am I missing something? If someone could post a alternate code snippet, that would be great.

Thanks in advance,

Clint

Hi Clint,

Try this expression

Code Snippet

=Count(iif (Fields!ResponseTime.Value > 500,1,nothing))

Best Regards,

Rajiv

|||

Thanks that works well..as an after thought, I need to add something in that would separate on of the field that has a different threshhold. Its grouped up and all but one has the 500 threshold count but one has 1000. Any ideas on how to separate it out?

I was thinking

Code Snippet

=Count(iif (Fields!ResponseTime.Value="QMEN" > 1000,1,nothing)) or Count(iif (Fields!ResponseTime.Value > 500,1,nothing))

But that errored out.|||

Need a second iff. Try just wrapping it around the timeout value

(I can't see your original post, so I'm just going to alter the innermost part of it. I don't think you meant the responsetime = QMEN..)

. . . > iif(XXXX.Value="QMEN", 1000, 500) . . .

|||

How would I incorporate that into?...

=Count(iif (Fields!ResponseTime.Value > 500,1,nothing))

Thanks.

|||

=Count(iif (Fields!ResponseTime.Value > iif(XXXX.Value="QMEN", 1000, 500) , 1, nothing))

Where XXXX is whatever field has the value QMEN

|||Awsome. Thanks so much. the worked perfectlysql

Count in Empty RecordSet and BOF/EOF

Count has been addressed many times but, to my knowledge, the association between "Count" on an empty record set and BOF/EOF has not been discussed. There are a few comparisons to review here, thank you for your help and patience.

My question: When Count is used in a query will BOF and EOF always be FALSE even if the recordset is empty (see Method 1 example)? (Do aggregate functions always return a BOF and EOF set to FALSE?)

Also, is it better to use Method 2, or Method 3 listed below instead of Method 1?

Method 1 example:
Select Count(bill_no ) from bill_table where budget_id = '297333'

(ASP Web page):
'Will BOF and EOF always return FALSE for Count - regardless if the count is zero or not?
If objRS.BOF AND objRS.EOF Then
'Empty Record set
Else
'Record set is not empty
End If

Method 2 example:
Select Count(bill_no ) from bill_table where budget_id = '297333'

(ASP Web page):
'Is this safe or will -1 be returned in some instances even if there may be records that match?
If Cint(objRS(0)) > 0 Then
'Record set is not empty
Else
'Empty Record set
End If

Method 3:
'Uses RecordCount - (but this errors for me: returns -1 (I suspect wrong cursor))
Select bill_no from bill_table where budget_id = '297333'

(ASP Web page)
If objRS.BOF AND objRS.EOF Then
'Empty record set
Else
' not empty
intNumBills = objRS.RecordCountAny SQL statement that uses an aggregate (any aggregate) has to return rows. Even if zero rows were counted, the count itself is still returned as an aggregate row. This implies that Method 1 isn't going to work, but that Method 2 is guaranteed to work.

The value -1 in a recordcount has special meanings for various kinds of recordsets. See the RecordCount (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprorecordcount.asp) entry in the ADO documentation.

-PatP

Could not open FCB for invalid file ID 20294 in database

Hi,
I am getting this error when I am trying to delete record
"Server: Msg 5180, Level 22, State 1, Line 1
Could not open FCB for invalid file ID 20294 in database 'SAS_Test'.
Connection Broken"
But when I use select command it works fine. I can ping the server, but some
how can't delete does not work!.
I check the SQL server log and it has "Could not open FCB for invalid file
ID 20294 in database, Error: 5180, Severity: 22, State: 1"
Any help would be appreciated.
Shan
Run DBCC CheckDB for that database to check physical corruption under file..
also refer to this link: -
http://support.microsoft.com/default...;en-us;Q276043
"Shan" wrote:

> Hi,
> I am getting this error when I am trying to delete record
> "Server: Msg 5180, Level 22, State 1, Line 1
> Could not open FCB for invalid file ID 20294 in database 'SAS_Test'.
> Connection Broken"
> But when I use select command it works fine. I can ping the server, but some
> how can't delete does not work!.
> I check the SQL server log and it has "Could not open FCB for invalid file
> ID 20294 in database, Error: 5180, Severity: 22, State: 1"
> Any help would be appreciated.
> --
> Shan

Could not open FCB for invalid file ID 20294 in database

Hi,
I am getting this error when I am trying to delete record
"Server: Msg 5180, Level 22, State 1, Line 1
Could not open FCB for invalid file ID 20294 in database 'SAS_Test'.
Connection Broken"
But when I use select command it works fine. I can ping the server, but some
how can't delete does not work!.
I check the SQL server log and it has "Could not open FCB for invalid file
ID 20294 in database, Error: 5180, Severity: 22, State: 1"
Any help would be appreciated.
--
ShanRun DBCC CheckDB for that database to check physical corruption under file..
also refer to this link: -
http://support.microsoft.com/defaul...b;en-us;Q276043
"Shan" wrote:

> Hi,
> I am getting this error when I am trying to delete record
> "Server: Msg 5180, Level 22, State 1, Line 1
> Could not open FCB for invalid file ID 20294 in database 'SAS_Test'.
> Connection Broken"
> But when I use select command it works fine. I can ping the server, but so
me
> how can't delete does not work!.
> I check the SQL server log and it has "Could not open FCB for invalid file
> ID 20294 in database, Error: 5180, Severity: 22, State: 1"
> Any help would be appreciated.
> --
> Shan

Could not open FCB for invalid file ID 20294 in database

Hi,
I am getting this error when I am trying to delete record
"Server: Msg 5180, Level 22, State 1, Line 1
Could not open FCB for invalid file ID 20294 in database 'SAS_Test'.
Connection Broken"
But when I use select command it works fine. I can ping the server, but some
how can't delete does not work!.
I check the SQL server log and it has "Could not open FCB for invalid file
ID 20294 in database, Error: 5180, Severity: 22, State: 1"
Any help would be appreciated.
--
ShanRun DBCC CheckDB for that database to check physical corruption under file..
also refer to this link: -
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q276043
"Shan" wrote:
> Hi,
> I am getting this error when I am trying to delete record
> "Server: Msg 5180, Level 22, State 1, Line 1
> Could not open FCB for invalid file ID 20294 in database 'SAS_Test'.
> Connection Broken"
> But when I use select command it works fine. I can ping the server, but some
> how can't delete does not work!.
> I check the SQL server log and it has "Could not open FCB for invalid file
> ID 20294 in database, Error: 5180, Severity: 22, State: 1"
> Any help would be appreciated.
> --
> Shan

Could not continue scan with NOLOCK due to data movement. when delete a record

Error 601
Severity Level 12
Message Text
Could not continue scan with NOLOCK due to data movement.
My client always hit this error when he tried to delete a record from
table. The table contains about 1 million records. the table has a
unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
(col_a),
non-unique index idx_3 on col_c. An update trigger on table also.
When he execute
delete from table_a where col_a = @.parm1 and col_c = @.parm3. he gets
the 601 error.
but if he execute
delete from table_a where col_a = @.parm1 and col_b = @.parm2. he can
delete this one record.
We execute dbcc checktable(table_a) and no error reported.
I read posts that saying it is because read-uncommitted or nolock
hint, but we do not has this options and we did not execute select
statement.
Any idea why this happens, we are afraid that the table is not healthy/
waiting to corruption. Is there anything we can do to narrow down
where the cause is and to provent it happen again?
and it is MS SQL 2000 with SP4
thanks in advance
-rockdaleDoes this KB article apply?
http://support.microsoft.com/kb/815008
Linchi
"rockdale" wrote:
> Error 601
> Severity Level 12
> Message Text
> Could not continue scan with NOLOCK due to data movement.
> My client always hit this error when he tried to delete a record from
> table. The table contains about 1 million records. the table has a
> unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
> (col_a),
> non-unique index idx_3 on col_c. An update trigger on table also.
> When he execute
> delete from table_a where col_a = @.parm1 and col_c = @.parm3. he gets
> the 601 error.
> but if he execute
> delete from table_a where col_a = @.parm1 and col_b = @.parm2. he can
> delete this one record.
> We execute dbcc checktable(table_a) and no error reported.
> I read posts that saying it is because read-uncommitted or nolock
> hint, but we do not has this options and we did not execute select
> statement.
> Any idea why this happens, we are afraid that the table is not healthy/
> waiting to corruption. Is there anything we can do to narrow down
> where the cause is and to provent it happen again?
> and it is MS SQL 2000 with SP4
> thanks in advance
> -rockdale
>|||NOPE.
The database is SQL2K with SP4.
They did not execute any select statement, not mention Read Uncommited
option.
They execute the delete statement using one where condition and get
the error.
They execute the delete statement using another where condition and
no error occured.
I even execute the DBCC TRACEON (9134, -1) as the article you
mentioned, I still getting the same error. I can not restart the sql
server, it is a production server.
Thanks
On Oct 19, 11:46 am, Linchi Shea
<LinchiS...@.discussions.microsoft.com> wrote:
> Does this KB article apply?http://support.microsoft.com/kb/815008
> Linchi
>
> "rockdale" wrote:
> > Error 601
> > Severity Level 12
> > Message Text
> > Could not continue scan with NOLOCK due to data movement.
> > My client always hit this error when he tried to delete a record from
> > table. The table contains about 1 million records. the table has a
> > unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
> > (col_a),
> > non-unique index idx_3 on col_c. An update trigger on table also.
> > When he execute
> > delete from table_a where col_a = @.parm1 and col_c = @.parm3. he gets
> > the 601 error.
> > but if he execute
> > delete from table_a where col_a = @.parm1 and col_b = @.parm2. he can
> > delete this one record.
> > We execute dbcc checktable(table_a) and no error reported.
> > I read posts that saying it is because read-uncommitted or nolock
> > hint, but we do not has this options and we did not execute select
> > statement.
> > Any idea why this happens, we are afraid that the table is not healthy/
> > waiting to corruption. Is there anything we can do to narrow down
> > where the cause is and to provent it happen again?
> > and it is MS SQL 2000 with SP4
> > thanks in advance
> > -rockdale- Hide quoted text -
> - Show quoted text -|||Look for a DELETE trigger on the table.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"rockdale" <rockdale.green@.gmail.com> wrote in message
news:1192807669.901836.228930@.i38g2000prf.googlegroups.com...
> Error 601
> Severity Level 12
> Message Text
> Could not continue scan with NOLOCK due to data movement.
> My client always hit this error when he tried to delete a record from
> table. The table contains about 1 million records. the table has a
> unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
> (col_a),
> non-unique index idx_3 on col_c. An update trigger on table also.
> When he execute
> delete from table_a where col_a = @.parm1 and col_c = @.parm3. he gets
> the 601 error.
> but if he execute
> delete from table_a where col_a = @.parm1 and col_b = @.parm2. he can
> delete this one record.
> We execute dbcc checktable(table_a) and no error reported.
> I read posts that saying it is because read-uncommitted or nolock
> hint, but we do not has this options and we did not execute select
> statement.
> Any idea why this happens, we are afraid that the table is not healthy/
> waiting to corruption. Is there anything we can do to narrow down
> where the cause is and to provent it happen again?
> and it is MS SQL 2000 with SP4
> thanks in advance
> -rockdale
>|||They have a n update trigger in the table basically update the
lastedUpdateDateTime field. The Trigger is for Update only, so I do
not think delete will trigger this trigger.
On Oct 19, 1:30 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Look for a DELETE trigger on the table.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
> "rockdale" <rockdale.gr...@.gmail.com> wrote in message
> news:1192807669.901836.228930@.i38g2000prf.googlegroups.com...
>
> > Error 601
> > Severity Level 12
> > Message Text
> > Could not continue scan with NOLOCK due to data movement.
> > My client always hit this error when he tried to delete a record from
> > table. The table contains about 1 million records. the table has a
> > unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
> > (col_a),
> > non-unique index idx_3 on col_c. An update trigger on table also.
> > When he execute
> > delete from table_a where col_a = @.parm1 and col_c = @.parm3. he gets
> > the 601 error.
> > but if he execute
> > delete from table_a where col_a = @.parm1 and col_b = @.parm2. he can
> > delete this one record.
> > We execute dbcc checktable(table_a) and no error reported.
> > I read posts that saying it is because read-uncommitted or nolock
> > hint, but we do not has this options and we did not execute select
> > statement.
> > Any idea why this happens, we are afraid that the table is not healthy/
> > waiting to corruption. Is there anything we can do to narrow down
> > where the cause is and to provent it happen again?
> > and it is MS SQL 2000 with SP4
> > thanks in advance
> > -rockdale- Hide quoted text -
> - Show quoted text -|||I just delete the trigger and I still getting the same error.
More Info on index:
They have a UNIQUE NONCLUSTERED Constraints on table_a
(col_a, col_b, col_c)
an INDEX on table_a
(col_c)
an INDEX on table_a
(col_a)
Might these indices problems?
thanks
On Oct 19, 1:30 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Look for a DELETE trigger on the table.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
> "rockdale" <rockdale.gr...@.gmail.com> wrote in message
> news:1192807669.901836.228930@.i38g2000prf.googlegroups.com...
>
> > Error 601
> > Severity Level 12
> > Message Text
> > Could not continue scan with NOLOCK due to data movement.
> > My client always hit this error when he tried to delete a record from
> > table. The table contains about 1 million records. the table has a
> > unique index idx_1 on (col_a, col_b, col_c). non-unique index idx_2 on
> > (col_a),
> > non-unique index idx_3 on col_c. An update trigger on table also.
> > When he execute
> > delete from table_a where col_a = @.parm1 and col_c = @.parm3. he gets
> > the 601 error.
> > but if he execute
> > delete from table_a where col_a = @.parm1 and col_b = @.parm2. he can
> > delete this one record.
> > We execute dbcc checktable(table_a) and no error reported.
> > I read posts that saying it is because read-uncommitted or nolock
> > hint, but we do not has this options and we did not execute select
> > statement.
> > Any idea why this happens, we are afraid that the table is not healthy/
> > waiting to corruption. Is there anything we can do to narrow down
> > where the cause is and to provent it happen again?
> > and it is MS SQL 2000 with SP4
> > thanks in advance
> > -rockdale- Hide quoted text -
> - Show quoted text -

coul no redo log record error

please, i am receiving an error in log shipping of sql
server 2000. the message is "could not redo log record..."
i found this message at microsoft, and it says that it is
a bug that ocurs when trying to make index operations in
the
same time you are making log backups. it talks about a
fix, but i didnt find it and i could not resolve the
problem . every day ew test our backups with its logs and
its happening this about three times per week.
i am using sql 2000 with the latest service pack
you someone could help i would be very gratefull !
thanks !This would be a high severity bug. Contact Microsoft support.
"alexandre" <alexandre_santos@.uol.com.br> wrote in message
news:0a1101c38852$48cc7300$a301280a@.phx.gbl...
> please, i am receiving an error in log shipping of sql
> server 2000. the message is "could not redo log record..."
> i found this message at microsoft, and it says that it is
> a bug that ocurs when trying to make index operations in
> the
> same time you are making log backups. it talks about a
> fix, but i didnt find it and i could not resolve the
> problem . every day ew test our backups with its logs and
> its happening this about three times per week.
> i am using sql 2000 with the latest service pack
> you someone could help i would be very gratefull !
> thanks !
>