Ms Access to MS SQL server migration.

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

Import/Export (DTS) and Replication (2000)

Ms Access to MS SQL server migration.

Author

Topic

sagscorp22
Starting Member

6 Posts

Posted - 2004-10-06 : 15:01:19

Hi guys,

I am doing Ms Access to MS SQL server migration.
I have to import data from two tables in MS access with a common key id.

I used MS sql server's import function to map the data into sql server table.
1. Selected datasource as Microsoft access- Treiber(*.mdb)[Also tried it with Driver Do Microsoft access(*.mdb)]
2.Selected use a query to specify the data transfer
3.Specified the query.
4.Parsed successfully
5.Then preview showed all the required data as required
6.Then I ran the query
7.It gives popup "Successfully copied 1table(s) from access to MS Sql server. "

But when I open the sql table to see the data, the data is not there in the table.

Problem 2:
The destination (MS Sql) table has all the columns which allow Nulls.
Still if the source data has null values in any of the columns it gives error

The detailed error message is attached here with.

Can anybody help me out if I am going wrong somewhere?

Can anybody help me out if I am going wrong somewhere?

Regards,
Sag

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-10-06 : 19:17:00

Refresh you EM Tables and sort by create date... Your target may not be what you expect.

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-10-06 : 19:21:22

I cant see the error code however you probably have a data type mismatch.
Your best bet for doing this sort of process is to create the target table with the proper data types and not rely on the wizard for that... It will get them wrong, check and see if you have "memo" fields in access... run a query to see what the largest amount of data is... Then size your Sql column with data type Create a text or ntext that will accomodate your needs.

sagscorp22
Starting Member

6 Posts

Posted - 2004-10-07 : 09:33:30

There is no data type mismatch.
It had first but I took care of that. But it says "destination does not allow nulls" even though the destination is a NULL column.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources