| Author |
 Topic Search  Topic Options
|
daugava 
Newbie
Joined: 26 February 2010
Status: Offline
Points: 5
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Error upscaling from Access to MSSQL
Posted: 27 February 2010 at 5:56am |
|
Hi,
I am trying to convert my Web Wiz forum database (version 8)
from Access to MS SQL, so I can upgrade to Web Wiz 9.
After I run the Access Upscale wizard, the tblThread table
on the SQL server is empty and if I review the log file, it shows
this error:
Relationship or Constraint Failed to Upsize: Server Error
547: The ALTER TABLE statement conflicted with the FOREIGN
KEY constraint tblGuestName_FK00 The conflict occurred in
database "forumsSQL", table "dbo.tblThread", column
'Thread_ID'.
Does anyone know what could be causing this?
Any way to fix it?
Thanks,
Andy
P.S. I ran this query in Access:
select * from tblGuestName where thread_id not in
(select thread_id from tblThread)
It took forever, but eventually finished and returned no results.
Edited by daugava - 27 February 2010 at 6:00am
|
 |
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 01 March 2010 at 2:49pm |
|
Not seen this error before when upsizing.
Do you allow guests to post in your forum?
One way around this I can see is deleting all the data in your tblGuestName table, however it would mean that any posts made by Guests would be listed as being posted by 'Guests' and not the name they entered when they made the post.
|
|
|
|
daugava
Newbie
Joined: 26 February 2010
Status: Offline
Points: 5
|
Post Options
Thanks(0)
Quote Reply
Posted: 01 March 2010 at 8:27pm |
 WebWiz-Bruce wrote:
Not seen this error before when upsizing.Do you allow guests to post in your forum?One way around this I can see is deleting all the data in your tblGuestName table, however it would mean that any posts made by Guests would be listed as being posted by 'Guests' and not the name they entered when they made the post.
|
I used to allow guest posting, but eventually disabled it to cut down on spam.
So, the only effect of clearing the tblGuestName is the fact that old guest posts will lost author's name?
That's not bad.. small price to pay for switching from Access to MS SQL :)
Update: I have cleared tblGuestName and tried upsizing again.
This time there is no error in the log, but tblThread is still empty on the SQL server when the Upsizing process completes.
Is there anything else I can try?
Edited by daugava - 01 March 2010 at 9:29pm
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 02 March 2010 at 12:10pm |
|
Try using Microsoft SQL Server Migration Assistant 2008 for Access.
It can work better than the upsize wizard but places in lots of unwanted constraints that you need to remove from the SQL Server database afterwards.
|
|
|
|
daugava
Newbie
Joined: 26 February 2010
Status: Offline
Points: 5
|
Post Options
Thanks(0)
Quote Reply
Posted: 03 March 2010 at 7:30pm |
|
I have SQL Server Express 2005, so I have tried this version of software:
SQL Server Migration Assistant 2005"
It converted most tables, but generated errors on tblTopic:
"Error occured during data migration.
See the log for the detailed information."
However, I am not clear where I can view the log file, do you happen to know this?
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 04 March 2010 at 9:08am |
Sorry I wouldn't know. We migrate Web Wiz Forums Access database to SQL Server for our hosting clients who move to our services and know that sometime it can be very easy and take only 10 minutes, other times the database can be a real pig, and although we always get success it has been know to take up to 6 hours to do. If you can't do it yourself, it maybe another option to have a look at our Web Wiz Forums hosted service as we do a migration service with it were we upsize the database for you. You also get the latest Premium Edition with free updates and if you have any issues with your forum as it is hosted on our network our engineers are able to go in and fix it directly. More info can be found on the page below:- http://www.webwiz.net/webhosting/web_wiz_forums_hosting.asp
|
|
|
|
daugava
Newbie
Joined: 26 February 2010
Status: Offline
Points: 5
|
Post Options
Thanks(0)
Quote Reply
Posted: 04 March 2010 at 6:15pm |
|
Bruce,
Hosting forums with your service is tempting, I was thinking
about that option too.
I have some questions about it:
1) Which hosting plan would I need for a forum where original Access database
is 160Mb and there are 4k forum members?
2) Is there extra cost for Access > MS SQL migration service if
I switch to your hosting?
3) After the migration, do the values like forum ID and thread ID
remain the same? This would help me to properly redirect existing pages
which are indexed by search engines.
4) Will I have control over source code?
Like, if I want to add my Adsense ads or something, will I be
able to do this?
5) Will I still have direct access to my forum database, via
SQL Management Studio or some similar tool?
Thanks,
Andy
Edited by daugava - 04 March 2010 at 9:49pm
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 05 March 2010 at 9:14am |
|
1. For a 160MB Access database you would need to go for the Forum Business Hosting Package as this would give you a 500MB SQL Server database.
2. The Access to SQL Server Migration is included in the Migration Service to migrate your forum from a previous provider and costs £19.99. It include the Access to SQL server Migration, checking the database for errors, rebuilding indexes for better performance, upgrading the forum to the latest Premium Edition, and configuring the settings to work with our servers.
3. Yes the data in the database such as Forum, Topic, and Post ID's will remain the same.
4. Yes you do have access to the source code and can add in your own Google Ads.
5. Yes you do have access to the SQL Server database either through the online tools that we provide or through SQL Management Studio. The online tools also allow you to create backups of the database which you can download, or store on our file server.
|
|
|
|
