"Steve'o" wrote:

> Server  = SQL Server 2000 SP3a
> Client = Access 2000 SP3
>
> My sproc works fine if I run it piece by piece, the sproc fails when called
> by iteself.
>
> It stops at a particular point with:
> [Warning: Null value is eliminated by an aggregate or other SET operation]
>
> The sproc layout is simply:
>
> 1. Select stuff into #temp in SQL Server from linked Oracle server
>
> 2. Insert any lines into live that exist in #temp but not yet in live
>
> 3. Update live, set number columns = 0 where not exist in #temp
>
> 4. Update live, set columns = to columns from #temp where they match on id.
>
> The message
> [Warning: Null value is eliminated by an aggregate or other SET operation]
> occurs as the result of running stage 3 wihtin the sproc.  So the reason the
> sproc works in stages is because I ignore the warning, but obviously the
> sproc is bailing out at stage 3 when run as exec sp_My_Sproc
>
> At the top of the sproc I have
> set nocount on
> set ansi_nulls on
> set ansi_warnings on
>
> I tried setting set ansi_warnings off instead, but got an error
> [Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to
> be set for the connection]
> I presume that is something to do with stage 1 being the import from Oracle.
>
> The only thing I can think to do, is to swap stage 3 and 4.  But this is a
> work around, rather than a knowledgeable fix.
>
> Can anyone shine some light on what might be going on, no worries if not.
>
> Thanks.
> Steve'o

"Ray" wrote:

> Steve, Can you post some actual code, and perhaps what is happening on
> failure because the message you show is just a warning. I see this quite a
> bit on crosstab queries, and should not be causing your procedure to fail.
> But maybe not because I don't know much about how your connecting to the
> oracle db. Please post examples and code so We do not have to guess what the
> issue is.
>
> "Steve'o" wrote:
>
> > Server  = SQL Server 2000 SP3a
> > Client = Access 2000 SP3
> >
> > My sproc works fine if I run it piece by piece, the sproc fails when called
> > by iteself.
> >
> > It stops at a particular point with:
> > [Warning: Null value is eliminated by an aggregate or other SET operation]
> >
> > The sproc layout is simply:
> >
> > 1. Select stuff into #temp in SQL Server from linked Oracle server
> >
> > 2. Insert any lines into live that exist in #temp but not yet in live
> >
> > 3. Update live, set number columns = 0 where not exist in #temp
> >
> > 4. Update live, set columns = to columns from #temp where they match on id.
> >
> > The message
> > [Warning: Null value is eliminated by an aggregate or other SET operation]
> > occurs as the result of running stage 3 wihtin the sproc.  So the reason the
> > sproc works in stages is because I ignore the warning, but obviously the
> > sproc is bailing out at stage 3 when run as exec sp_My_Sproc
> >
> > At the top of the sproc I have
> > set nocount on
> > set ansi_nulls on
> > set ansi_warnings on
> >
> > I tried setting set ansi_warnings off instead, but got an error
> > [Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to
> > be set for the connection]
> > I presume that is something to do with stage 1 being the import from Oracle.
> >
> > The only thing I can think to do, is to swap stage 3 and 4.  But this is a
> > work around, rather than a knowledgeable fix.
> >
> > Can anyone shine some light on what might be going on, no worries if not.
> >
> > Thanks.
> > Steve'o

"Steve'o" wrote:

> Thanks Ray, I didn't think anyone would want to read through loads of code, I
> presumed it would have been rude of me to simply dump loads down and say
> "hey, whats wrong with that?"
>
> So here goes...  :)
>
> ALTER    procedure sp_Update_Ledger_Actuals_CY
> as
>
> set nocount on
> set ansi_nulls on
> set ansi_warnings on
>
> declare
> @YEAR1 char(4),
> @PERIOD1 char(2),
> @VERSION1 varchar(15)
>
> set @YEAR1 = (select vc_year from tbl_Versions_Current)
> set @VERSION1 = (select vc_import_version from tbl_Versions_Current)
> set @PERIOD1 = (select vc_import_period from tbl_Versions_Current)
>
> -- CHECK THE CONNECTION IS VALID, IF IT IS THEN UPDATE VALUES FROM THE GL
> if exists(select * from openquery(oralive,'select * from
> oralive.account_balances'))
>
> BEGIN
>
> -- UPDATE THE CURRENT YEAR ACTUALS, UPTO AND INCLUDING THE PERIOD SET
> -- BY THE CURRENT IMPORT PERIOD
> select
> ab_year,
> ab_version = @VERSION1,
> ab_cost_centre,
> ab_account,
> ab_actual = round(sum((cast(ab_actual as money))),0)
> into #tmpImportActuals
> from openquery(oralive,'select
>     ab_year,
>     ab_cost_centre,
>     ab_period,
>     ab_account,
>     (nvl(ab_Amount,0)) as ab_Actual
>     from oralive.account_balances') a
> inner join tbl_Cost_Centres_Hierarchies b on
> a.ab_cost_centre = ch_cost_centre
> left outer join tbl_Cost_Centres_Excluded c on
> a.ab_Cost_Centre = c.ce_Cost_Centre
> where
> ab_year = (@YEAR1) and
> ab_period <= (@PERIOD1) and
> ce_Cost_Centre is null
> group by ab_year, ab_cost_centre, ab_account
> order by ab_year, ab_cost_centre, ab_account
>
> -- Make sure there are Cost Centre and Account combinations
> insert into tbl_estimates (
> est_year,
> est_version,
> est_cost_centre,
> est_account,
> est_allow_edits,
> est_actual_cy,
> est_next_year_base_inflate,
> est_zero_estimate)
> select distinct
> ab_year,
> ab_version,
> ab_cost_centre,
> ab_account,
> ac_allow_edits,
> ab_actual,
> ac_inflate,
> ac_zero
> from #tmpimportactuals a left outer join tbl_estimates b on
> a.ab_year = b.est_year and
> a.ab_version = b.est_version and
> a.ab_cost_centre = b.est_cost_centre and
> a.ab_account = b.est_account
> inner join tbl_accounts c on
> a.ab_account = c.ac_account
> where
> b.est_year is null and
> b.est_version is null and
> b.est_cost_centre is null and
> b.est_account is null
>
> -- Update values to zero if not in list
> update tbl_estimates
> set
> est_actual_cy = 0
> from tbl_estimates a left outer join #tmpimportactuals b on
> a.est_year = b.ab_year and
> a.est_version = b.ab_version and
> a.est_cost_centre = b.ab_cost_centre and
> a.est_account = b.ab_account
> where
> b.ab_year is null and
> b.ab_cost_centre is null and
> b.ab_account is null
>
> -- Update the actuals
> update tbl_estimates
> set
> est_actual_cy = b.ab_actual
> from tbl_estimates a inner join #tmpimportactuals b on
> a.est_year = b.ab_year and
> a.est_version = b.ab_version and
> a.est_cost_centre = b.ab_cost_centre and
> a.est_account = b.ab_account
>
> END
>
>
>
> "Ray" wrote:
>
> > Steve, Can you post some actual code, and perhaps what is happening on
> > failure because the message you show is just a warning. I see this quite a
> > bit on crosstab queries, and should not be causing your procedure to fail.
> > But maybe not because I don't know much about how your connecting to the
> > oracle db. Please post examples and code so We do not have to guess what the
> > issue is.
> >
> > "Steve'o" wrote:
> >
> > > Server  = SQL Server 2000 SP3a
> > > Client = Access 2000 SP3
> > >
> > > My sproc works fine if I run it piece by piece, the sproc fails when called
> > > by iteself.
> > >
> > > It stops at a particular point with:
> > > [Warning: Null value is eliminated by an aggregate or other SET operation]
> > >
> > > The sproc layout is simply:
> > >
> > > 1. Select stuff into #temp in SQL Server from linked Oracle server
> > >
> > > 2. Insert any lines into live that exist in #temp but not yet in live
> > >
> > > 3. Update live, set number columns = 0 where not exist in #temp
> > >
> > > 4. Update live, set columns = to columns from #temp where they match on id.
> > >
> > > The message
> > > [Warning: Null value is eliminated by an aggregate or other SET operation]
> > > occurs as the result of running stage 3 wihtin the sproc.  So the reason the
> > > sproc works in stages is because I ignore the warning, but obviously the
> > > sproc is bailing out at stage 3 when run as exec sp_My_Sproc
> > >
> > > At the top of the sproc I have
> > > set nocount on
> > > set ansi_nulls on
> > > set ansi_warnings on
> > >
> > > I tried setting set ansi_warnings off instead, but got an error
> > > [Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to
> > > be set for the connection]
> > > I presume that is something to do with stage 1 being the import from Oracle.
> > >
> > > The only thing I can think to do, is to swap stage 3 and 4.  But this is a
> > > work around, rather than a knowledgeable fix.
> > >
> > > Can anyone shine some light on what might be going on, no worries if not.
> > >
> > > Thanks.
> > > Steve'o

"Ray" wrote:

> Code looks okay, I cannot test it though. Need Table Structures, and sample
> data.
> When you execute the stored procedure what is the actual error being returned.
>
> Because the Aggregate null warning is happening here.
> select
> ab_year,
> ab_version = @VERSION1,
> ab_cost_centre,
> ab_account,
> ab_actual = round(sum((cast(ab_actual as money))),0)
> into #tmpImportActuals
> from openquery(oralive,'select
>     ab_year,
>     ab_cost_centre,
>     ab_period,
>     ab_account,
>     (nvl(ab_Amount,0)) as ab_Actual
>     from oralive.account_balances') a
> inner join tbl_Cost_Centres_Hierarchies b on
> a.ab_cost_centre = ch_cost_centre
> left outer join tbl_Cost_Centres_Excluded c on
> a.ab_Cost_Centre = c.ce_Cost_Centre
> where
> ab_year = (@YEAR1) and
> ab_period <= (@PERIOD1) and
> ce_Cost_Centre is null
> group by ab_year, ab_cost_centre, ab_account
> order by ab_year, ab_cost_centre, ab_account
>
>  and I don't think this would cause your procedure to fail.
>
> "Steve'o" wrote:
>
> > Thanks Ray, I didn't think anyone would want to read through loads of code, I
> > presumed it would have been rude of me to simply dump loads down and say
> > "hey, whats wrong with that?"
> >
> > So here goes...  :)
> >
> > ALTER    procedure sp_Update_Ledger_Actuals_CY
> > as
> >
> > set nocount on
> > set ansi_nulls on
> > set ansi_warnings on
> >
> > declare
> > @YEAR1 char(4),
> > @PERIOD1 char(2),
> > @VERSION1 varchar(15)
> >
> > set @YEAR1 = (select vc_year from tbl_Versions_Current)
> > set @VERSION1 = (select vc_import_version from tbl_Versions_Current)
> > set @PERIOD1 = (select vc_import_period from tbl_Versions_Current)
> >
> > -- CHECK THE CONNECTION IS VALID, IF IT IS THEN UPDATE VALUES FROM THE GL
> > if exists(select * from openquery(oralive,'select * from
> > oralive.account_balances'))
> >
> > BEGIN
> >
> > -- UPDATE THE CURRENT YEAR ACTUALS, UPTO AND INCLUDING THE PERIOD SET
> > -- BY THE CURRENT IMPORT PERIOD
> > select
> > ab_year,
> > ab_version = @VERSION1,
> > ab_cost_centre,
> > ab_account,
> > ab_actual = round(sum((cast(ab_actual as money))),0)
> > into #tmpImportActuals
> > from openquery(oralive,'select
> >     ab_year,
> >     ab_cost_centre,
> >     ab_period,
> >     ab_account,
> >     (nvl(ab_Amount,0)) as ab_Actual
> >     from oralive.account_balances') a
> > inner join tbl_Cost_Centres_Hierarchies b on
> > a.ab_cost_centre = ch_cost_centre
> > left outer join tbl_Cost_Centres_Excluded c on
> > a.ab_Cost_Centre = c.ce_Cost_Centre
> > where
> > ab_year = (@YEAR1) and
> > ab_period <= (@PERIOD1) and
> > ce_Cost_Centre is null
> > group by ab_year, ab_cost_centre, ab_account
> > order by ab_year, ab_cost_centre, ab_account
> >
> > -- Make sure there are Cost Centre and Account combinations
> > insert into tbl_estimates (
> > est_year,
> > est_version,
> > est_cost_centre,
> > est_account,
> > est_allow_edits,
> > est_actual_cy,
> > est_next_year_base_inflate,
> > est_zero_estimate)
> > select distinct
> > ab_year,
> > ab_version,
> > ab_cost_centre,
> > ab_account,
> > ac_allow_edits,
> > ab_actual,
> > ac_inflate,
> > ac_zero
> > from #tmpimportactuals a left outer join tbl_estimates b on
> > a.ab_year = b.est_year and
> > a.ab_version = b.est_version and
> > a.ab_cost_centre = b.est_cost_centre and
> > a.ab_account = b.est_account
> > inner join tbl_accounts c on
> > a.ab_account = c.ac_account
> > where
> > b.est_year is null and
> > b.est_version is null and
> > b.est_cost_centre is null and
> > b.est_account is null
> >
> > -- Update values to zero if not in list
> > update tbl_estimates
> > set
> > est_actual_cy = 0
> > from tbl_estimates a left outer join #tmpimportactuals b on
> > a.est_year = b.ab_year and
> > a.est_version = b.ab_version and
> > a.est_cost_centre = b.ab_cost_centre and
> > a.est_account = b.ab_account
> > where
> > b.ab_year is null and
> > b.ab_cost_centre is null and
> > b.ab_account is null
> >
> > -- Update the actuals
> > update tbl_estimates
> > set
> > est_actual_cy = b.ab_actual
> > from tbl_estimates a inner join #tmpimportactuals b on
> > a.est_year = b.ab_year and
> > a.est_version = b.ab_version and
> > a.est_cost_centre = b.ab_cost_centre and
> > a.est_account = b.ab_account
> >
> > END
> >
> >
> >
> > "Ray" wrote:
> >
> > > Steve, Can you post some actual code, and perhaps what is happening on
> > > failure because the message you show is just a warning. I see this quite a
> > > bit on crosstab queries, and should not be causing your procedure to fail.
> > > But maybe not because I don't know much about how your connecting to the
> > > oracle db. Please post examples and code so We do not have to guess what the
> > > issue is.
> > >
> > > "Steve'o" wrote:
> > >
> > > > Server  = SQL Server 2000 SP3a
> > > > Client = Access 2000 SP3
> > > >
> > > > My sproc works fine if I run it piece by piece, the sproc fails when called
> > > > by iteself.
> > > >
> > > > It stops at a particular point with:
> > > > [Warning: Null value is eliminated by an aggregate or other SET operation]
> > > >
> > > > The sproc layout is simply:
> > > >
> > > > 1. Select stuff into #temp in SQL Server from linked Oracle server
> > > >
> > > > 2. Insert any lines into live that exist in #temp but not yet in live
> > > >
> > > > 3. Update live, set number columns = 0 where not exist in #temp
> > > >
> > > > 4. Update live, set columns = to columns from #temp where they match on id.
> > > >
> > > > The message
> > > > [Warning: Null value is eliminated by an aggregate or other SET operation]
> > > > occurs as the result of running stage 3 wihtin the sproc.  So the reason the
> > > > sproc works in stages is because I ignore the warning, but obviously the
> > > > sproc is bailing out at stage 3 when run as exec sp_My_Sproc
> > > >
> > > > At the top of the sproc I have
> > > > set nocount on
> > > > set ansi_nulls on
> > > > set ansi_warnings on
> > > >
> > > > I tried setting set ansi_warnings off instead, but got an error
> > > > [Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to
> > > > be set for the connection]
> > > > I presume that is something to do with stage 1 being the import from Oracle.
> > > >
> > > > The only thing I can think to do, is to swap stage 3 and 4.  But this is a
> > > > work around, rather than a knowledgeable fix.
> > > >
> > > > Can anyone shine some light on what might be going on, no worries if not.
> > > >
> > > > Thanks.
> > > > Steve'o

"Steve'o" wrote:

> Hi Ray, the only output I get in QA is
>  [Warning: Null value is eliminated by an aggregate or other SET operation]
> That's it.
>
> It occurs at stage 3 not 1, so I've swapped 3 with 4 as they opperate on
> different slices of the data it does not matter which order they occur.
>
> Another reason I did not include the code in the inital post was because I
> was wondering if it was a "theory" problem.  In that, I've got worked round
> it so its not a major issue and don't want anyone spending loads of time
> looking at it, including me as deadlines are approaching and I've got to
> crack on.  Hence my original airy fairy post where I was fishing for a "oh
> yeah, I've seen that loads before where its nearly always solved by setting x
> off, or y on"
>
> Don't spose I can be really cheeky and ask how you deal with divide by zero
> errors?
> Totally OT I know, but it can't hurt to ask :)
>
> I have an update set statement which sais
> update table
> set
> column_a = (column_b / column_c *100)
>
>
>
>
> "Ray" wrote:
>
> > Code looks okay, I cannot test it though. Need Table Structures, and sample
> > data.
> > When you execute the stored procedure what is the actual error being returned.
> >
> > Because the Aggregate null warning is happening here.
> > select
> > ab_year,
> > ab_version = @VERSION1,
> > ab_cost_centre,
> > ab_account,
> > ab_actual = round(sum((cast(ab_actual as money))),0)
> > into #tmpImportActuals
> > from openquery(oralive,'select
> >     ab_year,
> >     ab_cost_centre,
> >     ab_period,
> >     ab_account,
> >     (nvl(ab_Amount,0)) as ab_Actual
> >     from oralive.account_balances') a
> > inner join tbl_Cost_Centres_Hierarchies b on
> > a.ab_cost_centre = ch_cost_centre
> > left outer join tbl_Cost_Centres_Excluded c on
> > a.ab_Cost_Centre = c.ce_Cost_Centre
> > where
> > ab_year = (@YEAR1) and
> > ab_period <= (@PERIOD1) and
> > ce_Cost_Centre is null
> > group by ab_year, ab_cost_centre, ab_account
> > order by ab_year, ab_cost_centre, ab_account
> >
> >  and I don't think this would cause your procedure to fail.
> >
> > "Steve'o" wrote:
> >
> > > Thanks Ray, I didn't think anyone would want to read through loads of code, I
> > > presumed it would have been rude of me to simply dump loads down and say
> > > "hey, whats wrong with that?"
> > >
> > > So here goes...  :)
> > >
> > > ALTER    procedure sp_Update_Ledger_Actuals_CY
> > > as
> > >
> > > set nocount on
> > > set ansi_nulls on
> > > set ansi_warnings on
> > >
> > > declare
> > > @YEAR1 char(4),
> > > @PERIOD1 char(2),
> > > @VERSION1 varchar(15)
> > >
> > > set @YEAR1 = (select vc_year from tbl_Versions_Current)
> > > set @VERSION1 = (select vc_import_version from tbl_Versions_Current)
> > > set @PERIOD1 = (select vc_import_period from tbl_Versions_Current)
> > >
> > > -- CHECK THE CONNECTION IS VALID, IF IT IS THEN UPDATE VALUES FROM THE GL
> > > if exists(select * from openquery(oralive,'select * from
> > > oralive.account_balances'))
> > >
> > > BEGIN
> > >
> > > -- UPDATE THE CURRENT YEAR ACTUALS, UPTO AND INCLUDING THE PERIOD SET
> > > -- BY THE CURRENT IMPORT PERIOD
> > > select
> > > ab_year,
> > > ab_version = @VERSION1,
> > > ab_cost_centre,
> > > ab_account,
> > > ab_actual = round(sum((cast(ab_actual as money))),0)
> > > into #tmpImportActuals
> > > from openquery(oralive,'select
> > >     ab_year,
> > >     ab_cost_centre,
> > >     ab_period,
> > >     ab_account,
> > >     (nvl(ab_Amount,0)) as ab_Actual
> > >     from oralive.account_balances') a
> > > inner join tbl_Cost_Centres_Hierarchies b on
> > > a.ab_cost_centre = ch_cost_centre
> > > left outer join tbl_Cost_Centres_Excluded c on
> > > a.ab_Cost_Centre = c.ce_Cost_Centre
> > > where
> > > ab_year = (@YEAR1) and
> > > ab_period <= (@PERIOD1) and
> > > ce_Cost_Centre is null
> > > group by ab_year, ab_cost_centre, ab_account
> > > order by ab_year, ab_cost_centre, ab_account
> > >
> > > -- Make sure there are Cost Centre and Account combinations
> > > insert into tbl_estimates (
> > > est_year,
> > > est_version,
> > > est_cost_centre,
> > > est_account,
> > > est_allow_edits,
> > > est_actual_cy,
> > > est_next_year_base_inflate,
> > > est_zero_estimate)
> > > select distinct
> > > ab_year,
> > > ab_version,
> > > ab_cost_centre,
> > > ab_account,
> > > ac_allow_edits,
> > > ab_actual,
> > > ac_inflate,
> > > ac_zero
> > > from #tmpimportactuals a left outer join tbl_estimates b on
> > > a.ab_year = b.est_year and
> > > a.ab_version = b.est_version and
> > > a.ab_cost_centre = b.est_cost_centre and
> > > a.ab_account = b.est_account
> > > inner join tbl_accounts c on
> > > a.ab_account = c.ac_account
> > > where
> > > b.est_year is null and
> > > b.est_version is null and
> > > b.est_cost_centre is null and
> > > b.est_account is null
> > >
> > > -- Update values to zero if not in list
> > > update tbl_estimates
> > > set
> > > est_actual_cy = 0
> > > from tbl_estimates a left outer join #tmpimportactuals b on
> > > a.est_year = b.ab_year and
> > > a.est_version = b.ab_version and
> > > a.est_cost_centre = b.ab_cost_centre and
> > > a.est_account = b.ab_account
> > > where
> > > b.ab_year is null and
> > > b.ab_cost_centre is null and
> > > b.ab_account is null
> > >
> > > -- Update the actuals
> > > update tbl_estimates
> > > set
> > > est_actual_cy = b.ab_actual
> > > from tbl_estimates a inner join #tmpimportactuals b on
> > > a.est_year = b.ab_year and
> > > a.est_version = b.ab_version and
> > > a.est_cost_centre = b.ab_cost_centre and
> > > a.est_account = b.ab_account
> > >
> > > END
> > >
> > >
> > >
> > > "Ray" wrote:
> > >
> > > > Steve, Can you post some actual code, and perhaps what is happening on
> > > > failure because the message you show is just a warning. I see this quite a
> > > > bit on crosstab queries, and should not be causing your procedure to fail.
> > > > But maybe not because I don't know much about how your connecting to the
> > > > oracle db. Please post examples and code so We do not have to guess what the
> > > > issue is.
> > > >
> > > > "Steve'o" wrote:
> > > >
> > > > > Server  = SQL Server 2000 SP3a
> > > > > Client = Access 2000 SP3
> > > > >
> > > > > My sproc works fine if I run it piece by piece, the sproc fails when called
> > > > > by iteself.
> > > > >
> > > > > It stops at a particular point with:
> > > > > [Warning: Null value is eliminated by an aggregate or other SET operation]
> > > > >
> > > > > The sproc layout is simply:
> > > > >
> > > > > 1. Select stuff into #temp in SQL Server from linked Oracle server
> > > > >
> > > > > 2. Insert any lines into live that exist in #temp but not yet in live
> > > > >
> > > > > 3. Update live, set number columns = 0 where not exist in #temp
> > > > >
> > > > > 4. Update live, set columns = to columns from #temp where they match on id.
> > > > >
> > > > > The message
> > > > > [Warning: Null value is eliminated by an aggregate or other SET operation]
> > > > > occurs as the result of running stage 3 wihtin the sproc.  So the reason the
> > > > > sproc works in stages is because I ignore the warning, but obviously the
> > > > > sproc is bailing out at stage 3 when run as exec sp_My_Sproc
> > > > >
> > > > > At the top of the sproc I have
> > > > > set nocount on
> > > > > set ansi_nulls on
> > > > > set ansi_warnings on
> > > > >
> > > > > I tried setting set ansi_warnings off instead, but got an error
> > > > > [Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to
> > > > > be set for the connection]
> > > > > I presume that is something to do with stage 1 being the import from Oracle.
> > > > >
> > > > > The only thing I can think to do, is to swap stage 3 and 4.  But this is a
> > > > > work around, rather than a knowledgeable fix.
> > > > >
> > > > > Can anyone shine some light on what might be going on, no worries if not.
> > > > >
> > > > > Thanks.
> > > > > Steve'o

"Steve'o" <Ste***@discussions.microsoft.com> wrote in message
news:BA3439CE-DDFA-4B34-A99C-9407768A651E@microsoft.com...
> Server  = SQL Server 2000 SP3a
> Client = Access 2000 SP3
>
> My sproc works fine if I run it piece by piece, the sproc fails when
> called
> by iteself.
>
> It stops at a particular point with:
> [Warning: Null value is eliminated by an aggregate or other SET operation]
>
> The sproc layout is simply:
>
> 1. Select stuff into #temp in SQL Server from linked Oracle server
>
> 2. Insert any lines into live that exist in #temp but not yet in live
>
> 3. Update live, set number columns = 0 where not exist in #temp
>
> 4. Update live, set columns = to columns from #temp where they match on
> id.
>
> The message
> [Warning: Null value is eliminated by an aggregate or other SET operation]
> occurs as the result of running stage 3 wihtin the sproc.  So the reason
> the
> sproc works in stages is because I ignore the warning, but obviously the
> sproc is bailing out at stage 3 when run as exec sp_My_Sproc
>
> At the top of the sproc I have
> set nocount on
> set ansi_nulls on
> set ansi_warnings on
>
> I tried setting set ansi_warnings off instead, but got an error
> [Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to
> be set for the connection]
> I presume that is something to do with stage 1 being the import from
> Oracle.
>
> The only thing I can think to do, is to swap stage 3 and 4.  But this is a
> work around, rather than a knowledgeable fix.
>
> Can anyone shine some light on what might be going on, no worries if not.
>
> Thanks.
> Steve'o

"Keith Kratochvil" wrote:

> You could try turning the warning off.  Here is an example for you to play
> with within Query Analyzer:
>
> create table #foo (col int)
> insert into #foo values (1)
> insert into #foo values (null)
> insert into #foo values (1)
>
>
> PRINT 'normal behavior:'
> go
> select sum(col) from #foo
> go
>
> SET ANSI_WARNINGS OFF
> GO
> PRINT '/*************************************************************/'
> PRINT ''
> PRINT ''
> GO
> PRINT 'behavior with ANSI_WARNINGS OFF:'
> go
> select sum(col) from #foo
> GO
> SET ANSI_WARNINGS ON
> GO
>
>
>
> --
> Keith
>
>
> "Steve'o" <Ste***@discussions.microsoft.com> wrote in message
> news:BA3439CE-DDFA-4B34-A99C-9407768A651E@microsoft.com...
> > Server  = SQL Server 2000 SP3a
> > Client = Access 2000 SP3
> >
> > My sproc works fine if I run it piece by piece, the sproc fails when
> > called
> > by iteself.
> >
> > It stops at a particular point with:
> > [Warning: Null value is eliminated by an aggregate or other SET operation]
> >
> > The sproc layout is simply:
> >
> > 1. Select stuff into #temp in SQL Server from linked Oracle server
> >
> > 2. Insert any lines into live that exist in #temp but not yet in live
> >
> > 3. Update live, set number columns = 0 where not exist in #temp
> >
> > 4. Update live, set columns = to columns from #temp where they match on
> > id.
> >
> > The message
> > [Warning: Null value is eliminated by an aggregate or other SET operation]
> > occurs as the result of running stage 3 wihtin the sproc.  So the reason
> > the
> > sproc works in stages is because I ignore the warning, but obviously the
> > sproc is bailing out at stage 3 when run as exec sp_My_Sproc
> >
> > At the top of the sproc I have
> > set nocount on
> > set ansi_nulls on
> > set ansi_warnings on
> >
> > I tried setting set ansi_warnings off instead, but got an error
> > [Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to
> > be set for the connection]
> > I presume that is something to do with stage 1 being the import from
> > Oracle.
> >
> > The only thing I can think to do, is to swap stage 3 and 4.  But this is a
> > work around, rather than a knowledgeable fix.
> >
> > Can anyone shine some light on what might be going on, no worries if not.
> >
> > Thanks.
> > Steve'o
>
>
>

"Steve'o" <Ste***@discussions.microsoft.com> wrote in message
news:9D65BDB3-6BBD-4DF5-997C-99CBA83CE0C6@microsoft.com...
> Thanks Keith, but I did say that I'd tried that and it wouldn't run at
> all.
>
> "Keith Kratochvil" wrote:
>
>> You could try turning the warning off.  Here is an example for you to
>> play
>> with within Query Analyzer:
>>
>> create table #foo (col int)
>> insert into #foo values (1)
>> insert into #foo values (null)
>> insert into #foo values (1)
>>
>>
>> PRINT 'normal behavior:'
>> go
>> select sum(col) from #foo
>> go
>>
>> SET ANSI_WARNINGS OFF
>> GO
>> PRINT '/*************************************************************/'
>> PRINT ''
>> PRINT ''
>> GO
>> PRINT 'behavior with ANSI_WARNINGS OFF:'
>> go
>> select sum(col) from #foo
>> GO
>> SET ANSI_WARNINGS ON
>> GO
>>
>>
>>
>> --
>> Keith
>>
>>
>> "Steve'o" <Ste***@discussions.microsoft.com> wrote in message
>> news:BA3439CE-DDFA-4B34-A99C-9407768A651E@microsoft.com...
>> > Server  = SQL Server 2000 SP3a
>> > Client = Access 2000 SP3
>> >
>> > My sproc works fine if I run it piece by piece, the sproc fails when
>> > called
>> > by iteself.
>> >
>> > It stops at a particular point with:
>> > [Warning: Null value is eliminated by an aggregate or other SET
>> > operation]
>> >
>> > The sproc layout is simply:
>> >
>> > 1. Select stuff into #temp in SQL Server from linked Oracle server
>> >
>> > 2. Insert any lines into live that exist in #temp but not yet in live
>> >
>> > 3. Update live, set number columns = 0 where not exist in #temp
>> >
>> > 4. Update live, set columns = to columns from #temp where they match on
>> > id.
>> >
>> > The message
>> > [Warning: Null value is eliminated by an aggregate or other SET
>> > operation]
>> > occurs as the result of running stage 3 wihtin the sproc.  So the
>> > reason
>> > the
>> > sproc works in stages is because I ignore the warning, but obviously
>> > the
>> > sproc is bailing out at stage 3 when run as exec sp_My_Sproc
>> >
>> > At the top of the sproc I have
>> > set nocount on
>> > set ansi_nulls on
>> > set ansi_warnings on
>> >
>> > I tried setting set ansi_warnings off instead, but got an error
>> > [Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options
>> > to
>> > be set for the connection]
>> > I presume that is something to do with stage 1 being the import from
>> > Oracle.
>> >
>> > The only thing I can think to do, is to swap stage 3 and 4.  But this
>> > is a
>> > work around, rather than a knowledgeable fix.
>> >
>> > Can anyone shine some light on what might be going on, no worries if
>> > not.
>> >
>> > Thanks.
>> > Steve'o
>>
>>
>>

"--CELKO--" wrote:

> This is ANSI behavior.  When the NULLs are dropped in an aggregate
> function, this comes up.  You can turn off the warnings or catch them
> in the code.
>
> The really cute question is with cursors that have aggregates.  When
> does the warning appear in the DECLARE CURSOR? the OPEN CURSOR? or at
> the first FETCH that gets such a row back?  The answer is that it is
> implementation defined and real products do it differently.
>
>