Frequently Corrupted DB problem

[John Martin]

I am a MS-SQL newbie and I am probably doing something wrong and I could use
some help.

I want to migrate some MS-Access Apps to SQL server, and I have already
created some TopSpeed Clarion apps and VB apps to use MSSQL with Windows
2000 Small Business Server.
I want to gradually move functions from the existing Access DB to the MSSQL
DB, so currently the Access DB is the live DB with all update programs, and
some web apps and reports are running against the MSSQL DB which is updated
daily.

To migrate the access DB, (a big one, 64mb, with some 100K+ record tables),
I used the Import-Export wizard and subsequently saved the package. The
package typically excutes the migration of the 25 tables well about 2 or 3
times, and then begins to consistently fail, and frequently corrupts the DB.
I then delete the DB, and do the import from scratch, and then recreate my
indexes over 3 tables. I reduced the threads from 4 to 1, but the stability
imporved only slightly.

I have written migration programs which work flawlessly, but they take 8 to
12 hours to migrate the data versus 15 minutes for DTS.

I know many more details could be described, but I think I have described
the general conditions.

Any ideas?

Thanks and best wishes,

John

[Robert Lummert]

create linked server to .mdb-file(don't forget to make the mssql-service
running an nt-account that may access this file).
then make an sql-script that imports with inserts from rowsetprov:

insert into <mssqltable>
select <mdbcol1>, <mdbcol2>
from <yourLinkedServer>...<yourAccessTable>

works pretty good.
from time to time truncate the db's logfile.

[John Martin]

Thanks!

I really appreciate the advice.

Best wishes,

John
"Robert Lummert" <r...@w4u.com> wrote in message
news:3C3302AC...@w4u.com...

[John Martin]

Thank you very much, I appreciate the help!

Best wishes,

John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!