One hell of a problem (SQL2K)

So...we have a SQL server and we are using a query to build a basis for a
report. We just recently noticed that depending on which machine you run the
query from you get a different answer.
Me = WinXpSp2 MDAC 2.8sp1 Retrieving incorrect results
Coworker= WinXpSp2 MDAC 2.8sp1 Retrieves teh correct results
She gets like 6 returns and I get 189. What would casuse this? Where
should I start looking?
Here's the query:
SELECT P.CREATEDDT, P.CREATEDBY,
'MEASURE' OBJECT_TYPE,
P.PROJECTID OBJECTID,
P.PROJECTID,
P.TITLE,
' ' OTHER,
'Missing Make Model or Serial' ERROR,
P.PMC,
TS.STATUS,
TS.TRANSACTIONID,
''OTHER_PROJ,''OTHER_TITLE
FROM(SELECT * FROM ACTIONATTR WHERE ATTRNAME IN ('MAKE','MODEL','SERIAL'))
ATT INNER JOIN ACTIONSUMMARY ACSON
ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
ACS.PROJECTID = P.PROJECTID LEFT JOIN LINK_MEAS_INCENT LMION
ACS.MEASUREID = LMI.MEASUREIDLEFT JOIN VIEW_TRANSACTION_SUM TS ON
LMI.INCENTIVEID = TS.INCENTIVEID
WHERE P.PROJECTID IN(
SELECT PROJECTID FROMACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACSON
ATT.MEASUREID = ACS.MEASUREID
WHERE ACS.CATEGORY IN ('ECWEE142','ECWEE16','ECWEE18','ECWEG142','ECWEG1 6',
'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142 ',
'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISH E',
'FHEDISHG')
AND ATTRNAME IN ('MAKE','MODEL','SERIAL')
AND (ATTRVALUE IS NULL OR ATTRVALUE=''))
AND P.STATUS <>'ABANDON' AND P.PMC = 'HP' AND P.PROJECTID
<>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
AND IMPORTEDDT IS NULL
UNION
SELECT*,
(SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
ATT.MEASUREID = ACS.MEASUREID
WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_PROJ,
(SELECT TITLE FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
ATT.MEASUREID = ACS.MEASUREID
INNER JOIN PROJECT P ON ACS.PROJECTID = P.PROJECTID
WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_TITLE
FROM
(SELECT P.CREATEDDT,P.CREATEDBY,
'MEASURE' TYPE,
ATT.MEASUREID,
P.PROJECTID,
P.TITLE,
ATTRVALUE OTHER,
'Duplicate Serial Numbers' ERROR,
P.PMC,
TS.STATUS,
TS.TRANSACTIONID
FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME = 'SERIAL') ATT INNER JOIN
ACTIONSUMMARY ACSON
ATT.MEASUREID = ACS.MEASUREIDINNER JOIN PROJECT P ON
ACS.PROJECTID = P.PROJECTID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
P.PROJECTID = TS.PROJECTID
WHERE ATTRVALUE IN
(SELECT ATTRVALUE FROM ACTIONATTR
WHERE ATTRNAME IN ('SERIAL')
AND ATTRVALUE IS NOT NULL AND ATTRVALUE <> ' '
GROUP BY ATTRVALUE
HAVING COUNT(ATTRVALUE) >1)
AND ATTRNAME IN ('HEFSERIAL', 'SERIAL') AND P.PMC = 'HP' AND P.STATUS
<>'ABANDON' AND P.PROJECTID <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
AND IMPORTEDDT IS NULL)K
If you both are executing this through Query Analyzer, I'd
start by making sure you have the same connection settings,
properties on both PCs. From the menu in Query Analyzer,
Tools -> Options.
-Sue
On Mon, 2 Oct 2006 16:41:01 -0700, Karl
<Karl@.discussions.microsoft.com> wrote:

>So...we have a SQL server and we are using a query to build a basis for a
>report. We just recently noticed that depending on which machine you run the
>query from you get a different answer.
>Me = WinXpSp2 MDAC 2.8sp1 Retrieving incorrect results
>Coworker= WinXpSp2 MDAC 2.8sp1 Retrieves teh correct results
>She gets like 6 returns and I get 189. What would casuse this? Where
>should I start looking?
>Here's the query:
>
>SELECT P.CREATEDDT, P.CREATEDBY,
>'MEASURE' OBJECT_TYPE,
>P.PROJECTID OBJECTID,
>P.PROJECTID,
>P.TITLE,
>' ' OTHER,
>'Missing Make Model or Serial' ERROR,
>P.PMC,
>TS.STATUS,
>TS.TRANSACTIONID,
>''OTHER_PROJ,''OTHER_TITLE
>FROM(SELECT * FROM ACTIONATTR WHERE ATTRNAME IN ('MAKE','MODEL','SERIAL'))
>ATT INNER JOIN ACTIONSUMMARY ACSON
>ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
>ACS.PROJECTID = P.PROJECTID LEFT JOIN LINK_MEAS_INCENT LMION
>ACS.MEASUREID = LMI.MEASUREIDLEFT JOIN VIEW_TRANSACTION_SUM TS ON
>LMI.INCENTIVEID = TS.INCENTIVEID
>WHERE P.PROJECTID IN(
>SELECT PROJECTID FROMACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACSON
>ATT.MEASUREID = ACS.MEASUREID
>WHERE ACS.CATEGORY IN ('ECWEE142','ECWEE16','ECWEE18','ECWEG142','ECWEG1 6',
>'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142 ',
>'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISH E',
>'FHEDISHG')
>AND ATTRNAME IN ('MAKE','MODEL','SERIAL')
>AND (ATTRVALUE IS NULL OR ATTRVALUE=''))
> AND P.STATUS <>'ABANDON' AND P.PMC = 'HP' AND P.PROJECTID
><>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
>AND IMPORTEDDT IS NULL
>
>UNION
>SELECT*,
>(SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
>ATT.MEASUREID = ACS.MEASUREID
> WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_PROJ,
>(SELECT TITLE FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
>ATT.MEASUREID = ACS.MEASUREID
>INNER JOIN PROJECT P ON ACS.PROJECTID = P.PROJECTID
> WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_TITLE
>FROM
>(SELECT P.CREATEDDT,P.CREATEDBY,
>'MEASURE' TYPE,
>ATT.MEASUREID,
>P.PROJECTID,
>P.TITLE,
>ATTRVALUE OTHER,
>'Duplicate Serial Numbers' ERROR,
>P.PMC,
>TS.STATUS,
>TS.TRANSACTIONID
>FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME = 'SERIAL') ATT INNER JOIN
>ACTIONSUMMARY ACSON
>ATT.MEASUREID = ACS.MEASUREIDINNER JOIN PROJECT P ON
>ACS.PROJECTID = P.PROJECTID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
>P.PROJECTID = TS.PROJECTID
>WHERE ATTRVALUE IN
>(SELECT ATTRVALUE FROM ACTIONATTR
>WHERE ATTRNAME IN ('SERIAL')
>AND ATTRVALUE IS NOT NULL AND ATTRVALUE <> ' '
>GROUP BY ATTRVALUE
>HAVING COUNT(ATTRVALUE) >1)
>AND ATTRNAME IN ('HEFSERIAL', 'SERIAL') AND P.PMC = 'HP' AND P.STATUS
><>'ABANDON' AND P.PROJECTID <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
>AND IMPORTEDDT IS NULL)K
>
|||Sue - good thoughts. This is happening all across out network. Some
machines get 35 results and some get 189. We noticed because we switched
from Crystal X to Crystal XI (they are on different servers).
The connection settings in query analyzer are the same. All default.
Good thought, I didn't think to check there.
This also happens when querying through ODBC connections. And query
analyzer. I don't even know where to begin.
Any more ideas?
And thanks so much for replying!
"Sue Hoegemeier" wrote:

> If you both are executing this through Query Analyzer, I'd
> start by making sure you have the same connection settings,
> properties on both PCs. From the menu in Query Analyzer,
> Tools -> Options.
> -Sue
> On Mon, 2 Oct 2006 16:41:01 -0700, Karl
> <Karl@.discussions.microsoft.com> wrote:
>
>
|||Are you using the same login? Perhaps the same tables aren't hit, because same tables exists with
different object owners?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Karl" <Karl@.discussions.microsoft.com> wrote in message
news:467A9CF7-0739-43D9-98E8-EC62BE9A8806@.microsoft.com...
> So...we have a SQL server and we are using a query to build a basis for a
> report. We just recently noticed that depending on which machine you run the
> query from you get a different answer.
> Me = WinXpSp2 MDAC 2.8sp1 Retrieving incorrect results
> Coworker= WinXpSp2 MDAC 2.8sp1 Retrieves teh correct results
> She gets like 6 returns and I get 189. What would casuse this? Where
> should I start looking?
> Here's the query:
>
> SELECT P.CREATEDDT, P.CREATEDBY,
> 'MEASURE' OBJECT_TYPE,
> P.PROJECTID OBJECTID,
> P.PROJECTID,
> P.TITLE,
> ' ' OTHER,
> 'Missing Make Model or Serial' ERROR,
> P.PMC,
> TS.STATUS,
> TS.TRANSACTIONID,
> ''OTHER_PROJ,''OTHER_TITLE
> FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME IN ('MAKE','MODEL','SERIAL'))
> ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> ACS.PROJECTID = P.PROJECTID LEFT JOIN LINK_MEAS_INCENT LMI ON
> ACS.MEASUREID = LMI.MEASUREID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> LMI.INCENTIVEID = TS.INCENTIVEID
> WHERE P.PROJECTID IN(
> SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID
> WHERE ACS.CATEGORY IN ('ECWEE142','ECWEE16','ECWEE18','ECWEG142','ECWEG1 6',
> 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142 ',
> 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISH E',
> 'FHEDISHG')
> AND ATTRNAME IN ('MAKE','MODEL','SERIAL')
> AND (ATTRVALUE IS NULL OR ATTRVALUE=''))
> AND P.STATUS <>'ABANDON' AND P.PMC = 'HP' AND P.PROJECTID
> <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> AND IMPORTEDDT IS NULL
>
> UNION
> SELECT *,
> (SELECT PROJECTID FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID
> WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_PROJ,
> (SELECT TITLE FROM ACTIONATTR ATT INNER JOIN ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID
> INNER JOIN PROJECT P ON ACS.PROJECTID = P.PROJECTID
> WHERE ATTRVALUE =OTHER AND ATT.MEASUREID <>K.MEASUREID)OTHER_TITLE
> FROM
> (SELECT P.CREATEDDT,P.CREATEDBY,
> 'MEASURE' TYPE,
> ATT.MEASUREID,
> P.PROJECTID,
> P.TITLE,
> ATTRVALUE OTHER,
> 'Duplicate Serial Numbers' ERROR,
> P.PMC,
> TS.STATUS,
> TS.TRANSACTIONID
> FROM (SELECT * FROM ACTIONATTR WHERE ATTRNAME = 'SERIAL') ATT INNER JOIN
> ACTIONSUMMARY ACS ON
> ATT.MEASUREID = ACS.MEASUREID INNER JOIN PROJECT P ON
> ACS.PROJECTID = P.PROJECTID LEFT JOIN VIEW_TRANSACTION_SUM TS ON
> P.PROJECTID = TS.PROJECTID
> WHERE ATTRVALUE IN
> (SELECT ATTRVALUE FROM ACTIONATTR
> WHERE ATTRNAME IN ('SERIAL')
> AND ATTRVALUE IS NOT NULL AND ATTRVALUE <> ' '
> GROUP BY ATTRVALUE
> HAVING COUNT(ATTRVALUE) >1)
> AND ATTRNAME IN ('HEFSERIAL', 'SERIAL') AND P.PMC = 'HP' AND P.STATUS
> <>'ABANDON' AND P.PROJECTID <>'P00000000817'ANd P.PROGRAMCODE <>'DYS'
> AND IMPORTEDDT IS NULL)K
>
|||I'm using the same login with both.
Any other thoughts?
"Tibor Karaszi" wrote:

> Are you using the same login? Perhaps the same tables aren't hit, because same tables exists with
> different object owners?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Karl" <Karl@.discussions.microsoft.com> wrote in message
> news:467A9CF7-0739-43D9-98E8-EC62BE9A8806@.microsoft.com...
>
|||Any differences when you look at the two in syscacheobjects?
I would guess you would find two different bucket ids to
identify them - you might get some clues with the values
there.
Any differences when you look at the execution plans on each
PC?
Other than that, have you tried just first executing each
select that are being unioned to narrow it down some? Or are
both results different on the two machines where you can
reproduce this issue.
-Sue
On Tue, 3 Oct 2006 09:00:01 -0700, Karl
<Karl@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue - good thoughts. This is happening all across out network. Some
>machines get 35 results and some get 189. We noticed because we switched
>from Crystal X to Crystal XI (they are on different servers).
>The connection settings in query analyzer are the same. All default.
>Good thought, I didn't think to check there.
>This also happens when querying through ODBC connections. And query
>analyzer. I don't even know where to begin.
>Any more ideas?
>And thanks so much for replying!
>"Sue Hoegemeier" wrote:
|||How do I find my specific query in syschacheobjects? There's 20,000 items in
there or so.........
And what do you mean by differences in the execution plans on each PC? I
guess I don't get what your asking here.
Thanks so much for you help so far!!
(waiting for the other user to get back to run the selects seperately)
"Sue Hoegemeier" wrote:

> Any differences when you look at the two in syscacheobjects?
> I would guess you would find two different bucket ids to
> identify them - you might get some clues with the values
> there.
> Any differences when you look at the execution plans on each
> PC?
> Other than that, have you tried just first executing each
> select that are being unioned to narrow it down some? Or are
> both results different on the two machines where you can
> reproduce this issue.
> -Sue
> On Tue, 3 Oct 2006 09:00:01 -0700, Karl
> <Karl@.discussions.microsoft.com> wrote:
>
>
|||1. Something like:
select *
from master..syscacheobjects
where sql like '<beginning of your sql statement> %'
2. Display the execution plan for the query on each PC. If
there is a difference, that might lead you to the issue.
In Query Analyzer, from the menu go to Query and select
Display Estimated Execution Plan.
-Sue
On Tue, 3 Oct 2006 12:40:02 -0700, Karl
<Karl@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>How do I find my specific query in syschacheobjects? There's 20,000 items in
>there or so.........
>And what do you mean by differences in the execution plans on each PC? I
>guess I don't get what your asking here.
>Thanks so much for you help so far!!
>(waiting for the other user to get back to run the selects seperately)
>"Sue Hoegemeier" wrote:
|||1. -They both look good.........
2. The execution plan looks identical too.
Damn.......
"Sue Hoegemeier" wrote:

> 1. Something like:
> select *
> from master..syscacheobjects
> where sql like '<beginning of your sql statement> %'
> 2. Display the execution plan for the query on each PC. If
> there is a difference, that might lead you to the issue.
> In Query Analyzer, from the menu go to Query and select
> Display Estimated Execution Plan.
> -Sue
> On Tue, 3 Oct 2006 12:40:02 -0700, Karl
> <Karl@.discussions.microsoft.com> wrote:
>
>
|||We are getting different results in the select statements - I broke it down
into two queries and we are getting different results there too. My
co-worker is getting 39, and then 23 while I am getting 276 and then 158.
Thats "alittle bit" of a difference.
"Sue Hoegemeier" wrote:

> 1. Something like:
> select *
> from master..syscacheobjects
> where sql like '<beginning of your sql statement> %'
> 2. Display the execution plan for the query on each PC. If
> there is a difference, that might lead you to the issue.
> In Query Analyzer, from the menu go to Query and select
> Display Estimated Execution Plan.
> -Sue
> On Tue, 3 Oct 2006 12:40:02 -0700, Karl
> <Karl@.discussions.microsoft.com> wrote:
>
>