Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
HELP! Access Upsizing to SQL Server 1
- Thread starter Ausburgh
- Start date
- Thread starter
- #2
- Thread starter
- #4
-
1
- #5
RobertT687
Programmer
A couple of things to look for. Not sure if they will help or not.
First, make sure none of the column names in the Access Table are SQL reserved words. Simple things like 'Date', 'Desc', 'Name' work fine in Access but are problematic in SQL.
Make sure the Access table definition does not include a 'Caption' entry that is different than the actual column name. The Wizard apparently tries to CREATE the table on SQL Server using real (or caption) name but the table load step uses the opposite convention and fails since the column nanes don't match.
There is a know issue trying to upsize an Access97 table to SQLServer2000. That usually fails with an 'Overflow' error. Only workaround is to upsize to SQL Server 7 and migrate the table to the 2000 server. There is a Knowledgebase article on this error, but I don't the article number.
I seem to remember an issue trying to upsize an Access table that has an Autonumber column.
Hope some of these help.
First, make sure none of the column names in the Access Table are SQL reserved words. Simple things like 'Date', 'Desc', 'Name' work fine in Access but are problematic in SQL.
Make sure the Access table definition does not include a 'Caption' entry that is different than the actual column name. The Wizard apparently tries to CREATE the table on SQL Server using real (or caption) name but the table load step uses the opposite convention and fails since the column nanes don't match.
There is a know issue trying to upsize an Access97 table to SQLServer2000. That usually fails with an 'Overflow' error. Only workaround is to upsize to SQL Server 7 and migrate the table to the 2000 server. There is a Knowledgebase article on this error, but I don't the article number.
I seem to remember an issue trying to upsize an Access table that has an Autonumber column.
Hope some of these help.
- Thread starter
- #6
RobertT687
Programmer
No, those names aren't reserved words and will work fine.
Some other things that I thought of later were to make sure none of the Access column names have imbedded spaces ('My Personal Settings'), start with a number ('4PartPaper' or '401kElection') or contain special characters ('401k%').
Does the table contain any Access data types like Yes/No, OLE Object, or Memo? Those may also cause problems.
Some other things that I thought of later were to make sure none of the Access column names have imbedded spaces ('My Personal Settings'), start with a number ('4PartPaper' or '401kElection') or contain special characters ('401k%').
Does the table contain any Access data types like Yes/No, OLE Object, or Memo? Those may also cause problems.
- Thread starter
- #8
- Thread starter
- #9
Can someone please tell me what this (below) means:
---------------------
ODBC --insert on a linked table 'tbl_Today' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Explicit value must be specified for identity column in table 'tbl_Today' when IDENTITY_INSERT is set to ON. (#545)
--------------
thanks
---------------------
ODBC --insert on a linked table 'tbl_Today' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Explicit value must be specified for identity column in table 'tbl_Today' when IDENTITY_INSERT is set to ON. (#545)
--------------
thanks
BOSSupport
Programmer
You may try to upsize the table without data and then use the DTS Import Data Wizard to import the data. It is very good a showing you the record number and the error. Go to the Access Table, find and fix and then import the data again until success.
- Status
Similar threads
- Locked
- Question
- Question