| Author |
 Topic  |
|
Rabid
Starting Member
USA
12 Posts |
 Posted - 02 July 2002 : 01:46:57
|
I've trolled these forums for four nights as I've tried to upgrade from Access97 v3.3.03 to SQL Server 7 (SP2) v3.3.05. After failing many combinations of upgrade paths, here's details of my most recent attempt. I'd be grateful if someone can help.
I successfully upgraded a copy of my Access97 v3.3.03 mdb to Access97 v3.3.05, (using setup.asp?RC=3). Everything checked out OK.
I did successful posts, replies, and "Update Counts". Then, I transferred a copy of the mdb to my SQL Server box, to eventually import with DTS.
I created a blank SQL Server 7 database using Enterprise Mangler, and created an account that is db_owner of the blank database, (I am admin of this SQL Server).
I modified config.asp to specify SQL Server, and to use the SQL Server 7 OLEDB connection string to my server.
I ran setup.asp?RC=5 to install empty v3.3.05 tables onto SQL Server 7.
That was successful. I did a few sample posts and replies. No problems.
I used DTS on SQL Server to import the v3.3.05 Access97 mdb into the blank SQL Server database, using the db_owner account I just created. For each table, I modified the transform to "Delete rows in dest table", and I checked "Enable identity insert". (I understand that I don't want to recreate the Access table structures, I just want to append the data from the Access tables into the corresponding empty tables, and allow identity inserts.)
The DTS package runs, and I get nine tables successfully transferred, and four tables failed. I examine the four failures, and get the following error messages. (I changed the bitmaps to monochrome to make them small. I also blanked out the db owner and db name in the title bar):
I am guessing there's something else I need to do in the DTS import into SQL Server, like change Advanced options for converting data types, or something, but I could find no posts describing such changes in these forums.
Thanks for any assistance. |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 02 July 2002 : 06:07:57
|
Weird, I never heard about that kind of problems. Tell me one thing, can you convert the Access 97 table to an Access 2000, before doing the DTS, and then try the DTS again?
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs
Edited by - ruirib on 02 July 2002 06:14:42 |
 |
|
|
Rabid
Starting Member
USA
12 Posts |
Posted - 02 July 2002 : 10:22:22
|
| I could try that. I have Access 2K at the day job. I'll convert and try to import it tonight. Thanks for your response. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Rabid
Starting Member
USA
12 Posts |
Posted - 03 July 2002 : 01:34:23
|
No luck. I used Access 2000 to convert the Access97 3303 mdb, and got the same errors when importing with DTS. I used Access 2000 to convert an upgraded Access97 3305 mdb, and also got the same errors: 9 good tables, 4 failed, as above.
I'm beginning to think the Access database is corrupt, as far as DTS is concerned. But the Access 2000 "Repair & Compact" reported no errors.
I will email you a link to the zipped mdb, in case you're curious enough to try to DTS-import it into your SQL7 database server. If not, I understand, no biggie. I'm not dead in the water; I can stick with Access.
thanks. |
|
|
|
Rabid
Starting Member
USA
12 Posts |
Posted - 03 July 2002 : 01:51:55
|
| Email sent to ruirib via this forum. Thanks for any help you can afford to spend time on! |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 July 2002 : 05:43:13
|
Ok, I'll have a look as soon as I can. I run SQL Server 2000, but there is no relevant difference there. I'll post here about the results. What's weird is that it seems DTS is mixing columns. Anyway I'll test it myself.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs
Edited by - ruirib on 03 July 2002 06:02:06 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 July 2002 : 08:10:32
|
I've managed to import all the data from all tables without a problem, except in the case of the FORUM_SUBSCIPTIONS table. Someone messed up this Access table really good. It had no primary key, SUBSCRIPTION_ID was not an AutoNumber as it should be, there were references, in TOPIC_ID, to topics that didn't exist , etc...
I just decided not to import any data from it (it had just a few records anyway) and everything is working. The problem is that I have SQL Server 2000, so I can't just backup and send you the database. So tell me how you want to proceed with this. If your SQL Server can be reached from the Internet maybe you can provide me with a login that allows me to DTS all this data to a blank Snitz DB...
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Rabid
Starting Member
USA
12 Posts |
Posted - 03 July 2002 : 12:35:33
|
Thank you for the research, and the offer to upload. How does this procedure sound?- I'll take the forums off-line to prevent posting during the conversion.
- I'll upgrade the Access97 from v3303 to v3305.
- I'll convert from Access97 to Access2000.
- I'll email you the link to the converted database, and conection info to my blank SQL7 database.
- You'll import it via your SQL2000 Enterprise Manager DTS, and report what database corrections I may need to make.
I'm not too worried about losing subscription data. Users can always re-subscribe. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 July 2002 : 12:45:39
|
That looks Ok, although you could convert your Access 97 DB to Access 2000 before upgrading to 3.3.05, but that is not really important.
Maybe you can also setup a test forum, so that we can test the database as soon as I complete the DTS. Otherwise I can setup one of my test forums to use your database and try it from there.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Rabid
Starting Member
USA
12 Posts |
Posted - 03 July 2002 : 15:47:54
|
| Great. I'll let you know when I am underway. |
|
|
|
Rabid
Starting Member
USA
12 Posts |
Posted - 20 July 2002 : 17:07:10
|
quote:
I've managed to import all the data from all tables without a problem, except in the case of the FORUM_SUBSCIPTIONS table. Someone messed up this Access table really good. It had no primary key, SUBSCRIPTION_ID was not an AutoNumber as it should be, there were references, in TOPIC_ID, to topics that didn't exist , etc...
FYI, I opened the snitz_forums_2000.mdb that comes with 3.3.05, in tools.zip, and the FORUMS_SUBSCRIPTIONS table has no primary key, nor is SUBSCRIPTION_ID an AutoNumber.
Anyway, I'm dropping the few records in mine, and starting to work again on this upgrade from Access97 3.3.05 to SQL7. I now have access to a SQL2000 box to DTS through, as you suggested above. Thanks for all of your help so far; I'll let you know how it goes. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 20 July 2002 : 17:38:53
|
quote:
FYI, I opened the snitz_forums_2000.mdb that comes with 3.3.05, in tools.zip, and the FORUMS_SUBSCRIPTIONS table has no primary key, nor is SUBSCRIPTION_ID an AutoNumber.
Anyway, I'm dropping the few records in mine, and starting to work again on this upgrade from Access97 3.3.05 to SQL7. I now have access to a SQL2000 box to DTS through, as you suggested above. Thanks for all of your help so far; I'll let you know how it goes.
Well I just verified that. It's pretty weird because the 3.1 database had both. My comment was based on my current 3.3.03 database, but I had upgraded it from 3.1. to 3.3.03. So this is a problem with the structure of the database being downloaded since at least version 3.3.03.
If you had an autonumber field to the table and set it as the primary key (you'll need to delete the existing subscription records first) your upgrade will proceed smoothly.
DO let me know how it goes.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
Rabid
Starting Member
USA
12 Posts |
Posted - 20 July 2002 : 19:41:37
|
| Whew. It's upgraded. I followed your suggestion and DTS'd from SQL2000 to SQL7. No complaints from the DTS process. I'm testing it now, and will put it back on-line shortly. Thanks. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Rabid
Starting Member
USA
12 Posts |
Posted - 20 July 2002 : 20:27:37
|
Yeah, that was weird, that I couldn't DTS an Access97 or Access2000 database, version 3305 (upgraded from 3303), directly into SQL Server 7. It failed on both my production and development SQL7 boxes, with the same errors in the screenshots, above.
In summary, to upgrade from Access97 v3.3.03, to SQL Server 7 v3.3.05, I had to DTS first into SQL Server 2000, then DTS over to SQL Server 7. |
|
|
|
Topic |
|
Topic Locked
. Glad to know it's working.