transfertext to SQL server, "numeric field overflow"

[DataMiner]

This works fine, where rawDemandHistory is an Access table. (demandhistorydata is a variable containing the path to the text data being imported)

DoCmd.TransferText acImport, "demandhistorydataimportspec", "rawDemandHistory", demandhistorydata, False

I used SSMS to migrate rawDemandHistory to SQL server, renamed it RawDemandHistory1, and linked it to my Access front end

Now, if I try to run

DoCmd.TransferText acImport, "demandhistorydataimportspec", "rawDemandHistory1", demandhistorydata, False

I get "runtime error 3349, numeric field overflow".

Yes, it seems the obvious thing would be that the data types are set up wrong in SQL server. But I've looked at my data... all of it is integers between 0 and 10000, and I've got the data type set to int.

So, any ideas?

 

[SQL_Hell]

hey,

load the source data into a temp table, setting the offending column as varchar rather than int, then run the following against the temp table (where 'column' is your field name)

select isnumeric(column) from temptable

If you get any values that are zero then thats the column that cannot be converted to integer

 

[DataMiner]

Thanks. I tried this and got no zeroes.
HOwever,I did figure out the source of the problem: I did find that I have a few numbers that exceed the integer data type I have set. (at least in Access, isnumeric will return true for any number, not just integer) When importing into a native Access table, these just get automatically excluded,and you get one of those "data_importerrors" tables generated. But when importing into a linked SQL server table, it doesn't behave that nicely and thus I get the ODBC error shown above.

I'm sure that the large numbers (which indicate weekly build quantites of >3,000,000,000, hardly likely in our manufacturing area!) are errors in my incoming data, but I have little to no control over that. So, I guess I'll change my field data type to 'real' and then probably zero out the unbelievable numbers after import.

 

[SQL_Hell]

Hi again

You could use bigint as your data type instead of real

bigint

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes