Search The ForumSearch   RegisterRegister  LoginLogin

AfterLogic WebMail Pro
 AfterLogic Forum : AfterLogic WebMail Pro
Subject Topic: Conversion from Access to SQL Server Post ReplyPost New Topic
Author
Message << Prev Topic | Next Topic >>
CRitchie
Newbie
Newbie


Joined: 22 March 2005
Location: United States
Online Status: Offline
Posts: 39
Posted: 01 April 2005 at 9:09am | IP Logged Quote CRitchie
I started out using Access but would like to switch over to SQL Server. Problem is I already have users that have folders and saved emails and such.

You wouldn't happen to have a conversion utility would you? I'm using version 3.1.

Back to Top View CRitchie's Profile Search for other posts by CRitchie
 
Alex
AfterLogic Support
AfterLogic Support
Avatar

Joined: 19 November 2003
Online Status: Offline
Posts: 2206
Posted: 01 April 2005 at 9:29am | IP Logged Quote Alex
There is no special utility for this but it can be done using SQL Server Import/Export Data Wizard.

First, you must create all the SQL tables just by switching WM Pro to MS SQL mode (tables are created automatically if they don't exist). Now, when you have empty tables on SQL Server, you can copy data from Access database into MS SQL database using Import/Export Data Wizard. Since all the tables used by WM Pro have the same format for both Access and MS SQL, just copy data from all the tables in Access database onto MS SQL server.

Regards,
Alex
Back to Top View Alex's Profile Search for other posts by Alex
 
CRitchie
Newbie
Newbie


Joined: 22 March 2005
Location: United States
Online Status: Offline
Posts: 39
Posted: 01 April 2005 at 9:53am | IP Logged Quote CRitchie
Yes... that should work just fine. I'm thinking about using MSDE or MS SQL Server Express.

Any comments on this? I wouldn't think there would be a problem with either of these given the small number of users I have (8-10).
Back to Top View CRitchie's Profile Search for other posts by CRitchie
 
Alex
AfterLogic Support
AfterLogic Support
Avatar

Joined: 19 November 2003
Online Status: Offline
Posts: 2206
Posted: 01 April 2005 at 10:04am | IP Logged Quote Alex
Yes, 10 users installation should work fine with any supported database server.

Regards,
Alex
Back to Top View Alex's Profile Search for other posts by Alex
 
CRitchie
Newbie
Newbie


Joined: 22 March 2005
Location: United States
Online Status: Offline
Posts: 39
Posted: 01 April 2005 at 3:06pm | IP Logged Quote CRitchie
Thanks, I'll give it a shot this weekend.
Back to Top View CRitchie's Profile Search for other posts by CRitchie
 
CRitchie
Newbie
Newbie


Joined: 22 March 2005
Location: United States
Online Status: Offline
Posts: 39
Posted: 03 April 2005 at 10:03pm | IP Logged Quote CRitchie
Well after fighting with MSDE and getting security all setup the conversion went ok.

However when importing data from the Access database to the SQL Server database the wm_message table had errors. The error appeared to be related to the fact that the x_spam column in the Access model allows nulls while the SQL Server model doesn't.

It just so happened that there were rows in my Access database with x_spam set to null. I set all the nulls to '0' to import them successfully.

Just thought I'd let you know just in case there is a disconnect in the DB models.

BTW, I started at 3.0 and upgraded to 3.1 so it is possible the problem may be related to this. If x_spam is a new column for 3.1 then maybe it wasn't set to a value for old messages.
Back to Top View CRitchie's Profile Search for other posts by CRitchie
 
Alex
AfterLogic Support
AfterLogic Support
Avatar

Joined: 19 November 2003
Online Status: Offline
Posts: 2206
Posted: 04 April 2005 at 12:50pm | IP Logged Quote Alex
Thanks for the point! We'll take this fact into account for future versions.

Regards,
Alex
Back to Top View Alex's Profile Search for other posts by Alex
 
CRitchie
Newbie
Newbie


Joined: 22 March 2005
Location: United States
Online Status: Offline
Posts: 39
Posted: 05 April 2005 at 10:22pm | IP Logged Quote CRitchie
After "upgrading" to SQL Server attachments quick working. WebMail appeared unable to save attachment rows but no error was given. I tracked it down to two columns in wm_attachments with differing attributes from Access to SQL Server. In the SQL Server model I set str_original_name and str_temp_name to allow NULLS and everything seems ok again.

The Access database doesn't require values for these columns and it evidently makes a difference.

Back to Top View CRitchie's Profile Search for other posts by CRitchie
 
Alex
AfterLogic Support
AfterLogic Support
Avatar

Joined: 19 November 2003
Online Status: Offline
Posts: 2206
Posted: 06 April 2005 at 9:03am | IP Logged Quote Alex
Many thanks for this!

Regards,
Alex
Back to Top View Alex's Profile Search for other posts by Alex
 
CRitchie
Newbie
Newbie


Joined: 22 March 2005
Location: United States
Online Status: Offline
Posts: 39
Posted: 06 April 2005 at 9:42am | IP Logged Quote CRitchie
Unfortunately one of my users is still experiencing issues. But I think it is outside of MailBee and instead it is some sort of permission or size limit issue.

I'm able to get attachments but they are not.
Back to Top View CRitchie's Profile Search for other posts by CRitchie
 
Alex
AfterLogic Support
AfterLogic Support
Avatar

Joined: 19 November 2003
Online Status: Offline
Posts: 2206
Posted: 06 April 2005 at 9:53am | IP Logged Quote Alex
You can disable error handling in Administration Panel/Debug Settings to allow WebMail to diplay standard IIS errors on failure. If IIS is configured to show errors (or it's configured to send error nofitfication by mail) it's possible you'll see what's going on and exact place which fails.

Regards,
Alex
Back to Top View Alex's Profile Search for other posts by Alex
 
CRitchie
Newbie
Newbie


Joined: 22 March 2005
Location: United States
Online Status: Offline
Posts: 39
Posted: 06 April 2005 at 8:57pm | IP Logged Quote CRitchie
Another one figured out. Seems IIS6 sets a limit on the size of the request to just over 200K for ASP applications. You can change this and most likely will if you want to send attachments of bigger than 200K.

Follow this link to find out how to raise the limit:
Fix

This only pertains to IIS6 only.
Back to Top View CRitchie's Profile Search for other posts by CRitchie
 
Alex
AfterLogic Support
AfterLogic Support
Avatar

Joined: 19 November 2003
Online Status: Offline
Posts: 2206
Posted: 07 April 2005 at 9:19am | IP Logged Quote Alex
Thanks for your posting. Seems AspMaxRequestEntityAllowed issue is very common, so we'll add this into WebMail FAQ.

Regards,
Alex
Back to Top View Alex's Profile Search for other posts by Alex
 
cooltxtuk
Newbie
Newbie
Avatar

Joined: 05 December 2005
Location: United Kingdom
Online Status: Offline
Posts: 27
Posted: 02 February 2006 at 10:41am | IP Logged Quote cooltxtuk
Have all the issues raised in this post been fixed with the latest version of Webmail Pro?

I want to switch from Access to SQL but have limited time to do so and can't really afford to loose web email for a day so a 'smooth' upgrade would be nice.

Or, is there an 'upgrade' utility now?

Thanks

-Mark
Back to Top View cooltxtuk's Profile Search for other posts by cooltxtuk Visit cooltxtuk's Homepage
 
Alex
AfterLogic Support
AfterLogic Support
Avatar

Joined: 19 November 2003
Online Status: Offline
Posts: 2206
Posted: 02 February 2006 at 1:09pm | IP Logged Quote Alex
We're working on Access-SQL updating script. We'll send it to you in a few days.

Regards,
Alex
Back to Top View Alex's Profile Search for other posts by Alex
 
cooltxtuk
Newbie
Newbie
Avatar

Joined: 05 December 2005
Location: United Kingdom
Online Status: Offline
Posts: 27
Posted: 02 February 2006 at 1:54pm | IP Logged Quote cooltxtuk
Thanks Alex!

I must say, your level of support is very high!

-Mark
Back to Top View cooltxtuk's Profile Search for other posts by cooltxtuk Visit cooltxtuk's Homepage
 
Alex
AfterLogic Support
AfterLogic Support
Avatar

Joined: 19 November 2003
Online Status: Offline
Posts: 2206
Posted: 08 February 2006 at 6:55am | IP Logged Quote Alex
We've sent you the Access-SQL migration script. Please let us know if it works as expected.

BTW, this script only reads the source .mdb file. It does not write anything to the .mdb neither deletes this file after the conversion, so no data would be lost even if the conversion failed.

Regards,
Alex
Back to Top View Alex's Profile Search for other posts by Alex
 
cooltxtuk
Newbie
Newbie
Avatar

Joined: 05 December 2005
Location: United Kingdom
Online Status: Offline
Posts: 27
Posted: 08 February 2006 at 7:52am | IP Logged Quote cooltxtuk
Thank you Alex, The script was brilliant and worked perfectly.

Mark
Back to Top View cooltxtuk's Profile Search for other posts by cooltxtuk Visit cooltxtuk's Homepage
 

If you wish to post a reply to this topic you must first login
If you are not already registered you must first register

  Post ReplyPost New Topic
Printable version Printable version

Forum Jump

Powered by Web Wiz Forums version 7.9
Copyright ©2001-2004 Web Wiz Guide