Works in query analyser But gives error in Crystal

[simma]

Hello
I have this sql query which works in queryanalyser in Sql2000 but gives error while accessing in Crystal 7.0
Please advice

create table #tmpstore2
(loan_number varchar(15),
descrip varchar(40))
insert into #tmpstore2
SELECT f.loan_number,
case when ba.desc in('Attempting')then 'kloip'
when ba.desc in ('Litigation') then 'Litigation'
END as description
from fcs_master f (nolock)
inner join loss_mitigation fcl(nolock)
on f.loan_id = fcl.loan_id
inner join borrower b(nolock)
on f.loan_id = b.loan_id
inner join business_area_status ba(nolock)
on fcl.business_area_status_id = ba.business_area_status_id

select * from #tmpstore2
drop table #tmpstore2

 

[synapsevampire]

How are you attempting to access this query from Crystal, in a Stored Procedure?

If not, place it in a Stored Procedure as you cannot modify the SQL directly within CR 7. And you should be using the CR supplied ODBC or direct connectivity, though there may be problems with one or the other, that's an old version.

I'm not aware of a limitation of creating tables in a SQL Server SP for those versions, but it's possible.

-k

 

[simma]

Thanks synapsevampire ,
Yes I am using store procedure to execute the sql and I am using direct connectivity..
Thanks

 

[synapsevampire]

Perhaps you could try the CR ODBC driver, though it may not fully support SQL Server 2000, this may be the problem.

-k