Register

Database Arabic Text collation With SQL Server Problem ????

If you are using Softex Web Hosting and Email Services, this forums are dedicated to provide help materials, instructions, technical support, tips and tricks about using your Hosting plan and benefiting from it to the max.
Site Admin
Posts: 180
Joined: 27 Feb 2012, 16:26
Location: Egypt , Cairo

Database Arabic Text collation With SQL Server Problem ????

Postby Softex » 17 Dec 2014, 00:20

Softex provides a wide range for both Linux and Windows Hosting plans. With our windows hosting plans, we do provide SQL Server Database hosting along with other optional services such as "Dedicated IP" and "SSL Certificates for your domains", etc.

To know more about our hosting plans specs and prices, you can visit : http://www.softexsw.com/en/hosting-services.php


One of the main problems we usually face with our customers deploying their Arabic Based .net applications with SQL Server databases into our hosting services, is the Arabic collation problems.

Symptoms:
The web application starts to display question marks "??????????" instead of Arabic text retreived from within the SQL Database. So if the Retrieved item name is 'محمد' then it will show on the web page as '????'


Cause of the Problem:
This is a collation problem within your database setup , The database objects default collation should be set to Arabic upon creation instead of leaving it to Server Default (this is supposed to be defined by the Creator of the database).

Resolution:
If you would like to fix this, you need to change the database collation to Arabic. To change the Database collation to Arabic you will need to run the below script

Code: Select all
ALTER DATABASE <DBName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Alter database <DBName> collate Arabic_ci_as
Go
ALTER DATABASE <DBName> SET MULTI_USER
GO


* Please replace <DBName> with your actual database name

Explanation and Important Notes:
This code sets the database to single mode access, since changing collation must be done on Database with Single Access Mode, once mode is changed , the second command changes the database collation to Arabic , and then the last command changes the database back to Multi-user access Mode (Default mode).

The only important note you need to know about is that if you have users defined functions that have return type as @Table , you will need to drop them all before changing collation and then recreate them again after changing the collation. If you do not have functions with that return datatype, then you don't have to worry about anything.

Disclaimer:
It is always preferred to check and revise this script with your application developer to make sure it will not affect his internal structure before running it on your production database, you might even want to try it on test database first to make sure everything goes fine before running it on your live database.

Happy SQL :)
Softex Software House
Tech. Team
http://www.softexsw.com

Return to Softex Hosting and Email Services

Who is online

Users browsing this forum: No registered users and 1 guest

Fatal: ./cache/ is NOT writable.