old sql2k5 folder: machine

Showing posts with label machine. Show all posts

Friday, March 30, 2012

One Node One Machine Cluster?

Hi,
I am in the process of building a SQL Server "farm," but having to tone down
the pace due to $$. I have just purchased a RAID1 (2 disc/10K rpm/146 GB)
Channel 0, RAID5 (3 disc/10K rpm/146 GB) Channel 1 server with 2 CPU's. This
summer, I plan to buy an identical machine. They will both run Win 2003 EE,
with SQL Server 2000 EE. Rather than install SQL Server locally on my
existing machine, is there a problem with installing MSCS and a virtual
server, even though I only have the one machine now. Of course, it won't
give me failover protection, but it would give me the configuration I want
when I get my second box. Does this make any sense? Any caveats or words of
wisdom?
PS. The servers will be accessed in OTLP mode by an ADO.NET app running on
Terminal Server (Win 2003), and BizTalk Server 2004 - also on a Win 2003 box.
Most of the ADO.NET traffic will occur during business hours, with BTS
getting busy after hours.
Thanks.
John
Hi
Why don't you rather build a single node cluster? Build a normal cluster
node, but not add another node. Once you have bought your other machine, add
it to the cluster.
Just be aware, it is very difficult to get the exact same hardware months
apart. If you are going to use clustering, make sure all the hardware is on
the Windows Hardware Compatibility List (HCL).
A cluster "on the cheap" will just cause you hassles later. I've seen enough
"lets do it with a s little as we can", and the cluster ends up with worse
uptime than a single machine.
Regards
Mike
"JT" wrote:

> Hi,
> I am in the process of building a SQL Server "farm," but having to tone down
> the pace due to $$. I have just purchased a RAID1 (2 disc/10K rpm/146 GB)
> Channel 0, RAID5 (3 disc/10K rpm/146 GB) Channel 1 server with 2 CPU's. This
> summer, I plan to buy an identical machine. They will both run Win 2003 EE,
> with SQL Server 2000 EE. Rather than install SQL Server locally on my
> existing machine, is there a problem with installing MSCS and a virtual
> server, even though I only have the one machine now. Of course, it won't
> give me failover protection, but it would give me the configuration I want
> when I get my second box. Does this make any sense? Any caveats or words of
> wisdom?
> PS. The servers will be accessed in OTLP mode by an ADO.NET app running on
> Terminal Server (Win 2003), and BizTalk Server 2004 - also on a Win 2003 box.
> Most of the ADO.NET traffic will occur during business hours, with BTS
> getting busy after hours.
> Thanks.
> --
> John

One ms sql server 2000 instance blocks the other (thru a dts-job)

Hello,

we use two instances of the ms sql server 2000 (Version: 8.00.760) on a
4 processor windows 2000 (sp 4) machine.
A dts-packet on one instance completly blocks the work on the other
instance. Both instances have 2 dedicated processors. The two
processors of the blocked instance are in idle states.
How is this possible? Has someone had the same behavior of the sql
server and found a solution for this problem?

Thanks in advance for answers

Jrgen Simonsen(juergen.simonsen@.web.de) writes:
> we use two instances of the ms sql server 2000 (Version: 8.00.760) on a
> 4 processor windows 2000 (sp 4) machine.
> A dts-packet on one instance completly blocks the work on the other
> instance. Both instances have 2 dedicated processors. The two
> processors of the blocked instance are in idle states.
> How is this possible? Has someone had the same behavior of the sql
> server and found a solution for this problem?

Have you tried to access that blocked instance, to see if it is possible
to run queries on it? Have you checked the SQL Server error log for the
blocked instance?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

One hell of a problem (SQL2K)

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:
>
>

One hell of a problem (SQL2K)

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 sa
me 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','ECWEG14
2','ECWEG16'
,
> 'ECWEG18','ECWGE142','ECWGE16','ECWGE18'
,'ECWGG142',
> 'ECWGG16','ECWGG18','EHADISHE','EHADISHG
','FHEDISHE',
> '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 i
n
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.

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 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','ECWEG16',
'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
'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)KIf 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 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','ECWEG16',
> 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
> 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
> '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
>|||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:
> >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','ECWEG16',
> > 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
> > 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
> > '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
> >
>|||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','ECWEG16',
> 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
> 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
> '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...
> > 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','ECWEG16',
> > 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
> > 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
> > '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
> >
> >
>|||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:
>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:
>> >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','ECWEG16',
>> > 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
>> > 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
>> > '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
>> >
>>|||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:
> >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:
> >>
> >> >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','ECWEG16',
> >> > 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
> >> > 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
> >> > '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
> >> >
> >>
> >>
>|||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:
>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:
>> >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:
>> >>
>> >> >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','ECWEG16',
>> >> > 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
>> >> > 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
>> >> > '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
>> >> >
>> >>
>> >>
>>|||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:
> >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:
> >>
> >> >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:
> >> >>
> >> >> >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','ECWEG16',
> >> >> > 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
> >> >> > 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
> >> >> > '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
> >> >> >
> >> >>
> >> >>
> >>
> >>
>|||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:
> >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:
> >>
> >> >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:
> >> >>
> >> >> >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','ECWEG16',
> >> >> > 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
> >> >> > 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
> >> >> > '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
> >> >> >
> >> >>
> >> >>
> >>
> >>
>|||Your probably going to have to walk through one of those to
determine where the issue is and then go from there. Once
you narrow it down to a particular table, join or where
condition, it will probably be easier to solve.
-Sue
On Tue, 3 Oct 2006 15:00: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:
>> >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:
>> >>
>> >> >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:
>> >> >>
>> >> >> >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','ECWEG16',
>> >> >> > 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
>> >> >> > 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
>> >> >> > '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
>> >> >> >
>> >> >>
>> >> >>
>> >>
>> >>
>>|||Shot in the dark...
- Drop all indexes and recreate them
or
- Create tables with the same columns as the tables involved, but with
different table names. Export data out of the tables involved and load into
the new tables. Rewrite the query to use the new tables and see if there is
any different results.
Also, if I recall, there is a utility on the MDAC page that checks the
version of all MDAC components. See if anything strange is reported.
"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','ECWEG16',
> 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
> 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
> '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'll have to try the indexes.........good thought. Its HUGE tables
something could be outta whack...
I downloaded CCcheck from the MS website and the MDACs are the same.
"JJ" wrote:
> Shot in the dark...
> - Drop all indexes and recreate them
> or
> - Create tables with the same columns as the tables involved, but with
> different table names. Export data out of the tables involved and load into
> the new tables. Rewrite the query to use the new tables and see if there is
> any different results.
> Also, if I recall, there is a utility on the MDAC page that checks the
> version of all MDAC components. See if anything strange is reported.
>
> "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','ECWEG16',
> > 'ECWEG18','ECWGE142','ECWGE16','ECWGE18','ECWGG142',
> > 'ECWGG16','ECWGG18','EHADISHE','EHADISHG','FHEDISHE',
> > '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
> >
> >
>
>sql

Friday, March 30, 2012

One Node One Machine Cluster?

One ms sql server 2000 instance blocks the other (thru a dts-job)

One hell of a problem (SQL2K)

One hell of a problem (SQL2K)

One hell of a problem (SQL2K)

old sql2k5 folder

Blog Archive

About Me