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

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

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