Hi everyone,

We encountered two anomaly's in SQL 6.5 SP4.
1) Running an SP which apparently hangs but uses almost no processor-time
2) Running an SP that misses some data in the result set.

In both cases we created an workaround that solved the problem. The solution was to split the original SP into separate SP's that perform a part of the original SP.

Although we have a workaround, this behavior becomes troublesome when migrating to a new release of the datamodel and changing the SP's that are effected. This new situation causes SP's - that are NOT effected by the new release - to behave differently.

It is my guess that something in the environment is not behaving properly. Can anyone give me a suggestion?

Greetings,

Jody.
--
-- These exampe's are stripped verions of the original SP's
--
Example 1)
When the SQL-statements were executed outside the SP-context in ISQLW, the outcome was within the expected Timelimit of approx. 10 minutes.

create procedure xxx with recompile
as
begin

if exists( select * from tempdb.dbo.sysobjects where name = "##tempResult")
begin
drop table ##tempResult
end

create table ##tempResult
(
KeyNumber varchar(20) not null
)

insert ##tempResult
(
KeyNumber
)
select T3.KeyNo as "KeyNumber"
from Table1 T1,
Table2 T2,
Table3 T3
where T2.aaa = T1.aaa
and T3.bbb = T2.bbb

delete from ##tempResult
where ##tempResult.KeyNumber not in
(
select Min(t.KeyNumber)
from ##tempResult t
where ##tempResult.aaa = t.aaa
and ##tempResult.bbb = t.bbb
)

select KeyNumber, ...
from ##tempResult
group by KeyNumber, ...

drop Table ##tempResult
end

Example 2)

create procedure xxx with recompile
as
begin
if exists (select * from tempdb.dbo.sysobjects where name = "##tempMessages")
begin
drop table ##tempMessages
end

create table ##tempMessages
(
id decimal(3) identity not null,
ExtraData varchar(10) not null
)

insert ##tempMessages
(
ExtraData
)
select ExtraData
from Table
order by ExtraData

select "ID " + convert(varchar, id) as "Code", ExtraData
from ##tempMessages

union all

select "XX " + convert(varchar, CodeNr) as "Code", ExtraDataX
from TableX

end
go