Function works on SQLServer but not on MSDE

I have spent considerable time trying to debug this one without
success.
I have a database which runs on my client's SQLServer 2000. It
contains a scalar-valued text function which works fine.
Using the same function definition in the same way on MSDE it returns
the wrong result. I am baffled. Is there some incompatibility I
should know about?
The function (simplified) goes like this
ALTER FUNCTION dbo.fnDoseRate
(
@.ID Int,
@.WhichDoseRate Int
)
RETURNS Float
AS
BEGIN
DECLARE @.Dose Float
IF @.WhichDoseRate=1
SELECT @.Dose = 1.2
IF @.WhichDoseRate=2
SELECT @.Dose = 2.3
IF @.WhichDoseRate=3
SELECT @.Dose = 3.4
RETURN @.Dose
END
I call it from a query
SELECT X, Y, dbo.fnDoseRate(1,1), dbo.fnDoseRate(1,2),
dbo.fnDoseRate(2,3) FROM MyTable
and the query delivers the expected results
If I change the query to
SELECT X, Y, SUM(dbo.fnDoseRate(1,1)), SUM(dbo.fnDoseRate(1,2)),
SUM(dbo.fnDoseRate(2,3)) FROM MyTable GROUP BY X, Y
and the first 2 of the SUM fields return the same value (appropriate
only to 1,1). If I change the last call to 1,3 it gives the same wrong
value.
Any ideas?
Bill Manville
MVP - Microsoft Excel, Oxford, England
FWIW I should add that I created the function definition and the view
using an Access2002 ADP project.
Bill Manville
MVP - Microsoft Excel, Oxford, England
|||Are both engines at the same service pack level?
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Bill Manville" <Bill-Manville@.msn.com> wrote in message
news:VA.000013fd.3da5fb80@.msn.com...
> FWIW I should add that I created the function definition and the view
> using an Access2002 ADP project.
> Bill Manville
> MVP - Microsoft Excel, Oxford, England
>
|||>Are both engines at the same service pack level?
Good question.
The client controls the remote server so I don't readily know about
that. Is there a way I could interrogate it to find out?
Nor am I sure how to find out the service pack level of my MSDE; I'm a
bit of an amateur in this area! Can you point me in the right
direction?
Looking at SysInfo > Loaded Modules, I see
sqlserver 2000.080.0194.00
Is that the correct place to look? And is that the most recent
version? If not, where should I go to update it?
(I run Microsoft Update regularly but I guess it might not reach MSDE)
Bill Manville
MVP - Microsoft Excel, Oxford, England
|||SELECT @.@.Version
returns:
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 2)
Ah, I'm running on XP which is reported (in this case) as NT 5.1.
It seems to me there is a Microsoft site that lists the versions and the
service packs etc. associated with each. I'm pretty sure I have SS 2005 SP2
installed here.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Bill Manville" <Bill-Manville@.msn.com> wrote in message
news:VA.000013fe.3fc7c2fb@.msn.com...
> Good question.
> The client controls the remote server so I don't readily know about
> that. Is there a way I could interrogate it to find out?
> Nor am I sure how to find out the service pack level of my MSDE; I'm a
> bit of an amateur in this area! Can you point me in the right
> direction?
> Looking at SysInfo > Loaded Modules, I see
> sqlserver 2000.080.0194.00
> Is that the correct place to look? And is that the most recent
> version? If not, where should I go to update it?
> (I run Microsoft Update regularly but I guess it might not reach MSDE)
>
> Bill Manville
> MVP - Microsoft Excel, Oxford, England
>
|||OK, so my MSDE is
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000
00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Personal
Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and my client's server is
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005
23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard
Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I guess that indicates my MSDE is a bit out of date.
Now to find out how to get it updated...
Bill Manville
MVP - Microsoft Excel, Oxford,
|||Ah, it's looks like it--perhaps dangerously so. It might still be prone to
the network attacks the pre SP2 versions faced years ago.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Bill Manville" <Bill-Manville@.msn.com> wrote in message
news:VA.000013ff.412751a3@.msn.com...
> OK, so my MSDE is
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
> Aug 6 2000
> 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Personal
> Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>
> and my client's server is
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005
> 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard
> Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
>
> I guess that indicates my MSDE is a bit out of date.
> Now to find out how to get it updated...
> Bill Manville
> MVP - Microsoft Excel, Oxford,
>