Diffrence between sql 6.5 and sql 2000

[3270]

Hi

I got a bit of problem. Im converting from and old sql 6.5 server to a new sql 2000 server(in 6.5 mode, and withansi defaults off)
Ill try to illustrate the problem with the following select

create table #test
(
test int
)
go

truncate table #test
insert into #test values (1)
insert into #test values (2)
insert into #test values (3)

declare @test int

select @test = test
from #test
where (test = @test or @test is null)

select @@rowcount

In 6.5 @@rowcount =1
in 2000 @@rowcount = 3

Is there an easy way to make a select the does the same in 2000 as in sql 6.5

 

[vongrunt]

...
declare @test int

select @test = test
from #test
where (test = @test or @test is null)...

ORDER BY what?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mrdenny]

Create an actual table not a temp table. Temp tables are created in tempdb which is not in 6.5 mode.

You should get the results you are looking for if you create an actual table.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[3270]

Hi thanx for the answers

vongrunt: there are no search order

mrdenny tempdb is in 6.5 mode.

The value of @test in on sql 6.5 is 1
The value of @test in on sql 2000 is 3

 

[mrdenny]

If you put in a top 1 command into the select you should get the same output as from the 6.5 command.

I would have assumed that the functionaly would have switched back when you put tempdb in 6.5 mode?

Why not install SQL 6.5 and continue to run that (other that its out of it's support life cycle, or very close at least)?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[vongrunt]

I just ran above code on permanent table (no #) in both 65 and 80 compat mode databases. Both times @@ROWCOUNT was 3.

SQL2k reads all rows and assigns last found value to @test. Very useful for some tricks and hacks, but if there is no explicit ORDER BY then last value is... not reliable. Maybe that's the problem here. If not, I guess SQL6.5 behaves differently and stops after first value found.

How about using SET ROWCOUNT 1 before and SET ROWCOUNT 0 after?


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[3270]

We have changed to sql2000 mainly because of the lack of ms support. But also because i doesnt perform that well backing up 40gb of data takes a very long time on 6.5.

The trick with setting rowcount = 1 isnt optimal, because the select is in a stored procedure. it looks like this

testproc
@test1 null output,
@test2 null output,
@test3 null output

select @test1 = test,
@test2 = test2,
@test3 = test3
from #test
where (test = @test1 or @test1 is null)
and (test2 = @test2 or @test2 is null)

if @@rowcount > 1
begin
Select 'Error multiple recordsfound'
return
end
....

So you dont have to give the procedure both @test1 and @test2. It simply searches with conditions you apply
Thats why there is a check on @@rowcount to see if we only found 1 record.

 

[vongrunt]

I'm afraid you'll have to do it old way - one query for getting COUNT(*), another to retrieve @test1... @test3 values if count(*) returns 1.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)