|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Query working in EM and QA but not in Store procHow come I have an sql query that I can run in both SQL Server 2000 Enterprise Manager and Query Analyzer, but when I try to run it as a stored procedure, I get a different result. Instead of the expected 505 rows, I get 9425 rows. I haven't bothered to describe the tables yet. Lot of work to get that out. Just want to know if anyone would know why this could happen!?!? Any NULL problems I am not aware of? Tried to turn on and off ANSI NULLS, but no help. Here is the message I get when I run the code in QA and Executes the same code through a store proc: (505 row(s) affected) Warning: Null value is eliminated by an aggregate or other SET operation. (9425 row(s) affected) Warning: Null value is eliminated by an aggregate or other SET operation. By the way, here is the query, just for the hell of it: SELECT DISTINCT hms.hms_substance.sl_id, hms.hms1.hms_id, hms.produkt_prodgruppe.product_name, hms.hms1.trade_name, hms.hms1.manufacturer, hms.hms1.url_hms, hms.hms1.status, hms.owner.owner_iscustomer, hms.subsGroupHms.groupName, hms.hms_substance.company_class, hms.hms_substance.note, rag.riskAssessementGroupID, rag.createdDate, ra.substitutStatus, MAX(ti.date_created) AS date_created, ti.on_obs_list, hms.hms1.hasSwane, ra.conclusionProduct FROM hms.produkt_prodgruppe INNER JOIN hms.hms1 ON hms.produkt_prodgruppe.uproduct_id = hms.hms1.product_id INNER JOIN hms.owner ON hms.hms1.owner_id = hms.owner.owner_id INNER JOIN hms.hms_substance ON hms.hms1.hms_id = hms.hms_substance.hms_id LEFT OUTER JOIN hms.riskAssessementGroup rag INNER JOIN hms.riskAssessement ra ON ra.riskAssessementGroupID = rag.riskAssessementGroupID AND rag.levid = 1000002 ON hms.hms_substance.sl_id = rag.levid AND hms.hms1.msdsid = ra.msdsid LEFT OUTER JOIN hms.subsGroupHms INNER JOIN hms.subsHmsGroupHms ON hms.subsGroupHms.groupID = hms.subsHmsGroupHms.groupID ON hms.hms_substance.sl_id = hms.subsGroupHms.levid AND hms.hms1.msdsid = hms.subsHmsGroupHms.msdsid LEFT OUTER JOIN hms.TIcabinet ti ON hms.hms1.hms_id = ti.hms_id GROUP BY hms.hms1.hms_id, hms.produkt_prodgruppe.product_name, hms.hms1.trade_name, hms.hms1.manufacturer, hms.hms1.url_hms, hms.hms1.status, hms.owner.owner_iscustomer, hms.subsGroupHms.groupName, hms.hms_substance.company_class, hms.hms_substance.note, rag.riskAssessementGroupID, rag.createdDate, ra.substitutStatus, ti.on_obs_list, rag.showStatus, hms.hms1.hasSwane, ra.conclusionProduct, hms.hms_substance.sl_id HAVING (rag.riskAssessementGroupID IN (SELECT MAX(rag.riskAssessementGroupID) AS riskAssessementGroupID FROM hms.riskAssessementGroup rag INNER JOIN hms.riskAssessement ra ON ra.riskAssessementGroupID = rag.riskAssessementGroupID RIGHT OUTER JOIN hms.hms1 hh ON ra.msdsid = hh.msdsid GROUP BY hh.trade_name, hh.manufacturer, rag.levid HAVING EXISTS (SELECT DISTINCT (MAX(r.createdDate)) FROM v_getRiskAssessementGID r GROUP BY r.msdsid, r.levid))) OR (rag.riskAssessementGroupID IS NULL) AND (hms.hms_substance.sl_id = 1000002) ORDER BY hms.hms1.trade_name If the query is run through EM or QA then I get the expected rows with the ID 1000002. If run as a SP the ID 1000002 is just ignored and I get the large resultset. Hope someone can help. If complete table definitions and data is wanted this will be posted. Best regards Henning :-) *** Sent via Developersdex http://www.developersdex.com *** I don't know why it wouldn't work, but I will note that
this part seems to do nothing at all: HAVING EXISTS ( SELECT DISTINCT (MAX(r.createdDate)) FROM v_getRiskAssessementGID r GROUP BY r.msdsid, r.levid) ) That expression is always true, regardless of whether there are any rows in v_getRiskAssessementGID or not. I would first check that you are running the same query each time Run the query in QA, then add create proc p as -- at the top go -- at the bottom exec p If no lock, compare the query plans when you run it as a proc vs. when you run it as a query. If they are identical, make sure everything in the procedure (and the view) is qualified with the owner name. Maybe there is a dbo.X and hms.X for some table and the sp reads a different one. Finally, I would parenthesise the joins of the form JOIN X JOIN Y ON <this> ON <that> If still no luck, try to pare it down to something reproducible to post here. Steve Kass Drew University www.stevekass.com kongsballa wrote: Show quote >Help, strange things happening! > >How come I have an sql query that I can run in both SQL Server 2000 >Enterprise Manager and Query Analyzer, but when I try to run it as a >stored procedure, I get a different result. Instead of the expected 505 >rows, I get 9425 rows. > >I haven't bothered to describe the tables yet. Lot of work to get that >out. Just want to know if anyone would know why this could happen!?!? >Any NULL problems I am not aware of? Tried to turn on and off ANSI >NULLS, but no help. > >Here is the message I get when I run the code in QA and Executes the >same code through a store proc: > >(505 row(s) affected) > >Warning: Null value is eliminated by an aggregate or other SET >operation. > > >(9425 row(s) affected) > >Warning: Null value is eliminated by an aggregate or other SET >operation. > >By the way, here is the query, just for the hell of it: > >SELECT DISTINCT >hms.hms_substance.sl_id, >hms.hms1.hms_id, >hms.produkt_prodgruppe.product_name, >hms.hms1.trade_name, >hms.hms1.manufacturer, >hms.hms1.url_hms, >hms.hms1.status, >hms.owner.owner_iscustomer, >hms.subsGroupHms.groupName, hms.hms_substance.company_class, >hms.hms_substance.note, >rag.riskAssessementGroupID, >rag.createdDate, >ra.substitutStatus, >MAX(ti.date_created) AS date_created, >ti.on_obs_list, >hms.hms1.hasSwane, >ra.conclusionProduct >FROM hms.produkt_prodgruppe INNER JOIN >hms.hms1 ON hms.produkt_prodgruppe.uproduct_id = hms.hms1.product_id >INNER JOIN >hms.owner ON hms.hms1.owner_id = hms.owner.owner_id INNER JOIN >hms.hms_substance ON hms.hms1.hms_id = hms.hms_substance.hms_id LEFT >OUTER JOIN >hms.riskAssessementGroup rag INNER JOIN >hms.riskAssessement ra ON ra.riskAssessementGroupID = >rag.riskAssessementGroupID AND rag.levid = 1000002 ON >hms.hms_substance.sl_id = rag.levid AND hms.hms1.msdsid = ra.msdsid LEFT >OUTER JOIN >hms.subsGroupHms INNER JOIN >hms.subsHmsGroupHms ON hms.subsGroupHms.groupID = >hms.subsHmsGroupHms.groupID ON >hms.hms_substance.sl_id = hms.subsGroupHms.levid AND hms.hms1.msdsid = >hms.subsHmsGroupHms.msdsid LEFT OUTER JOIN >hms.TIcabinet ti ON hms.hms1.hms_id = ti.hms_id >GROUP BY hms.hms1.hms_id, hms.produkt_prodgruppe.product_name, >hms.hms1.trade_name, >hms.hms1.manufacturer, >hms.hms1.url_hms, >hms.hms1.status, >hms.owner.owner_iscustomer, >hms.subsGroupHms.groupName, hms.hms_substance.company_class, >hms.hms_substance.note, >rag.riskAssessementGroupID, >rag.createdDate, >ra.substitutStatus, >ti.on_obs_list, >rag.showStatus, >hms.hms1.hasSwane, >ra.conclusionProduct, >hms.hms_substance.sl_id >HAVING (rag.riskAssessementGroupID IN >(SELECT MAX(rag.riskAssessementGroupID) AS riskAssessementGroupID >FROM hms.riskAssessementGroup rag INNER JOIN >hms.riskAssessement ra ON ra.riskAssessementGroupID = >rag.riskAssessementGroupID RIGHT OUTER JOIN >hms.hms1 hh ON ra.msdsid = hh.msdsid >GROUP BY hh.trade_name, hh.manufacturer, rag.levid >HAVING EXISTS >(SELECT DISTINCT (MAX(r.createdDate)) >FROM v_getRiskAssessementGID r >GROUP BY r.msdsid, r.levid))) OR >(rag.riskAssessementGroupID IS NULL) AND (hms.hms_substance.sl_id = >1000002) >ORDER BY hms.hms1.trade_name > >If the query is run through EM or QA then I get the expected rows with >the ID 1000002. If run as a SP the ID 1000002 is just ignored and I get >the large resultset. > >Hope someone can help. If complete table definitions and data is wanted >this will be posted. > >Best regards >Henning :-) > >*** Sent via Developersdex http://www.developersdex.com *** > > In addition to Steve's comments, I would add that
Show quote "Steve Kass" <sk***@drew.edu> wrote in message news:%23PJnieG0GHA.1256@TK2MSFTNGP04.phx.gbl... >I don't know why it wouldn't work, but I will note that > this part seems to do nothing at all: > > HAVING EXISTS ( > SELECT DISTINCT (MAX(r.createdDate)) > FROM v_getRiskAssessementGID r > GROUP BY r.msdsid, r.levid) > ) > > That expression is always true, regardless of whether there > are any rows in v_getRiskAssessementGID or not. I would > first check that you are running the same query each time > Run the query in QA, then add > > create proc p as -- at the top > > > go -- at the bottom > exec p > > If no lock, compare the query plans when you run it as a proc vs. when you > run it as a query. > > If they are identical, make sure everything in the procedure (and the > view) is qualified with the owner name. Maybe there is a dbo.X and hms.X > for some table and the sp reads a different one. Finally, I would > parenthesise the joins of the form > JOIN X JOIN Y > ON <this> ON <that> > > If still no luck, try to pare it down to something reproducible to post > here. > > Steve Kass > Drew University > www.stevekass.com > > > > > kongsballa wrote: > >>Help, strange things happening! >> >>How come I have an sql query that I can run in both SQL Server 2000 >>Enterprise Manager and Query Analyzer, but when I try to run it as a >>stored procedure, I get a different result. Instead of the expected 505 >>rows, I get 9425 rows. >> >>I haven't bothered to describe the tables yet. Lot of work to get that >>out. Just want to know if anyone would know why this could happen!?!? >>Any NULL problems I am not aware of? Tried to turn on and off ANSI >>NULLS, but no help. >> >>Here is the message I get when I run the code in QA and Executes the >>same code through a store proc: >> >>(505 row(s) affected) >> >>Warning: Null value is eliminated by an aggregate or other SET >>operation. >> >> >>(9425 row(s) affected) >> >>Warning: Null value is eliminated by an aggregate or other SET >>operation. >> >>By the way, here is the query, just for the hell of it: >> >>SELECT DISTINCT hms.hms_substance.sl_id, hms.hms1.hms_id, >>hms.produkt_prodgruppe.product_name, hms.hms1.trade_name, >>hms.hms1.manufacturer, hms.hms1.url_hms, hms.hms1.status, >>hms.owner.owner_iscustomer, hms.subsGroupHms.groupName, >>hms.hms_substance.company_class, hms.hms_substance.note, >>rag.riskAssessementGroupID, rag.createdDate, ra.substitutStatus, >>MAX(ti.date_created) AS date_created, ti.on_obs_list, hms.hms1.hasSwane, >>ra.conclusionProduct >>FROM hms.produkt_prodgruppe INNER JOIN >>hms.hms1 ON hms.produkt_prodgruppe.uproduct_id = hms.hms1.product_id >>INNER JOIN >>hms.owner ON hms.hms1.owner_id = hms.owner.owner_id INNER JOIN >>hms.hms_substance ON hms.hms1.hms_id = hms.hms_substance.hms_id LEFT >>OUTER JOIN >>hms.riskAssessementGroup rag INNER JOIN >>hms.riskAssessement ra ON ra.riskAssessementGroupID = >>rag.riskAssessementGroupID AND rag.levid = 1000002 ON >>hms.hms_substance.sl_id = rag.levid AND hms.hms1.msdsid = ra.msdsid LEFT >>OUTER JOIN >>hms.subsGroupHms INNER JOIN >>hms.subsHmsGroupHms ON hms.subsGroupHms.groupID = >>hms.subsHmsGroupHms.groupID ON hms.hms_substance.sl_id = >>hms.subsGroupHms.levid AND hms.hms1.msdsid = >>hms.subsHmsGroupHms.msdsid LEFT OUTER JOIN >>hms.TIcabinet ti ON hms.hms1.hms_id = ti.hms_id >>GROUP BY hms.hms1.hms_id, hms.produkt_prodgruppe.product_name, >>hms.hms1.trade_name, hms.hms1.manufacturer, hms.hms1.url_hms, >>hms.hms1.status, hms.owner.owner_iscustomer, hms.subsGroupHms.groupName, >>hms.hms_substance.company_class, hms.hms_substance.note, >>rag.riskAssessementGroupID, rag.createdDate, ra.substitutStatus, >>ti.on_obs_list, rag.showStatus, hms.hms1.hasSwane, ra.conclusionProduct, >>hms.hms_substance.sl_id >>HAVING (rag.riskAssessementGroupID IN >>(SELECT MAX(rag.riskAssessementGroupID) AS riskAssessementGroupID >>FROM hms.riskAssessementGroup rag INNER JOIN >>hms.riskAssessement ra ON ra.riskAssessementGroupID = >>rag.riskAssessementGroupID RIGHT OUTER JOIN >>hms.hms1 hh ON ra.msdsid = hh.msdsid >>GROUP BY hh.trade_name, hh.manufacturer, rag.levid >>HAVING EXISTS >>(SELECT DISTINCT (MAX(r.createdDate)) >>FROM v_getRiskAssessementGID r >>GROUP BY r.msdsid, r.levid))) OR >>(rag.riskAssessementGroupID IS NULL) AND (hms.hms_substance.sl_id = >>1000002) >>ORDER BY hms.hms1.trade_name >> >>If the query is run through EM or QA then I get the expected rows with >>the ID 1000002. If run as a SP the ID 1000002 is just ignored and I get >>the large resultset. >> >>Hope someone can help. If complete table definitions and data is wanted >>this will be posted. >> >>Best regards >>Henning :-) >> >>*** Sent via Developersdex http://www.developersdex.com *** >> Sorry, hit send by mistake, didn't mean to send blank message.
In addition to Steve's comments, I would add that I would use parenthesis when I am mixing AND's and OR's like where you have at the end of your query HAVING (rag.riskAssessementGroupID IN (SELECT MAX(rag.riskAssessementGroupID) AS riskAssessementGroupID FROM hms.riskAssessementGroup rag INNER JOIN hms.riskAssessement ra ON ra.riskAssessementGroupID = rag.riskAssessementGroupID RIGHT OUTER JOIN hms.hms1 hh ON ra.msdsid = hh.msdsid GROUP BY hh.trade_name, hh.manufacturer, rag.levid HAVING EXISTS (SELECT DISTINCT (MAX(r.createdDate)) FROM v_getRiskAssessementGID r GROUP BY r.msdsid, r.levid))) OR (rag.riskAssessementGroupID IS NULL) AND (hms.hms_substance.sl_id = 1000002) Use parenthesis to make clear to the reader which is done first. Oc course, without parenthesis, the AND is done and then the result is ORed with the HAVING statement. Tom Show quote "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message news:x5adncph18knAmHZnZ2dnUVZ_oCdnZ2d@comcast.com... > In addition to Steve's comments, I would add that > "Steve Kass" <sk***@drew.edu> wrote in message > news:%23PJnieG0GHA.1256@TK2MSFTNGP04.phx.gbl... >>I don't know why it wouldn't work, but I will note that >> this part seems to do nothing at all: >> >> HAVING EXISTS ( >> SELECT DISTINCT (MAX(r.createdDate)) >> FROM v_getRiskAssessementGID r >> GROUP BY r.msdsid, r.levid) >> ) >> >> That expression is always true, regardless of whether there >> are any rows in v_getRiskAssessementGID or not. I would >> first check that you are running the same query each time >> Run the query in QA, then add >> >> create proc p as -- at the top >> >> >> go -- at the bottom >> exec p >> >> If no lock, compare the query plans when you run it as a proc vs. when >> you run it as a query. >> >> If they are identical, make sure everything in the procedure (and the >> view) is qualified with the owner name. Maybe there is a dbo.X and hms.X >> for some table and the sp reads a different one. Finally, I would >> parenthesise the joins of the form >> JOIN X JOIN Y >> ON <this> ON <that> >> >> If still no luck, try to pare it down to something reproducible to post >> here. >> >> Steve Kass >> Drew University >> www.stevekass.com >> >> >> >> >> kongsballa wrote: >> >>>Help, strange things happening! >>> >>>How come I have an sql query that I can run in both SQL Server 2000 >>>Enterprise Manager and Query Analyzer, but when I try to run it as a >>>stored procedure, I get a different result. Instead of the expected 505 >>>rows, I get 9425 rows. >>> >>>I haven't bothered to describe the tables yet. Lot of work to get that >>>out. Just want to know if anyone would know why this could happen!?!? >>>Any NULL problems I am not aware of? Tried to turn on and off ANSI >>>NULLS, but no help. >>> >>>Here is the message I get when I run the code in QA and Executes the >>>same code through a store proc: >>> >>>(505 row(s) affected) >>> >>>Warning: Null value is eliminated by an aggregate or other SET >>>operation. >>> >>> >>>(9425 row(s) affected) >>> >>>Warning: Null value is eliminated by an aggregate or other SET >>>operation. >>> >>>By the way, here is the query, just for the hell of it: >>> >>>SELECT DISTINCT hms.hms_substance.sl_id, hms.hms1.hms_id, >>>hms.produkt_prodgruppe.product_name, hms.hms1.trade_name, >>>hms.hms1.manufacturer, hms.hms1.url_hms, hms.hms1.status, >>>hms.owner.owner_iscustomer, hms.subsGroupHms.groupName, >>>hms.hms_substance.company_class, hms.hms_substance.note, >>>rag.riskAssessementGroupID, rag.createdDate, ra.substitutStatus, >>>MAX(ti.date_created) AS date_created, ti.on_obs_list, hms.hms1.hasSwane, >>>ra.conclusionProduct >>>FROM hms.produkt_prodgruppe INNER JOIN >>>hms.hms1 ON hms.produkt_prodgruppe.uproduct_id = hms.hms1.product_id >>>INNER JOIN >>>hms.owner ON hms.hms1.owner_id = hms.owner.owner_id INNER JOIN >>>hms.hms_substance ON hms.hms1.hms_id = hms.hms_substance.hms_id LEFT >>>OUTER JOIN >>>hms.riskAssessementGroup rag INNER JOIN >>>hms.riskAssessement ra ON ra.riskAssessementGroupID = >>>rag.riskAssessementGroupID AND rag.levid = 1000002 ON >>>hms.hms_substance.sl_id = rag.levid AND hms.hms1.msdsid = ra.msdsid LEFT >>>OUTER JOIN >>>hms.subsGroupHms INNER JOIN >>>hms.subsHmsGroupHms ON hms.subsGroupHms.groupID = >>>hms.subsHmsGroupHms.groupID ON hms.hms_substance.sl_id = >>>hms.subsGroupHms.levid AND hms.hms1.msdsid = >>>hms.subsHmsGroupHms.msdsid LEFT OUTER JOIN >>>hms.TIcabinet ti ON hms.hms1.hms_id = ti.hms_id >>>GROUP BY hms.hms1.hms_id, hms.produkt_prodgruppe.product_name, >>>hms.hms1.trade_name, hms.hms1.manufacturer, hms.hms1.url_hms, >>>hms.hms1.status, hms.owner.owner_iscustomer, hms.subsGroupHms.groupName, >>>hms.hms_substance.company_class, hms.hms_substance.note, >>>rag.riskAssessementGroupID, rag.createdDate, ra.substitutStatus, >>>ti.on_obs_list, rag.showStatus, hms.hms1.hasSwane, ra.conclusionProduct, >>>hms.hms_substance.sl_id >>>HAVING (rag.riskAssessementGroupID IN >>>(SELECT MAX(rag.riskAssessementGroupID) AS riskAssessementGroupID >>>FROM hms.riskAssessementGroup rag INNER JOIN >>>hms.riskAssessement ra ON ra.riskAssessementGroupID = >>>rag.riskAssessementGroupID RIGHT OUTER JOIN >>>hms.hms1 hh ON ra.msdsid = hh.msdsid >>>GROUP BY hh.trade_name, hh.manufacturer, rag.levid >>>HAVING EXISTS >>>(SELECT DISTINCT (MAX(r.createdDate)) >>>FROM v_getRiskAssessementGID r >>>GROUP BY r.msdsid, r.levid))) OR >>>(rag.riskAssessementGroupID IS NULL) AND (hms.hms_substance.sl_id = >>>1000002) >>>ORDER BY hms.hms1.trade_name >>> >>>If the query is run through EM or QA then I get the expected rows with >>>the ID 1000002. If run as a SP the ID 1000002 is just ignored and I get >>>the large resultset. >>> >>>Hope someone can help. If complete table definitions and data is wanted >>>this will be posted. >>> >>>Best regards >>>Henning :-) >>> >>>*** Sent via Developersdex http://www.developersdex.com *** >>> > > I will start by thanking you all so much for taking the time to help
me!! Steve, I tried to create the procedure in QA as you said. IT WORKED!!! I am of course a little bit puzzled over that fact. Erland, I tried to create the procedure using EM and turning ANSI_NULLS on and off, but no luck. I believe the bug you mentioned might cause this. I am running SP3a. I will perform the upgrade ofcourse. Tom, I will improve my use of paranthesis. Thanx for now! H :-) *** Sent via Developersdex http://www.developersdex.com *** kongsballa (kongsgba***@devdex.com) writes:
> Steve, I tried to create the procedure in QA as you said. IT WORKED!!! I Moral: stay away from Enterprise Manager.> am of course a little bit puzzled over that fact. Erland, I tried to > create the procedure using EM and turning ANSI_NULLS on and off, but no > luck. I believe the bug you mentioned might cause this. I am running > SP3a. I will perform the upgrade ofcourse. Tom, I will improve my use of > paranthesis. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx kongsballa (kongsgba***@devdex.com) writes:
> How come I have an sql query that I can run in both SQL Server 2000 It could be a problem related to the ANSI_NULLS setting yes. When> Enterprise Manager and Query Analyzer, but when I try to run it as a > stored procedure, I get a different result. Instead of the expected 505 > rows, I get 9425 rows. > > I haven't bothered to describe the tables yet. Lot of work to get that > out. Just want to know if anyone would know why this could happen!?!? > Any NULL problems I am not aware of? Tried to turn on and off ANSI > NULLS, but no help. you said that you turned the setting of and on, did you this both for running the query directly in QA, or only running for the stored procedure? If you tried it only when running the procedure, try it with the query as well. The run-time setting for ANSI_NULLS has no importance when you run a stored procedure, as this setting is saved with the procedure. Thus, you could also try recreating the procedure and make sure that ANSI_NULLS is on. (That is, do this from Query Analyzer. EM has ANSI_NULLS off by default.) There is one more possibility. I seem to recall that there is a bug in SQL 2000 that can cause an outer join to return too many rows in some cases. Unfortunately, I don't remember very much of the details, so I cannot say if that bug may be applicable here. But I believe the bug was fixed in SQL 2000 SP4. (But not SP3.) -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics