Why does SUM() return a text value from a decimal(15,2) column?

[mikebp]

Hi,

I've currently hit a problem when using SQL Server 2000 with Access XP via ODBC / OLEDB from SQL Server by Microsoft.

I have a table with two main fields I'm trying to query:

district_code as varchar(4)
actual_other_cost as decimal(15,2)

Doing a simple SELECT statement in T-SQL will return both columns with the correct data type format. However if I run the following T-SQL statement:

SELECT district_code, SUM(actual_other_cost) as actual_other_costs
FROM work_order_actuals
GROUP BY district_code

... actual_other_cost comes back as a string column with a comma separated value, e.g. "581,060,111.22" left justified. If I use a cast to convert the SUM to a decimal (15,2) like so:

SELECT district_code, CAST(SUM(actual_other_cost) as decimal(15,2)) as actual_other_costs
FROM work_order_actuals
GROUP BY district_code

... the SUM'ed column comes back with the correct data format as a decimal value of 581060111.22.

So, why do I need to use the CAST on a SUM aggregate function? is there a way to force the correct answer? And where does the problem likely reside? Access, ODBC, SQL Server 2000?

Any help would be greatly appreciated.

Thanks,

Mike Berry-Porter
mikebp@gallery-software.com

 

[zhavic]

It seems you have in your definnition of ODBC connection
checked option 'Use regional settings when outputting currency, numbers, dates and times'

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.