Different result between using query and call from ASP

Dear all,
I have a problem, my developer create a store procedure to calculate an asset depreciation. He got different result when executed it between directly from query windows and called it from an ASP page. The right result is from query windows.
When I checked the script there's no strange syntax and the used parameters were simple, branch_id and period : fa_sp_calc_depre @.branch_id, @.period, simple isn't it ?
Assume the structure of the query like below :

insert into xxx
select ... from (
select ... from (
select ... from tblA
union all
select ... from tblB
) x
union all
select ... from tblC
union all
select ... from tblD
) x
left join tblB on ...

One thing that I suspect is the usage of alias x that repeated more than one. Can we used this structure with same alias in a subquery more than one ?

Best regards,

Hery

If the query executed fine directly then it should not behave differently from your UI.

Can you verify the following items,

1. Connection String - check the server, database name, credentials & etc

2. Parameter - Sometimes programmers set sample param (hard-coded value) & etc.

Finally, you can use the Profiler to profile the query (cross-verify).

|||Hi Manivannan thanks for the reply,
I've checked using Profiler and there's nothing with the query, so yesterday I've changed the alias of first subquery into something else. I checked on my computer and it worked fine with right calculation.

But today I found something weird, when our user do the calculation process she got wrong result again. And when I do the process on myu computer (with same program) I got the right result. It getting me more confused now. FYI our program is under web using ASP classic.

Where's the problem anyway ?

Best regards,

Hery|||Hi,
Is anybody help me on this ?

Best regards,

Hery|||

It really sounds like the two different interfaces are hitting two different servers -dev and prod perhaps.

Check the connection string used by the ASP application.

Is it the same server and database that you connect to using the Query windows?

Also, the aliases should not be causing any issue.

|||

Hi Arnie, thanks for the reply...

I've checked into the data and found that datetime field always changed when I executed the sp. For example, the period parameter is 200705 means month May year 2007, inside the sp it will generate the start date = 05/01/2007, but sometimes the value of start date would be 01/05/2007 and reproduce wrong result. I put set dateformat mdy inside the sp.

The question now is why the value of start date always changing since the syntax set it hard-coded (start_date = right(period,2)+'/01/'+left(period,4) ? I thought its value would be mdy, CMIIW.

Best regards,

Hery