Import - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Import

Author

Topic

vicki
Posting Yak Master

117 Posts

Posted - 2002-04-25 : 16:13:04

Hi,

I have the text file and when I import into sql then I got 81752 rows and if I import into access then I got 93972 rows, how it diffrent? what should I do now?

Thanks

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2002-04-26 : 03:13:17

I would run a compare against the 2 tables to see what's being left out. Are you sure that SQL's giving you no error messages?
What is the total length of the text file?
How are you running the import?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-26 : 17:52:52

One place to check for errors is date values. I've had headaches when importing data from Access to SQL Server due to dates like 4/26/100. Access seems to accept that date, but it is out of range for SQL datetime fields.

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-08-10 : 08:49:44

I've dug up an old thread trying to find ways to pull date's from MS Access (ex. 12/23/02) into the SQL datetime field. As stated above, SQL will not accept that format. Does anyone know how to import the data into SQL while still maintaining the dates? Even if the times were all 8:00AM for example, that would be ok.

Thanks

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-10 : 19:36:41

If you do an import straight from Access into SQL then there shouldn't be a problem. I would assume that the date values are transmitted as decimals then interpreted at the other end (feel free to correct me on this...). The only exception is when Access has a dodgy date field.

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-08-11 : 08:22:47

When I do an import straight from access into an existing table, I get the following error:

Error at destination for Row number 1. Errors encountered so far in this task: 1. Insert error, column 3 ('Date Made', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Invalid character value for cast specification.

The are several columns in this table. Of which, three are date columns. For example the first 5 columns in Access are as follows:

In MS Access:

Column Name DataType

ID AutoNumber
NCR Number Text
Date Made Date/Time
Date NCR - Responded Date/Time
Date NCR is Assigned Date/Time

The table I am importing to (in SQL) is structured as follows:

In MS SQL:

Column Name DataType

ID Int
NCR Number nVarchar
Date Made DateTime
Date NCR - Responded DateTime
Date NCR is Assigned DateTime

All columns except Nulls...

An example of the data in the Date columns (MS Access) specified above is as follows:

Date Made Date Answered Date NCR is Assigned

12/16/989 1/23/1999 1/20/1999
12/15/1999 1/13/1999 1/13/1999
12/16/1998 2/3/1999 2/1/1999
2/4/1999 2/17/1999 2/17/1999
12/16/1999 2/25/1999 2/25/1999
4/6/1999 4/27/1999 4/21/1999
5/1/1999 5/28/1999 5/12/1999
3/18/1999 5/28/1999 5/18/1999

Any more thoughts ?

Thanks.....

John

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-08-11 : 08:38:00

Ok, my bad. Found the problem. Some of the dates were not properly formated in the MS Access DB (for instance the 1st one in the list above!). After corrected that, the data imported fine.

Thanks for your help.

John

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-08-11 : 08:38:16

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-08-11 : 08:38:16

scotrob11
Starting Member

1 Post

Posted - 2004-09-07 : 23:33:20

I also received the same error when bringing my Access database into SQL Server (status 6: Data overflow - Invalid character value for cast specification). In my case the field in question is a datetime field, but all of the data is in the format mm/dd/yyy such as:

9/12/2003
11/15/2003
12/1/2003

This field does not have timestamps -- there is a corresponding field with the timestamp, but I also save it this way to make it easier to do some queries. Anyway, it looks like Access doesn't mind a field saved this way, but SQL does. Is there anything easy I can do short of converting the data to include a timestamp. I would rather not do this, because I have many scripts that assume there will not be a timestamp and I would rather not change them.

Thanks in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources