converting from access database

Post Reply

Author	Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic
Binji Members Profile Find Members Posts Newbie Joined: 23 February 2010 Status: Offline Points: 5	Post Options Post Reply Quote Binji Report Post Thanks(0) Quote Reply Topic: converting from access database Posted: 23 February 2010 at 2:39am
	First of all hello to all and i hope you will be able to help me. I have tried upscaling access database to MSSQL server and the data in the database seems ok but when i open the forum none of the special utf8 characters are displayed properly. Its the same thing with MySQL. I tried fresh install and posting something with special characters which displays fine. However in the database its not displayed as it should be. So Im guessing this forum uses a different encoding for database connection? If so which one and can i use utf8 to connect to my database?

WebWiz-Bruce Members Profile Find Members Posts Admin Group Web Wiz Developer Joined: 03 September 2001 Location: Bournemouth Status: Offline Points: 9844	Post Options Post Reply Quote WebWiz-Bruce Report Post Thanks(0) Quote Reply Posted: 23 February 2010 at 8:52am
	There is not a different connection string for the database for UTF-8 encoding. You did not mention which tolls you used to migrate the database, as this has a lot to do with keeping the encoding the same. I've always found the upsize wizard in Access is very good at upsizing the database, however for SQL Server 2008 you need to use 'Microsoft SQL Server Migration Assistant 2008 for Access' which is more tricky as you have to go through the database after and remove any constraints that should not have been created. You should also check the collocation of your database. Unless you changed it by default it shoudl be Latin1_General_CI_AS which I find works fine for UTF-8. You should also check that the data type for string fields is nvarchar as this also supports UTF-8.
	Web Wiz Forums Hosting ASP.NET Web Hosting

Binji Members Profile Find Members Posts Newbie Joined: 23 February 2010 Status: Offline Points: 5	Post Options Post Reply Quote Binji Report Post Thanks(0) Quote Reply Posted: 23 February 2010 at 12:18pm
	Sorry... the server version is 2005. I imported the data as latin1 and it works so far. Except for a few errors in import process anyway. I imported it in mySQL with Navicat Premium. Gonna try to import it in MS SQL next...

WebWiz-Bruce Members Profile Find Members Posts Admin Group Web Wiz Developer Joined: 03 September 2001 Location: Bournemouth Status: Offline Points: 9844	Post Options Post Reply Quote WebWiz-Bruce Report Post Thanks(0) Quote Reply Posted: 23 February 2010 at 12:37pm
	The mySQL version uses a very different schema than the Access and SQL Server versions and so you are unlikely to migrate the database from Access to mySQL and have a stable forum. You should instead go from Access to SQL Server as the database schemas are very much the same.
	Web Wiz Forums Hosting ASP.NET Web Hosting

Binji Members Profile Find Members Posts Newbie Joined: 23 February 2010 Status: Offline Points: 5	Post Options Post Reply Quote Binji Report Post Thanks(0) Quote Reply Posted: 23 February 2010 at 7:43pm
	I will if you tell me how to do that. If i upscale from Access it migrates fine but all the special characters are messed up. And i cant find any way to first convert everything to latin before migrating. Not even if i set the mssql database collation to latin. With MySQL i dumped the database, converted it into ANSI and reimported it. Here it seems id sooner invent my own database than accomplish this....

WebWiz-Bruce Members Profile Find Members Posts Admin Group Web Wiz Developer Joined: 03 September 2001 Location: Bournemouth Status: Offline Points: 9844	Post Options Post Reply Quote WebWiz-Bruce Report Post Thanks(0) Quote Reply Posted: 24 February 2010 at 10:20am
	I already said in a previous post regarding the datatypes used for the storage of text data using nvarchar
	Web Wiz Forums Hosting ASP.NET Web Hosting

Binji Members Profile Find Members Posts Newbie Joined: 23 February 2010 Status: Offline Points: 5	Post Options Post Reply Quote Binji Report Post Thanks(0) Quote Reply Posted: 31 March 2010 at 11:26pm
	Hi its me trying my hand at it again.. So this is what i did: created new MSSQL database (it created it as Slovenian_CI_AS as default so i changed it to Latin1_General_CI_AS) used migration assistant to migrate data installed forum as an upgrade result: still not ok :( If i set encoding to Win-1250 its almost right but i dont get any č, ć characters. I did set the data types translations to nvarchar where possible. Which constraints would i have to remove and how do i remove them? Then i created MySQL database as UTF8 and imported database structure. Then i imported data from Access DB using Navicat 8 but it could import all of it because of errors (probably due to encoding differences - data too long for some columns). I know i already managed to import it without errors but i have no idea which encodings i used... What i did get still had same problems as MSSQL database so i tried replacing all characters with characters forum recognizes. Which worked except that search doesnt work (i assume this is responsible because i tried searching on a fresh install and it works) and of course the fact of missing records... Right now im probably gonna try also replacing characters in database in MSSQL with Win-1250 and UTF8 characters and see it if works... edit: turns out some of the errors are also of type "Duplicate entry 'some entry' for key 3" for tblauthor for example... seems database restraints are the problem since if i import directly without first creating a structure the import goes fine (except still for some invalid values errors...). The forum also doesnt work properly aftewards of course... edit2: seems the "Duplicate entry" is due to the fact some of the users have special characters in their username and something in the import process cannot handle that. For example it seems to take boštjan and bostjan as same value... Im not 100% but fairly certain that is the case.. Edited by Binji - 01 April 2010 at 1:18am

Post Reply

Forum Jump

Forum Permissions View Drop Down

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum