Import problem into SQL Server 2005 linked table

[hedgracer]

I have an import problem into an sql server 2005 linked table. The field is an int with null checked. It is a number of contracts field. The linked table in Access 2003 I have set as a long integer (the default) with 0 decimal places. I have an import specification set up as fixed width and have no trouble with that on any of the other lines in this file I am importing (a text file). This is the line I am having trouble with:

PE20090616800 800 560 7AE60055 7AE60055 TDRAKE 560Y 05USD00000005059125 00000000000000 00000000000000 00000005059125 00040473 NN X

For whatever reason the 00040473 is throwing a "numeric field overflow error" every time I import it. I am not getting this error on any other line (there are hundreds of lines in this file) so this is extremely puzzling. One other tidbit of information: when that number changed to 00032666 it imports into the table. Change the number to 00032777 and it throws the error. Can anyone give me a clue on this one? Any help is appreciated. Thanks.

Dave

 

[PHV]

An INT can't hold a value greater than 32767

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hedgracer]

Would I change it to numeric(18,0) in the sql server 2005 table? Or is the problem on the access side?

Dave

 

[hedgracer]

I think you are thinking of smallint. Smallint only goes to 32767. Int goes to 2,147,483,647. That is what is making this so puzzling.

Dave

 

[dhookom]

Are you creating a new table or importing into an existing table? If the table is existing, what is the data type of the field your number is going into? It should be Long or larger decimal.

Duane
Hook'D on Access
MS Access MVP