Different result from Script and SP

microsoft.public.sqlserver.programming

13 Sep 2006 2:28 PM

Svein Terje Gaup

Hi, I have an SP that gives an incorrect value for one of the columns
in the result set.

I have been debugging, and so I copied the text from the SP, removed
the CREATE Procedure part, and declared/assigned the parameters their
values. When I run the exact same query in query analyzer, I get the
correct values.

So "exec SP @parms" gives the wrong result while running the full query
gives the correct result.

What could be reasons for this happening? I thought it might be because
of caching, but I still get the same results after restarting the
MSSQLSERVER service.

More info:
My Query/SP queries two Views, one as the primary table, and the other
in subqueries.
The field that gives the wrong result is the DGEstimatFerdig field from
the query under.

Query:
SELECT
    T.ProsjektID,
    T.ProsjektNr,
    T.ProsjektPK,
    T.ForetakFK,
    T.LandFK,
    T.ProsjektNavn,
    T.ProsjektLeder,
    T.ProsjektlederNavn,
    T.StatusKode,
    T.KontraktSum,
    T.KalkulertDG,
    T.EstimertInntekt,
    T.EstimertDG,
    T.EstimatStatus,
    (CASE
        WHEN (T.EstimatStatus = 255) THEN T.SumInntekterEstimatFerdig
        ELSE NULL
    END) AS SumInntekterEstimatFerdig,
    (CASE
        WHEN (T.EstimatStatus = 255) THEN T.SumKostnaderEstimatFerdig
        ELSE NULL
    END) AS SumKostnaderEstimatFerdig,
    (CASE
        WHEN (T.SumInntekterEstimatFerdig > 0)
            THEN
(((T.SumInntekterEstimatFerdig-T.SumKostnaderEstimatFerdig)/T.SumInntekterEstimatFerdig)*@factor)
        ELSE 0
    END) AS DGEstimatFerdig,
    EstimatCount
FROM
(
    SELECT
        Dim.ProsjektID,
        Dim.ProsjektPK,
        Dim.ForetakFK,
        Dim.LandFK,
        (RIGHT(Dim.ProsjektNr,LEN(Dim.ProsjektNr)-6)) AS ProsjektNr,
        Dim.ProsjektNavn,
        (RIGHT(Dim.Prosjektleder,LEN(Dim.Prosjektleder)-6)) AS Prosjektleder,
        Dim.ProsjektlederNavn,
        Dim.StatusKode,
        Data.Kontraktsum,
        (Data.KalkulertDG / 100) AS KalkulertDG,
        Data.EstimertInntekt,
        (Data.EstimertDG / 100) AS EstimertDG,
        (SELECT
            SumEstimatFerdig
        FROM vProsjektListe P
        WHERE EstimatLinjeTypeFK='I'
            AND Dim.MaxEstimatID=P.EstimatID) AS SumInntekterEstimatFerdig,
        (SELECT
            SumEstimatFerdig
        FROM vProsjektListe P
        WHERE EstimatLinjeTypeFK='K'
            AND Dim.MaxEstimatID=P.EstimatID) AS SumKostnaderEstimatFerdig,
        (SELECT TOP 1
            Status
        FROM vProsjektListe P
        WHERE
            Dim.MaxEstimatID=P.EstimatID) AS EstimatStatus,
        Dim.EstimatCount
    FROM vProsjekt Dim
        INNER JOIN DW_Data_Prosjekt Data ON Dim.ProsjektNr = Data.ProsjektNr
    WHERE StatusKode IN ('F','D','B','G')
    AND Dim.Prosjektleder = @Prosjektleder
    AND Dim.ProduktGruppe = @pg
) AS T
ORDER BY T.ProsjektlederNavn, T.ProsjektNr;

Hope someone can help...

Sincerely
Svein Terje Gaup

13 Sep 2006 2:45 PM

Alexander Kuznetsov

Svein,

output USEROPTIONS at the start of your SP and compare it to the output
of USEROPTIONS in QA as follows. If you see discrepancies, recompile
your SP under correct settings.

DBCC USEROPTIONS
GO

SET ANSI_NULLS OFF
go
CREATE PROCEDURE aa
AS
DBCC USEROPTIONS
GO
SET ANSI_NULLS ON
go
aa
go
DBCC USEROPTIONS
GO
DROP PROCEDURE aa
go

13 Sep 2006 3:05 PM

Jim Underwood

Also, qualify all of your table with the owner prefix, just to be certain
there is not another table/view by the same name.

Show quote

"Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
news:1158158715.846167.209560@h48g2000cwc.googlegroups.com...
> Svein,
>
> output USEROPTIONS at the start of your SP and compare it to the output
> of USEROPTIONS in QA as follows. If you see discrepancies, recompile
> your SP under correct settings.
>
> DBCC USEROPTIONS
> GO
>
> SET ANSI_NULLS OFF
> go
> CREATE PROCEDURE aa
> AS
> DBCC USEROPTIONS
> GO
> SET ANSI_NULLS ON
> go
> aa
> go
> DBCC USEROPTIONS
> GO
> DROP PROCEDURE aa
> go
>

13 Sep 2006 3:28 PM

Scott

Check your params you're passing. Are you sure they are being passed as you
expect? Try including them in the select coming back for debug.

Show quote

"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uhkABY01GHA.3644@TK2MSFTNGP03.phx.gbl...
> Also, qualify all of your table with the owner prefix, just to be certain
> there is not another table/view by the same name.
>
> "Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
> news:1158158715.846167.209560@h48g2000cwc.googlegroups.com...
>> Svein,
>>
>> output USEROPTIONS at the start of your SP and compare it to the output
>> of USEROPTIONS in QA as follows. If you see discrepancies, recompile
>> your SP under correct settings.
>>
>> DBCC USEROPTIONS
>> GO
>>
>> SET ANSI_NULLS OFF
>> go
>> CREATE PROCEDURE aa
>> AS
>> DBCC USEROPTIONS
>> GO
>> SET ANSI_NULLS ON
>> go
>> aa
>> go
>> DBCC USEROPTIONS
>> GO
>> DROP PROCEDURE aa
>> go
>>
>
>

14 Sep 2006 9:34 AM

Svein Terje Gaup

Hi, I forgot to mention, I'm running on SQL Server 2005.

When running the exact statements you posted, I get this output from
all the DBCC USEROPTIONS:

textsize    2147483647
language    us_english
dateformat    mdy
datefirst    7
lock_timeout    -1
quoted_identifier    SET
arithabort    SET
ansi_null_dflt_on    SET
ansi_warnings    SET
ansi_padding    SET
ansi_nulls    SET
concat_null_yields_null    SET
isolation level    read committed

textsize    2147483647
language    us_english
dateformat    mdy
datefirst    7
lock_timeout    -1
quoted_identifier    SET
arithabort    SET
ansi_null_dflt_on    SET
ansi_warnings    SET
ansi_padding    SET
concat_null_yields_null    SET
isolation level    read committed

textsize    2147483647
language    us_english
dateformat    mdy
datefirst    7
lock_timeout    -1
quoted_identifier    SET
arithabort    SET
ansi_null_dflt_on    SET
ansi_warnings    SET
ansi_padding    SET
ansi_nulls    SET
concat_null_yields_null    SET
isolation level    read committed

I dont see anything funny here. Please tell me if you do.

I run the statement in my SP like this:

USE MY_DATABASE
GO

DBCC USEROPTIONS;
exec ProsjektGetListByProsjektleder
@Prosjektleder=N'NO-GK-1356',@ProduktGruppe=N'A'
GO

DBCC USEROPTIONS;

declare @Prosjektleder nvarchar(50);
declare @ProduktGruppe nvarchar(50);
.....

This is the output:

textsize    2147483647
language    us_english
dateformat    mdy
datefirst    7
lock_timeout    -1
quoted_identifier    SET
arithabort    SET
ansi_null_dflt_on    SET
ansi_warnings    SET
ansi_padding    SET
ansi_nulls    SET
concat_null_yields_null    SET
isolation level    read committed

textsize    2147483647
language    us_english
dateformat    mdy
datefirst    7
lock_timeout    -1
quoted_identifier    SET
arithabort    SET
ansi_null_dflt_on    SET
ansi_warnings    SET
ansi_padding    SET
ansi_nulls    SET
concat_null_yields_null    SET
isolation level    read committed

textsize    2147483647
language    us_english
dateformat    mdy
datefirst    7
lock_timeout    -1
quoted_identifier    SET
arithabort    SET
ansi_null_dflt_on    SET
ansi_warnings    SET
ansi_padding    SET
ansi_nulls    SET
concat_null_yields_null    SET
isolation level    read committed

Looks like they are all the same.

Do I need to set any options inside the SP?

Sincerely
Svein Terje Gaup

Alexander Kuznetsov skrev:

Show quote

> Svein,
>
> output USEROPTIONS at the start of your SP and compare it to the output
> of USEROPTIONS in QA as follows. If you see discrepancies, recompile
> your SP under correct settings.
>
> DBCC USEROPTIONS
> GO
>
> SET ANSI_NULLS OFF
> go
> CREATE PROCEDURE aa
> AS
> DBCC USEROPTIONS
> GO
> SET ANSI_NULLS ON
> go
> aa
> go
> DBCC USEROPTIONS
> GO
> DROP PROCEDURE aa
> go

14 Sep 2006 10:23 AM

Svein Terje Gaup

Ok, now I found out what was wrong. It was my mistake of course. Sorry
for wasting your time.

Sincerely
Svein Terje Gaup

Svein Terje Gaup skrev:

Show quote

> Hi, I forgot to mention, I'm running on SQL Server 2005.
>
> When running the exact statements you posted, I get this output from
> all the DBCC USEROPTIONS:
>
> textsize    2147483647
> language    us_english
> dateformat    mdy
> datefirst    7
> lock_timeout    -1
> quoted_identifier    SET
> arithabort    SET
> ansi_null_dflt_on    SET
> ansi_warnings    SET
> ansi_padding    SET
> ansi_nulls    SET
> concat_null_yields_null    SET
> isolation level    read committed
>
> textsize    2147483647
> language    us_english
> dateformat    mdy
> datefirst    7
> lock_timeout    -1
> quoted_identifier    SET
> arithabort    SET
> ansi_null_dflt_on    SET
> ansi_warnings    SET
> ansi_padding    SET
> concat_null_yields_null    SET
> isolation level    read committed
>
> textsize    2147483647
> language    us_english
> dateformat    mdy
> datefirst    7
> lock_timeout    -1
> quoted_identifier    SET
> arithabort    SET
> ansi_null_dflt_on    SET
> ansi_warnings    SET
> ansi_padding    SET
> ansi_nulls    SET
> concat_null_yields_null    SET
> isolation level    read committed
>
> I dont see anything funny here. Please tell me if you do.
>
> I run the statement in my SP like this:
>
> USE MY_DATABASE
> GO
>
> DBCC USEROPTIONS;
> exec ProsjektGetListByProsjektleder
> @Prosjektleder=N'NO-GK-1356',@ProduktGruppe=N'A'
> GO
>
> DBCC USEROPTIONS;
>
> declare @Prosjektleder nvarchar(50);
> declare @ProduktGruppe nvarchar(50);
> ....
>
> This is the output:
>
> textsize    2147483647
> language    us_english
> dateformat    mdy
> datefirst    7
> lock_timeout    -1
> quoted_identifier    SET
> arithabort    SET
> ansi_null_dflt_on    SET
> ansi_warnings    SET
> ansi_padding    SET
> ansi_nulls    SET
> concat_null_yields_null    SET
> isolation level    read committed
>
> textsize    2147483647
> language    us_english
> dateformat    mdy
> datefirst    7
> lock_timeout    -1
> quoted_identifier    SET
> arithabort    SET
> ansi_null_dflt_on    SET
> ansi_warnings    SET
> ansi_padding    SET
> ansi_nulls    SET
> concat_null_yields_null    SET
> isolation level    read committed
>
> textsize    2147483647
> language    us_english
> dateformat    mdy
> datefirst    7
> lock_timeout    -1
> quoted_identifier    SET
> arithabort    SET
> ansi_null_dflt_on    SET
> ansi_warnings    SET
> ansi_padding    SET
> ansi_nulls    SET
> concat_null_yields_null    SET
> isolation level    read committed
>
> Looks like they are all the same.
>
> Do I need to set any options inside the SP?
>
> Sincerely
> Svein Terje Gaup
>
>
> Alexander Kuznetsov skrev:
> > Svein,
> >
> > output USEROPTIONS at the start of your SP and compare it to the output
> > of USEROPTIONS in QA as follows. If you see discrepancies, recompile
> > your SP under correct settings.
> >
> > DBCC USEROPTIONS
> > GO
> >
> > SET ANSI_NULLS OFF
> > go
> > CREATE PROCEDURE aa
> > AS
> > DBCC USEROPTIONS
> > GO
> > SET ANSI_NULLS ON
> > go
> > aa
> > go
> > DBCC USEROPTIONS
> > GO
> > DROP PROCEDURE aa
> > go

14 Sep 2006 3:30 PM

Jim Underwood

Please share what you found. Most mistakes are common ones, and others can
benefit by knowing what your mistake was.

Show quote

"Svein Terje Gaup" <gau***@yahoo.com> wrote in message
news:1158229401.328703.222410@d34g2000cwd.googlegroups.com...
> Ok, now I found out what was wrong. It was my mistake of course. Sorry
> for wasting your time.
>
> Sincerely
> Svein Terje Gaup
>
>
> Svein Terje Gaup skrev:
> > Hi, I forgot to mention, I'm running on SQL Server 2005.
> >
> > When running the exact statements you posted, I get this output from
> > all the DBCC USEROPTIONS:
> >
> > textsize 2147483647
> > language us_english
> > dateformat mdy
> > datefirst 7
> > lock_timeout -1
> > quoted_identifier SET
> > arithabort SET
> > ansi_null_dflt_on SET
> > ansi_warnings SET
> > ansi_padding SET
> > ansi_nulls SET
> > concat_null_yields_null SET
> > isolation level read committed
> >
> > textsize 2147483647
> > language us_english
> > dateformat mdy
> > datefirst 7
> > lock_timeout -1
> > quoted_identifier SET
> > arithabort SET
> > ansi_null_dflt_on SET
> > ansi_warnings SET
> > ansi_padding SET
> > concat_null_yields_null SET
> > isolation level read committed
> >
> > textsize 2147483647
> > language us_english
> > dateformat mdy
> > datefirst 7
> > lock_timeout -1
> > quoted_identifier SET
> > arithabort SET
> > ansi_null_dflt_on SET
> > ansi_warnings SET
> > ansi_padding SET
> > ansi_nulls SET
> > concat_null_yields_null SET
> > isolation level read committed
> >
> > I dont see anything funny here. Please tell me if you do.
> >
> > I run the statement in my SP like this:
> >
> > USE MY_DATABASE
> > GO
> >
> > DBCC USEROPTIONS;
> > exec ProsjektGetListByProsjektleder
> > @Prosjektleder=N'NO-GK-1356',@ProduktGruppe=N'A'
> > GO
> >
> > DBCC USEROPTIONS;
> >
> > declare @Prosjektleder nvarchar(50);
> > declare @ProduktGruppe nvarchar(50);
> > ....
> >
> > This is the output:
> >
> > textsize 2147483647
> > language us_english
> > dateformat mdy
> > datefirst 7
> > lock_timeout -1
> > quoted_identifier SET
> > arithabort SET
> > ansi_null_dflt_on SET
> > ansi_warnings SET
> > ansi_padding SET
> > ansi_nulls SET
> > concat_null_yields_null SET
> > isolation level read committed
> >
> > textsize 2147483647
> > language us_english
> > dateformat mdy
> > datefirst 7
> > lock_timeout -1
> > quoted_identifier SET
> > arithabort SET
> > ansi_null_dflt_on SET
> > ansi_warnings SET
> > ansi_padding SET
> > ansi_nulls SET
> > concat_null_yields_null SET
> > isolation level read committed
> >
> > textsize 2147483647
> > language us_english
> > dateformat mdy
> > datefirst 7
> > lock_timeout -1
> > quoted_identifier SET
> > arithabort SET
> > ansi_null_dflt_on SET
> > ansi_warnings SET
> > ansi_padding SET
> > ansi_nulls SET
> > concat_null_yields_null SET
> > isolation level read committed
> >
> > Looks like they are all the same.
> >
> > Do I need to set any options inside the SP?
> >
> > Sincerely
> > Svein Terje Gaup
> >
> >
> > Alexander Kuznetsov skrev:
> > > Svein,
> > >
> > > output USEROPTIONS at the start of your SP and compare it to the
output
> > > of USEROPTIONS in QA as follows. If you see discrepancies, recompile
> > > your SP under correct settings.
> > >
> > > DBCC USEROPTIONS
> > > GO
> > >
> > > SET ANSI_NULLS OFF
> > > go
> > > CREATE PROCEDURE aa
> > > AS
> > > DBCC USEROPTIONS
> > > GO
> > > SET ANSI_NULLS ON
> > > go
> > > aa
> > > go
> > > DBCC USEROPTIONS
> > > GO
> > > DROP PROCEDURE aa
> > > go
>

15 Sep 2006 7:28 AM

Svein Terje Gaup

Well... while I thought I was running the exact same query, it turned
out I was not. I had removed one CASE in the correct script, which I
had not updated the Stored Procedure with. Bit embarrassing really...

Sincerely
Svein Terje Gaup

Jim Underwood wrote:

Show quote

> Please share what you found. Most mistakes are common ones, and others can
> benefit by knowing what your mistake was.
>
> "Svein Terje Gaup" <gau***@yahoo.com> wrote in message
> news:1158229401.328703.222410@d34g2000cwd.googlegroups.com...
> > Ok, now I found out what was wrong. It was my mistake of course. Sorry
> > for wasting your time.
> >
> > Sincerely
> > Svein Terje Gaup
> >
> >
> > Svein Terje Gaup skrev:
> > > Hi, I forgot to mention, I'm running on SQL Server 2005.
> > >
> > > When running the exact statements you posted, I get this output from
> > > all the DBCC USEROPTIONS:
> > >
> > > textsize 2147483647
> > > language us_english
> > > dateformat mdy
> > > datefirst 7
> > > lock_timeout -1
> > > quoted_identifier SET
> > > arithabort SET
> > > ansi_null_dflt_on SET
> > > ansi_warnings SET
> > > ansi_padding SET
> > > ansi_nulls SET
> > > concat_null_yields_null SET
> > > isolation level read committed
> > >
> > > textsize 2147483647
> > > language us_english
> > > dateformat mdy
> > > datefirst 7
> > > lock_timeout -1
> > > quoted_identifier SET
> > > arithabort SET
> > > ansi_null_dflt_on SET
> > > ansi_warnings SET
> > > ansi_padding SET
> > > concat_null_yields_null SET
> > > isolation level read committed
> > >
> > > textsize 2147483647
> > > language us_english
> > > dateformat mdy
> > > datefirst 7
> > > lock_timeout -1
> > > quoted_identifier SET
> > > arithabort SET
> > > ansi_null_dflt_on SET
> > > ansi_warnings SET
> > > ansi_padding SET
> > > ansi_nulls SET
> > > concat_null_yields_null SET
> > > isolation level read committed
> > >
> > > I dont see anything funny here. Please tell me if you do.
> > >
> > > I run the statement in my SP like this:
> > >
> > > USE MY_DATABASE
> > > GO
> > >
> > > DBCC USEROPTIONS;
> > > exec ProsjektGetListByProsjektleder
> > > @Prosjektleder=N'NO-GK-1356',@ProduktGruppe=N'A'
> > > GO
> > >
> > > DBCC USEROPTIONS;
> > >
> > > declare @Prosjektleder nvarchar(50);
> > > declare @ProduktGruppe nvarchar(50);
> > > ....
> > >
> > > This is the output:
> > >
> > > textsize 2147483647
> > > language us_english
> > > dateformat mdy
> > > datefirst 7
> > > lock_timeout -1
> > > quoted_identifier SET
> > > arithabort SET
> > > ansi_null_dflt_on SET
> > > ansi_warnings SET
> > > ansi_padding SET
> > > ansi_nulls SET
> > > concat_null_yields_null SET
> > > isolation level read committed
> > >
> > > textsize 2147483647
> > > language us_english
> > > dateformat mdy
> > > datefirst 7
> > > lock_timeout -1
> > > quoted_identifier SET
> > > arithabort SET
> > > ansi_null_dflt_on SET
> > > ansi_warnings SET
> > > ansi_padding SET
> > > ansi_nulls SET
> > > concat_null_yields_null SET
> > > isolation level read committed
> > >
> > > textsize 2147483647
> > > language us_english
> > > dateformat mdy
> > > datefirst 7
> > > lock_timeout -1
> > > quoted_identifier SET
> > > arithabort SET
> > > ansi_null_dflt_on SET
> > > ansi_warnings SET
> > > ansi_padding SET
> > > ansi_nulls SET
> > > concat_null_yields_null SET
> > > isolation level read committed
> > >
> > > Looks like they are all the same.
> > >
> > > Do I need to set any options inside the SP?
> > >
> > > Sincerely
> > > Svein Terje Gaup
> > >
> > >
> > > Alexander Kuznetsov skrev:
> > > > Svein,
> > > >
> > > > output USEROPTIONS at the start of your SP and compare it to the
> output
> > > > of USEROPTIONS in QA as follows. If you see discrepancies, recompile
> > > > your SP under correct settings.
> > > >
> > > > DBCC USEROPTIONS
> > > > GO
> > > >
> > > > SET ANSI_NULLS OFF
> > > > go
> > > > CREATE PROCEDURE aa
> > > > AS
> > > > DBCC USEROPTIONS
> > > > GO
> > > > SET ANSI_NULLS ON
> > > > go
> > > > aa
> > > > go
> > > > DBCC USEROPTIONS
> > > > GO
> > > > DROP PROCEDURE aa
> > > > go
> >