different results with the same query

I have SELECT query which include 2 UNION operators.
SELECT ....
UNION
(select....
UNION
selec...)as t1...
If I execute this query in query analyzer, I get 7 records as a result.
If I create new SP and copy the same query into this new STORED PROCEDURE
and execute this procedure,
I get 13 records as a result.
How is that possible that absolutly the same sintax return diferent results?
Both queries are executed on the same database with sa account.
It seems to me that if I execute this query in query analyzer, it return
results as without the union.
Does anybody know why?
This is the first time for me experiencing something like this in 7 years.
Regards,
Simonno It should not. check somebody might have updated mean while.
Post complete script if you find problem
--
Regards
R.D
--Knowledge gets doubled when shared
"simon" wrote:

> I have SELECT query which include 2 UNION operators.
> SELECT ....
> UNION
> (select....
> UNION
> selec...)as t1...
> If I execute this query in query analyzer, I get 7 records as a result.
> If I create new SP and copy the same query into this new STORED PROCEDURE
> and execute this procedure,
> I get 13 records as a result.
> How is that possible that absolutly the same sintax return diferent result
s?
> Both queries are executed on the same database with sa account.
> It seems to me that if I execute this query in query analyzer, it return
> results as without the union.
> Does anybody know why?
> This is the first time for me experiencing something like this in 7 years.
> Regards,
> Simon
>
>|||nobody changed anything.
I execut the procedure in query analyzer: exec dbo.test
OR I execute script below in query analyzer(without "create procedure
dbo.test as" text)
and I get different results.
Amazing, isn't it? Both scripts are executed in the same query analyzer
window with the Sa account butt different result.
Is there some bug or what?
I also try to execute the script with SQL2005 SQL server managment studio
but the same result.
The script is:
CREATE PROCEDURE dbo.test
AS
declare @.idIzdelka varchar(20),@.idDrzave char(3)
set @.idIzdelka='I2314'
set @.idDrzave='CZK'
SELECT
Tk.st_nar_dob,nerazdeljena=sum(Tk.nerazdeljena),razdeljena=sum(Tk.razdeljena
),skupaj=sum(Tk.skupaj),
dobava=(SELECT rok_dobave FROM narDobIzdNed WHERE st_nar_dob=tk.st_nar_dob
AND izd_id=@.idIzdelka)FROM
(SELECT
st_nar_dob=T2.navisionId,sum(isnull(T2.navKolicina,0))-sum(T2.nar_kolicina)-
sum(isnull(T2.zakljucenaKol,0))
as nerazdeljena,
sum(T2.nar_kolicina)+sum(isnull(T2.zakljucenaKol,0)) as
razdeljena,sum(isnull(T2.navKolicina,0)) as skupaj FROM
(SELECT T1.*,zalogaNavision=(SELECT kolicina FROM skladisceIzdelek WHERE
navisionId=t1.navisionId
AND idIzdelka=@.idIzdelka),
navKolicina=(SELECT isnull(kolicina_dej,kolicina_nar) FROM narDobIzdNed
WHERE st_nar_dob=t1.navisionId AND izd_id=@.idIzdelka),
zakljucenaKol=(SELECT sum(nar_kolicina) FROM narociloIzdelek WHERE
navisionID=t1.navisionId AND izd_id=@.idIzdelka and izd_zakljucen=1)
FROM
(SELECT sum(n2.nar_kolicina) as nar_Kolicina,n2.navisionId FROM
(select n1.nar_id,n1.izd_id,max(n1.datum_spremembe) as datumSpremembe FROM
narociloIzdelek n1
GROUP BY n1.nar_id,n1.izd_id)AS T1
INNER JOIN narociloIzdelek n2 ON T1.nar_id=n2.nar_id AND T1.izd_id=n2.izd_id
AND T1.datumSpremembe=n2.datum_spremembe
INNER JOIN narocilo n ON n2.nar_id=n.nar_id INNER JOIN skladisce s ON
n.nar_skladisce_id=s.skladisce_id
INNER JOIN uporabnik u ON u.up_id=n.nar_up_id
WHERE n.nar_status=2 AND n2.izd_zakljucen=0 AND n2.izd_id=@.idIzdelka and
n2.nar_kolicina<>0
and not (n2.navisionId is null OR n2.navisionId='')AND
s.skladisce_drzava_id=@.idDrzave
GROUP BY n2.navisionId)as T1 )as T2 GROUP BY T2.navisionId
UNION
SELECT DISTINCT
T1. st_nar_dob,nerazdeljena=isnull(kolicina_
dej,kolicina_nar),0 as
razdeljena,
skupaj=isnull(kolicina_dej,kolicina_nar)
from--vse, ki so pod sprosti
(select st_nar_dob from narDobIzdNed WHERE izd_id=@.idIzdelka and (naZalogo
is null OR odobri=1)
UNION --vse, ki so obviseli z navision id-jem in so prav tako pod sprosti
select st_nar_dob=navisionID from skladisceIzdelek WHERE navisionID is not
null AND idSkladisca=4 AND idIzdelka=@.idIzdelka)
as T1 INNER JOIN narDobIzdNed n ON T1.st_nar_dob=n.st_nar_dob AND
n.izd_id=@.idIzdelka
WHERE T1.st_nar_dob not in(SELECT n2.navisionID FROM
(select n1.nar_id,n1.izd_id,max(n1.datum_spremembe) as datumSpremembe FROM
narociloIzdelek n1
WHERE n1.izd_id=@.idIzdelka GROUP BY n1.nar_id,n1.izd_id)AS T1
INNER JOIN narociloIzdelek n2 ON T1.nar_id=n2.nar_id AND T1.izd_id=n2.izd_id
AND T1.datumSpremembe=n2.datum_spremembe WHERE n2.izd_id=@.idIzdelka))as Tk
GROUP BY Tk.st_nar_dob
regards,S
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:726AB188-438B-4DBA-8CE6-DA4E1C35CEC1@.microsoft.com...
> no It should not. check somebody might have updated mean while.
> Post complete script if you find problem
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "simon" wrote:
>|||-- Check the settings for ansi nulls for the two stored procedures
-- which I believe are set when the stored procedure are created.
-- This could affect your results.
select name, OBJECTPROPERTY ( id , 'ExecIsAnsiNullsOn' ) as
ExecIsAnsiNullsOn
from sysobjects
where OBJECTPROPERTY ( id , 'IsProcedure' )=1
and name in ('test','?')|||thank you, that was the right answer.
In query analyzer is set to ON.
But I don't understand, I don't compare NULL=NULL anywhere in my query.
Does that UNION operator do internally?
I thought that SET ANSI NULLS ON or OFF affects only comparisations.
Regards,
Simon
<markc600@.hotmail.com> wrote in message
news:1128425201.376908.171720@.o13g2000cwo.googlegroups.com...
> -- Check the settings for ansi nulls for the two stored procedures
> -- which I believe are set when the stored procedure are created.
> -- This could affect your results.
> select name, OBJECTPROPERTY ( id , 'ExecIsAnsiNullsOn' ) as
> ExecIsAnsiNullsOn
> from sysobjects
> where OBJECTPROPERTY ( id , 'IsProcedure' )=1
> and name in ('test','?')
>|||On Tue, 4 Oct 2005 14:33:11 +0200, simon wrote:

>thank you, that was the right answer.
>In query analyzer is set to ON.
>But I don't understand, I don't compare NULL=NULL anywhere in my query.
>Does that UNION operator do internally?
>I thought that SET ANSI NULLS ON or OFF affects only comparisations.
Hi Simon,
Your query has many comparisons between columns, such as
WHERE st_nar_dob=t1.navisionId (random snippet)
If both st_nar_dob and t1.navisionId can hold NULL, then there will be
combinations that test as equal with SET ANSI_NULLS OFF, but unequal
with standard ANSI null handling.
As far as I know, both explicit DISTINCT and the implied DISTINCT in the
UNION operator are not affected by ANSI_NULLS setting - but I'm not 100%
sure.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Different results when updating a cube

I've updated an OLAP cube and I can see the new cell value in my report.

But I was surprised when I tried to execute a MDX against the changes I made and compared it with the data of my cube in the reportin-Services.

I updated a cube cell with the value of 1

The report showed - in fact - 1; but the MDX-query got 0.99999 back.

Why is their a different?

Any idea?

Florian

If the cell you updated was not right down at the leaf level, then AS would have split up the value you were allocating across all the leaf level cells under the one that you updated. My guess is that this has most likely resulted in a small rounding error. The report is probably just set to format to a given number of decimal places an is rounding up.

Different results when executing from .NET component compare to executing from SQL Managem

Hi all,

I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.
This started to really annoying me, any thoughts or solution?
Thanks very much guys

I'm interested in some more details: what's the meaning of different results? Returning different data (rows)? Or same data (rows) in different orders? Can you post the stored proceudre if it does not contain too many statements?

Since recomplie the stored procedure can solve the issue, it seems SQL optimizer chooses different execution plans, thus may lead to different results. One work around is to call the stored procedure with recomplie:

EXECUTEyourSPName WITH RECOMPILE

However this is not a so good solution, as RECOMPLIE at each execution will impact SQL performance.

|||

The result that come back seems to be from the old version of the stored procedure, or could be it's joining differently.
I am suspecting that .NET SQL provider has a seperate query plan cache. The actual select statement is like this, I removed few lines in the where clause and column selections.
SELECT
main.*,fa.*
FROM [NetFare] main
INNER JOIN dbo.Agent fa
ON main.FareId = fa.FareId
LEFT OUTER JOIN travel.dbo.currencies c
ON c.Code = main.FareCurrency
Where

AND (PriceReturnType = @.ReturnType)
And main.PortSetID in (Select Distinct PortSetID
From FarePortSetMember Where PortCode = @.DestinationPort Or PortCode = @.DestinationCity)
And main.OriginPortSetId in (Select Distinct FarePortSetId
From FarePortSetMember Where PortCode = @.OriginPort Or PortCode = @.OriginCity)
AND ((DateFrom <= @.EarliestDepartureDate) AND (DateTo >= @.EarliestDepartureDate))
AND (DATEDIFF(day, TicketDateFrom,GetDate()) >= 0 AND DATEDIFF(day, TicketDateTo, getdate()) <=0)
And status in ('Active','Updating')

Option (KEEPFIXED PLAN)

I just added the OPTION (KEEPFIXED PLAN) and have been monitoring to see if the problem happens again.
So there is no fancy stuff in the query, but it seems to me that the .NET SQL provider are using different query plan.

Could MVP dudes verify this for us, please?

I am using SQL 2000 by the way and .NET 1.1 component called from ASP.NET 2.0