Upsizing wizard failures

[thereverand]

I used the upsizing wizard to upsize a MS Access db in Access 2k
format to SQL Server 2000 format. The following failures occurred:
1) The upsizing wizard silently failed to upgrade all FK constraints.
2) Some unique compound keys were not upsized.
3) Many indexes were not upsized
4) Some indexes were duplicated and other, very bizarrly named,
indexes were created.

Is there a reason for these failures occurred? I have searched through
the knowledge base but really haven't had much luck.

This was a test upgrade, but eventually I need to migrate the DB and
application to SQL Server and I *must* be able to do this reliably.

Any help appreciated.

 

[Tom Wickerath]

Hi Reverand,

A couple of comments:

1.) First, you might have better results by posting your question to the
Discussions in SQL Upsizing newsgroup. Here is a link to that group using the
web portal:

http://www.microsoft.com/communitie...?dg=microsoft.public.access.sqlupsizing&exp=1

However, I'm not sure how much help you will get, given that SQL 2000 is not
the current version. I believe that you can download & install SQL Server
2005 Express for free. My understanding is that SSMA (SQL Server Migration
Assistant) has been significanly improved for this release.

2.) Try using Enterprise Manager to create the exact table structure that
you want. This way, you are not relying on some wizard to make the correct
guess. Then use DTS to transfer your records from the JET database to your
new SQL database.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[thereverand]

Hi Reverand,

A couple of comments:

1.) First, you might have better results by posting your question to the
Discussions in SQL Upsizing newsgroup. Here is a link to that group using the
web portal:

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg...

However, I'm not sure how much help you will get, given that SQL 2000 is not
the current version. I believe that you can download & install SQL Server
2005 Express for free. My understanding is that SSMA (SQL Server Migration
Assistant) has been significanly improved for this release.

2.) Try using Enterprise Manager to create the exact table structure that
you want. This way, you are not relying on some wizard to make the correct
guess. Then use DTS to transfer your records from the JET database to your
new SQL database.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/e...http://www.access.qbuilt.com/html/search.html
__________________________________________

Thx, I will check it out. 2005 is not an option though, as my client
is on 2000 and not ready to upgrade.

RPD

 

[Tony Toews [MVP]]

I used the upsizing wizard to upsize a MS Access db in Access 2k
format to SQL Server 2000 format. The following failures occurred:

I don't have specific answers to some of your questions but I'll
answer what I can.

1) The upsizing wizard silently failed to upgrade all FK constraints.
2) Some unique compound keys were not upsized.
3) Many indexes were not upsized

This may have happened because, and I sure could be wrong, SQL Server
doesn't like null values in a unique index. Although we do use that
effectively in Access.

4) Some indexes were duplicated and other, very bizarrly named,
indexes were created.

Did the bizarrely named indexes have names in GUID format? If so
those indexes were created back in Access 97/

See my Random Thoughts on SQL Server Upsizing from Microsoft Access
Tips page at http://www.granite.ab.ca/access/sqlserverupsizing.htm

There is a new tool from the SQL Server group.
SQL Server Migration Assistant for Access (SSMA Access)
http://www.microsoft.com/sql/solutions/migration/default.mspx

Now this maybe worth your while running just to help find the problems
and then port over the schema to SQL Server 2000 format. You can
install SQL Server Express on your own system to do the initial
conversion.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm