One more question regarding Reporting action

All the reporting action samples I read from AdventureWorks, when pass link and parameter to the report in reporting service, is linked to the report as Analysis Service data source based report.

I am wondering if reporting action can pass parameter to regular relational db based reporting service report not Analysis Service data source based report.

If yes, could you share a simple sample?

I can't see why the data source of a report would matter, when using a reporting action. The Adventure Works examples may all be using an Analysis Services data source just for convenience. Have you tried editing an existing Adventure Works reporting action, to point to a relational report instead?|||

Hi Deepak, thank you so much for your reply! Yes, I did try the following using AW AS and AW sample reports RS projects:

Here is my reporting action:

Name: Sales Order Detail

Target Type: Dimension members

Target object: Internet Sales Order Detail

ReportServer:localhost

ReportPath:ReportServer?/AdventureWorks Sample Reports/Sales Order Detail

Parameters NamealesOrderNumber (Match the one at Sales Order Detail report

Parameter Value: [Internet Sales Order Details].[Sales Order Number].[Sales Order Number].CurrentMember.Name

And I make sure Sales Order Detail Report is running correctly and parameter is matching the parameter name I defined at the cube reporting action. Then I deploy the cube, however when drop the Sale Order Number dimension and right click, there is no Reporting Action showing up.

Could you give me some pointers?

Thanks

|||

Did you configure the "Caption", under "Additional Properties" - if not, you can refer to the Adventure Works "Sales Reason Comparisons" action, whose Caption is defined as an MDX expression:

"Sales Reason Comparisons for " +

[Product].[Category].CurrentMember.Member_Caption + "..."

|||

Thanks again. Just did that and deployed.

"Sales Reason Comparisons for " + [Internet Sales Order Details].[Sales Order Number].[Sales Order Number].CurrentMember.Member_Caption + "..."

However reporting action is still not showing. Deepak, anything else I miss?

Thanks

|||Can't think of anything else - are you able to see the standard Reporting action which comes with Adventure Works, ie. "Sales Reason Comparisons"? If so, I would suggest making a test copy of Adventure Works, and progressively modifying this action to incorporate your report in small steps, till it breaks - or maybe it doesn't break!

one matrix above another doesn't export properly to Excel

A newb question: I've got a report with a table leftmost in the body,
with a matrix immediately to the right of the table. Immediately
below the table are 2 textboxes displaying totals from the table.
Immediately below the first matrix is another matrix displaying totals
from the first matrix:
Table Matrix1
Textboxes Matrix2
All is well in preview, HTML view, and PDF export.
After Excel export, the sheet is arranged thus:
Table Matrix1
Matrix2 Textboxes
I've tried putting the 2 matrices in a rectangle, and even inside
nested rectangles, with no effect on the incorrect Excel layout.
The report is designed this way because the users want empty space
between the detail data (in the Table and Matrix1) and the totals.
The number of columns in Matrix 1 is dynamic and so I need Matrix2 to
expand in step with Matrix1.
I think. Help? RDL available if anybody wants it.
Thanks
MatrixLoverI am running into similar problem. I have one matrix over another and report
logic hide / unhide matrix. Every work like charm in reports, export to pdf,
but having trouble in excel export. When export to excel, the hidden matrix
also appears just below the active matrix. Any help appreciated.
"MatrixLover" wrote:
> A newb question: I've got a report with a table leftmost in the body,
> with a matrix immediately to the right of the table. Immediately
> below the table are 2 textboxes displaying totals from the table.
> Immediately below the first matrix is another matrix displaying totals
> from the first matrix:
> Table Matrix1
> Textboxes Matrix2
> All is well in preview, HTML view, and PDF export.
> After Excel export, the sheet is arranged thus:
> Table Matrix1
> Matrix2 Textboxes
> I've tried putting the 2 matrices in a rectangle, and even inside
> nested rectangles, with no effect on the incorrect Excel layout.
> The report is designed this way because the users want empty space
> between the detail data (in the Table and Matrix1) and the totals.
> The number of columns in Matrix 1 is dynamic and so I need Matrix2 to
> expand in step with Matrix1.
> I think. Help? RDL available if anybody wants it.
> Thanks
> MatrixLover
>

One insert box per individual name?

Hi,

My Crystal Report has a box around each section (detail sections a&b). So, for example, John Doe might have 3 different sections and appear within 3 boxes. But now my client would like this box to group for each individual name, meaning John Doe's data should only be in one box. How would I do this? I'm new to Crystal Reports...help!

Please help as soon as possible.

Thanks so much!
LillyGroup your records on that field (click on it-> Insert->Group) place the fields you want to shown in you report into GF then suppress Details section.

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

One failed subscription prevents running all others

Hello,
I was trying to invertigate why a certain report does not run properly in
some cases. I scheduled it to run daily. So far so good, i got logfiles to
study. However, since this try RS no longer executes ANY scheduled
subscription. i made no changes to the system, just scheduled a defective
report. (which BTW was in Excel and the row-count exceded 65536). Does
anybode know how to edit the (hypothetical) queue used by RS to clear the way
for my other subscriptions.
extra info: RSExecutionlog DB has no entries of executed subscriptions. The
SQL job manager says all jobs have executed succesfully as planned.(including
the subscriptions)Are there any rows in either the event table or notifications table?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
news:175AE411-3994-4C8E-993F-8D4D258219B8@.microsoft.com...
> Hello,
> I was trying to invertigate why a certain report does not run properly in
> some cases. I scheduled it to run daily. So far so good, i got logfiles
> to
> study. However, since this try RS no longer executes ANY scheduled
> subscription. i made no changes to the system, just scheduled a defective
> report. (which BTW was in Excel and the row-count exceded 65536). Does
> anybode know how to edit the (hypothetical) queue used by RS to clear the
> way
> for my other subscriptions.
> extra info: RSExecutionlog DB has no entries of executed subscriptions.
> The
> SQL job manager says all jobs have executed succesfully as
> planned.(including
> the subscriptions)
>

One DataRegion(Table) Multiple DataSets

I have a complexed report
It makes use of two queries and 2 tables
I need to use a group in order to display the information correctly,
If I had one query it would have worked perfectly, But the data I am
retrieving is so complexed that I need to make use of two queries other wise
I get duplicate data
Table 1 contains section1, and 2 of the displayed info
Table 2 contains the 3rd section
it looks like this;
Page 1
header
Section1
Section 2
Section 3
Footer
Page 2
header
Section1
Section 2
Section 3
Footer
So in order to accomplish this I take two tables link them to one dataset.
Add a group, But this results in the following. I need page breaks so I set
the page break option in the group properties
Page 1
header
section 1
section2
Footer
Page 2
section1
section2
Page 3
Section 3
Page 4 Section 3
I then put the 2 tables in a list box, and set the grouping on the list, And
This works 100 %. It groups all the data brilliantly. The problem is I cant
use one query, I need to use two!
SO Is their a work around or some way to link 2 datasets to one list
control.By adding the full path or something. The only way I can currently
reference more than one dataset per table is by using aggeragate funtions.
But =First(Fields!SIZE.Value, "DataSet2") will only return the top 1 result
so that doesnt work I tried (Fields!SIZE.Value, "DataSet2") but that returns
an errorData regions, in SQL Server 2000 Reporting Services, can only be bound to a
single data set with once exception: All secondary data references must be
contained in an aggregate function with the dataset specified. For example,
First(=Fields!<SomeField>.Value), "<SomeDataSet>"), is allowed. To achieve
the effect you want will have to be done in the query. Some of the tools
available to you are joins, unions, openrowset, or linked servers.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griffen" <Griffen@.discussions.microsoft.com> wrote in message
news:B7A2C3C0-4522-466A-B5D2-08ECCD3471C1@.microsoft.com...
> I have a complexed report
> It makes use of two queries and 2 tables
> I need to use a group in order to display the information correctly,
> If I had one query it would have worked perfectly, But the data I am
> retrieving is so complexed that I need to make use of two queries other
wise
> I get duplicate data
> Table 1 contains section1, and 2 of the displayed info
> Table 2 contains the 3rd section
> it looks like this;
> Page 1
> header
> Section1
> Section 2
> Section 3
> Footer
>
> Page 2
> header
> Section1
> Section 2
> Section 3
> Footer
> So in order to accomplish this I take two tables link them to one dataset.
> Add a group, But this results in the following. I need page breaks so I
set
> the page break option in the group properties
>
> Page 1
> header
> section 1
> section2
> Footer
> Page 2
> section1
> section2
> Page 3
> Section 3
> Page 4 Section 3
> I then put the 2 tables in a list box, and set the grouping on the list,
And
> This works 100 %. It groups all the data brilliantly. The problem is I
cant
> use one query, I need to use two!
> SO Is their a work around or some way to link 2 datasets to one list
> control.By adding the full path or something. The only way I can currently
> reference more than one dataset per table is by using aggeragate funtions.
> But =First(Fields!SIZE.Value, "DataSet2") will only return the top 1
result
> so that doesnt work I tried (Fields!SIZE.Value, "DataSet2") but that
returns
> an errorsql

One database item per page Reporting Services

I'm creating Reporting Services reports in Visual Studio. I create a new
report, and see the Data, Layout, and Preview tabs.
In the data tab, I say: "select name from mytable". Four names are returned.
I'd like to display one name per page.
I posted this question a few days ago. The folks that responded (thanks)
suggested that in the Layout tab, I could place a table or list on the form,
then drag the "name" from DataSet1 onto the table or list. The problem with
this approach is that all 4 names are grouped on the first page. I'l like to
see one name per page.
In reality, mytable contains dozens of fields which will go on the report
form. I want Reporting Services to print out a one page report for Mr. Able,
Mr. Baker. Mr. Charles, and so on.
--
RandyI think the best way to handle this is with the List control.
In the layout tab drag the list from the toolbox.
You can then drag the name (and any other field) into the list control.
You can then right click on the List control and select properties.
In the General tab, click "Edit details group..."
In the top line of the expression list, choose the name field from the drop
down.
Check the "Page break at end" checkbox.
Each instance of the list will now show up on it's own page. You can drag
whatever fields you want into the list box.
Hope this helps.
"randy1200" wrote:
> I'm creating Reporting Services reports in Visual Studio. I create a new
> report, and see the Data, Layout, and Preview tabs.
> In the data tab, I say: "select name from mytable". Four names are returned.
> I'd like to display one name per page.
> I posted this question a few days ago. The folks that responded (thanks)
> suggested that in the Layout tab, I could place a table or list on the form,
> then drag the "name" from DataSet1 onto the table or list. The problem with
> this approach is that all 4 names are grouped on the first page. I'l like to
> see one name per page.
> In reality, mytable contains dozens of fields which will go on the report
> form. I want Reporting Services to print out a one page report for Mr. Able,
> Mr. Baker. Mr. Charles, and so on.
> --
> Randy|||Hi,
Hey Its simple. Suppose "select name from mytable" is your query.
After dragging table and placing fields as per your wish. Create a group
with "name" field, so whenever name changes it goes to the next page (dont
forget to click on "page break at end".
Amarnath
"randy1200" wrote:
> I'm creating Reporting Services reports in Visual Studio. I create a new
> report, and see the Data, Layout, and Preview tabs.
> In the data tab, I say: "select name from mytable". Four names are returned.
> I'd like to display one name per page.
> I posted this question a few days ago. The folks that responded (thanks)
> suggested that in the Layout tab, I could place a table or list on the form,
> then drag the "name" from DataSet1 onto the table or list. The problem with
> this approach is that all 4 names are grouped on the first page. I'l like to
> see one name per page.
> In reality, mytable contains dozens of fields which will go on the report
> form. I want Reporting Services to print out a one page report for Mr. Able,
> Mr. Baker. Mr. Charles, and so on.
> --
> Randy|||Got it! Many thanks...
--
Randy
"Amarnath" wrote:
> Hi,
> Hey Its simple. Suppose "select name from mytable" is your query.
> After dragging table and placing fields as per your wish. Create a group
> with "name" field, so whenever name changes it goes to the next page (dont
> forget to click on "page break at end".
> Amarnath
> "randy1200" wrote:
> > I'm creating Reporting Services reports in Visual Studio. I create a new
> > report, and see the Data, Layout, and Preview tabs.
> >
> > In the data tab, I say: "select name from mytable". Four names are returned.
> > I'd like to display one name per page.
> >
> > I posted this question a few days ago. The folks that responded (thanks)
> > suggested that in the Layout tab, I could place a table or list on the form,
> > then drag the "name" from DataSet1 onto the table or list. The problem with
> > this approach is that all 4 names are grouped on the first page. I'l like to
> > see one name per page.
> >
> > In reality, mytable contains dozens of fields which will go on the report
> > form. I want Reporting Services to print out a one page report for Mr. Able,
> > Mr. Baker. Mr. Charles, and so on.
> >
> > --
> > Randy|||That's exactly what I needed. Many thanks...
--
Randy
"Limey" wrote:
> I think the best way to handle this is with the List control.
> In the layout tab drag the list from the toolbox.
> You can then drag the name (and any other field) into the list control.
> You can then right click on the List control and select properties.
> In the General tab, click "Edit details group..."
> In the top line of the expression list, choose the name field from the drop
> down.
> Check the "Page break at end" checkbox.
> Each instance of the list will now show up on it's own page. You can drag
> whatever fields you want into the list box.
> Hope this helps.
>
> "randy1200" wrote:
> > I'm creating Reporting Services reports in Visual Studio. I create a new
> > report, and see the Data, Layout, and Preview tabs.
> >
> > In the data tab, I say: "select name from mytable". Four names are returned.
> > I'd like to display one name per page.
> >
> > I posted this question a few days ago. The folks that responded (thanks)
> > suggested that in the Layout tab, I could place a table or list on the form,
> > then drag the "name" from DataSet1 onto the table or list. The problem with
> > this approach is that all 4 names are grouped on the first page. I'l like to
> > see one name per page.
> >
> > In reality, mytable contains dozens of fields which will go on the report
> > form. I want Reporting Services to print out a one page report for Mr. Able,
> > Mr. Baker. Mr. Charles, and so on.
> >
> > --
> > Randy

One column missing from datasets even after refresh?

My report's data source is a stored procedure which takes 4 parameters. If I execute the sp from the data tab it returns the correct data and columns; but if I right click the Report Datasets from the Datasets view and click on refresh I'm always missing one column. Is this related to the way the sp was written? Anybody had same expericence before? Thanks!

I have.

One thing you can do is check for a matching .rdl.data file and blow that away, see if next data pull rewrites the cached data.

Another thing you can probably do is just add it in manually. Rightclick on the *name* of the dataset (underneath the Report DataSets node in the Datasets window) and choose "Add..."

>L<

|||

Thanks for the help! The first method doesn't work. Even if I add a new report from scratch, the available fields are missing that column if I put the sp in the query builder from report build wizard.

Rightclick on the name of the dataset and add the missing column works, but after I deploy the report and go view the report from brower, I got the following message:

An error has occurred during report processing.

Query execution failed for data set 'RegionalLevelByWeek'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

|||Hmm. I think the first method doesn't work if you have a (usually generated) schema file associated

with the data -- I'm not looking at reports right now, so I could be wrong) -- you might have to "refresh" that file as well.

On the second one, do you have to re-deploy the datasource to the server or something? (remember it's "don't overwrite datasources during deploy" by default) Same is true with default values of params -- given your situation as you put it in this message, that might well be involved.

Now that I look at what you're saying here though... As far as your current thing with the two SP's, you could just put a dummy column into the first result set, couldn't you? The important thing to remember is that there is going to be *one* set of columns associated with that report, never two, so if your proc can return differently based on a param you pretty much have to make them match.

I'm sorry I can't do a worked example right now, it's always *something* find-able though...

>L<

sql