Is this a known issue?

Hi All,
I was working on the issue posted today by asween on deleting duplicate
records and stumbled on this behaviour. Can anyone throw some light on this?
First create a table and insert some rows
create table #tdup (a int, b int)
insert into #tdup
select 1,1
union all
select 1,2
union all
select 1,3
union all
select 1,3
union all
select 1,2
union all
select 1,2
union all
select 2,1
union all
select 3,1
union all
select 1,5
union all
select 1,4
union all
select 1,4
union all
select 1,1
union all
select 1,3
union all
select 2,2
union all
select 2,6
union all
select 3,8
-- Now run the select query
select * from #tdup
-- it should give 16 rows
--Now run this script
--(this was what I was working on for removing duplicates)
--Don't mind the logic.. juz run it .. plz :)
declare @.a int,
@.b int,
@.rowcnt int
declare c1 cursor for
select a,b,count(*)
from #tdup
group by a,b
having count(*) > 1
open c1
fetch next from c1 into @.a,@.b,@.rowcnt
while @.@.fetch_status = 0
begin
set @.rowcnt = @.rowcnt - 1
set rowcount @.rowcnt
delete from #tdup
where a=@.a and b= @.b
fetch next from c1 into @.a,@.b,@.rowcnt
end
close c1
deallocate c1
--Now select from the table again
select * from #tdup
--gives one row. Not what I expected..
--But might be a bug in my half baked script :)
--Now run this again
insert into #tdup
select 1,1
union all
select 1,2
union all
select 1,3
union all
select 1,3
union all
select 1,2
union all
select 1,2
union all
select 2,1
union all
select 3,1
union all
select 1,5
union all
select 1,4
union all
select 1,4
union all
select 1,1
union all
select 1,3
union all
select 2,2
union all
select 2,6
union all
select 3,8
-- I get one row inserted..
select * from #tdup
Any clue on this'
Thanks in advance
OmnibuzzSorry.. found the issue. Its with the idiot outside.. thats me..
didn't set the rowcount back to 0 :P
apologies.
"Omnibuzz" wrote:

> Hi All,
> I was working on the issue posted today by asween on deleting duplicate
> records and stumbled on this behaviour. Can anyone throw some light on thi
s?
> First create a table and insert some rows
>
> create table #tdup (a int, b int)
>
> insert into #tdup
> select 1,1
> union all
> select 1,2
> union all
> select 1,3
> union all
> select 1,3
> union all
> select 1,2
> union all
> select 1,2
> union all
> select 2,1
> union all
> select 3,1
> union all
> select 1,5
> union all
> select 1,4
> union all
> select 1,4
> union all
> select 1,1
> union all
> select 1,3
> union all
> select 2,2
> union all
> select 2,6
> union all
> select 3,8
>
> -- Now run the select query
> select * from #tdup
> -- it should give 16 rows
> --Now run this script
> --(this was what I was working on for removing duplicates)
> --Don't mind the logic.. juz run it .. plz :)
> declare @.a int,
> @.b int,
> @.rowcnt int
> declare c1 cursor for
> select a,b,count(*)
> from #tdup
> group by a,b
> having count(*) > 1
> open c1
> fetch next from c1 into @.a,@.b,@.rowcnt
> while @.@.fetch_status = 0
> begin
> set @.rowcnt = @.rowcnt - 1
> set rowcount @.rowcnt
> delete from #tdup
> where a=@.a and b= @.b
> fetch next from c1 into @.a,@.b,@.rowcnt
> end
> close c1
> deallocate c1
> --Now select from the table again
> select * from #tdup
> --gives one row. Not what I expected..
> --But might be a bug in my half baked script :)
> --Now run this again
> insert into #tdup
> select 1,1
> union all
> select 1,2
> union all
> select 1,3
> union all
> select 1,3
> union all
> select 1,2
> union all
> select 1,2
> union all
> select 2,1
> union all
> select 3,1
> union all
> select 1,5
> union all
> select 1,4
> union all
> select 1,4
> union all
> select 1,1
> union all
> select 1,3
> union all
> select 2,2
> union all
> select 2,6
> union all
> select 3,8
> -- I get one row inserted..
> select * from #tdup
> Any clue on this'
> Thanks in advance
> Omnibuzz
>|||Hi
SET Back ROWCOUNT to 0
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:B427712E-4F37-47C1-992E-EFBCBCB4DC95@.microsoft.com...
> Hi All,
> I was working on the issue posted today by asween on deleting duplicate
> records and stumbled on this behaviour. Can anyone throw some light on
> this?
> First create a table and insert some rows
>
> create table #tdup (a int, b int)
>
> insert into #tdup
> select 1,1
> union all
> select 1,2
> union all
> select 1,3
> union all
> select 1,3
> union all
> select 1,2
> union all
> select 1,2
> union all
> select 2,1
> union all
> select 3,1
> union all
> select 1,5
> union all
> select 1,4
> union all
> select 1,4
> union all
> select 1,1
> union all
> select 1,3
> union all
> select 2,2
> union all
> select 2,6
> union all
> select 3,8
>
> -- Now run the select query
> select * from #tdup
> -- it should give 16 rows
> --Now run this script
> --(this was what I was working on for removing duplicates)
> --Don't mind the logic.. juz run it .. plz :)
> declare @.a int,
> @.b int,
> @.rowcnt int
> declare c1 cursor for
> select a,b,count(*)
> from #tdup
> group by a,b
> having count(*) > 1
> open c1
> fetch next from c1 into @.a,@.b,@.rowcnt
> while @.@.fetch_status = 0
> begin
> set @.rowcnt = @.rowcnt - 1
> set rowcount @.rowcnt
> delete from #tdup
> where a=@.a and b= @.b
> fetch next from c1 into @.a,@.b,@.rowcnt
> end
> close c1
> deallocate c1
> --Now select from the table again
> select * from #tdup
> --gives one row. Not what I expected..
> --But might be a bug in my half baked script :)
> --Now run this again
> insert into #tdup
> select 1,1
> union all
> select 1,2
> union all
> select 1,3
> union all
> select 1,3
> union all
> select 1,2
> union all
> select 1,2
> union all
> select 2,1
> union all
> select 3,1
> union all
> select 1,5
> union all
> select 1,4
> union all
> select 1,4
> union all
> select 1,1
> union all
> select 1,3
> union all
> select 2,2
> union all
> select 2,6
> union all
> select 3,8
> -- I get one row inserted..
> select * from #tdup
> Any clue on this'
> Thanks in advance
> Omnibuzz
>|||Thanks Uri.. Figured it out... and You were very kind..
When I saw that you posted, I was expecting a round of fire :)
"Uri Dimant" wrote:

> Hi
> SET Back ROWCOUNT to 0
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:B427712E-4F37-47C1-992E-EFBCBCB4DC95@.microsoft.com...
>
>|||:-))) It's ok , sometimes we need a break to take a cofee
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:A01FD8CC-1FE3-4A87-B267-940229345537@.microsoft.com...
> Thanks Uri.. Figured it out... and You were very kind..
> When I saw that you posted, I was expecting a round of fire :)
> "Uri Dimant" wrote:
>