aggregate operation cannot take a nvarchar data type

[fubar]

I recently upsized my acess 2000 db to SQL 2000, but I am now having errors . . .

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]The sum or average aggregate operation cannot take a nvarchar data type as an argument.
/data_count.asp, line 4


The code within the asp file:
<% dataSQL = "SELECT SUM(filesize) FROM DL where show=1"
Set datacount = my_Conn.Execute(dataSQL)
anBytes = datacount(0)
%>

Can someone point me to a solution?
Thanks!

[Mike John]

If the column filesize only contains numerics then you could convert it to a suitable datatype via the cast or convert functions. so your query will be doing SUM(CAST .. .But if it only contains numerics why are you storing it in a varchar?

[fubar]

Unfortunately, I did not create the database. I am just the clean up man trying to get off of access on to a more robust system, and SQL is the only "allowable" option. I am also NOT a SQL programmer - just learning as I go along.

The (filesize) field is numeric only (in bytes), which I was totaling with the SELECT SUM statemnet then converting to GBytes with an ASP function. I have changed the nvarchar to numeric with success - I thank you for the tip.

[rmiao]

Double check the data type of filesize column, change to int if possible.