Query working in EM and QA but not in Store proc

microsoft.public.sqlserver.programming

4 Sep 2006 8:36 PM

kongsballa

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

4 Sep 2006 9:18 PM

Steve Kass

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 quoteHide 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 ***
>
>

Are all your drivers up to date? click for free checkup

4 Sep 2006 9:50 PM

Tom Cooper

In addition to Steve's comments, I would add that
Show quoteHide 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 ***
>>

4 Sep 2006 9:57 PM

Tom Cooper

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 quoteHide 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 ***
>>>
>
>

5 Sep 2006 8:42 AM

kongsballa

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

5 Sep 2006 10:25 PM

Erland Sommarskog

kongsballa (kongsgba***@devdex.com) writes:

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

Moral: stay away from Enterprise Manager.

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

4 Sep 2006 9:53 PM

Erland Sommarskog

kongsballa (kongsgba***@devdex.com) writes:

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

It could be a problem related to the ANSI_NULLS setting yes. When
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.)