BenSCooper
Programmer
Dear All,
I'm creating my first multi-language (English\Arabic) SQL Server database (version 2005) and could really do with some advice on the finer points of collation settings.
The structure of the system is:
1 main SQL database (handles storage for the main product)
1 or more additional SQL databases (handles storage for optional bolt on modules)
All of these databases are set to use unicode data types (nvarchar etc) for tables, procedures and functions.
When the product is installed a SQL server instance is automatically installed. The main SQL database is then created.
The optional module databases are created from backup files that will be prepared here ready for distribution.
My concern here is that the SQL instance will be created with a collation matching the Windows regional settings (Arabic in this case), as I believe this is the default SQL behaviour. This in turn will cause the main SQL db to attract the Arabic_CI_AS (or similar) collation when it is created.
As my module databases are restored from backups they will have my default collation of Latin1_General_CI_AS.
I'm concerned that if I'm right and this scenario happens, any cross database views\procedures will fail with a collation conflict error.
My questions are:
1- Is my concern realistic or have I misunderstood the workings of SQL with regard collations?
2- If the concern is valid, what is the best solution to the problem? I thought of forcing the server and main db to use Latin1 collation when installing, but wouldn't this upset any sortation rules\dates for Arabic customers?
Any advice would be very gratefully received. Oh yeah, and sorry for the novel length post
Ben Cooper
I'm creating my first multi-language (English\Arabic) SQL Server database (version 2005) and could really do with some advice on the finer points of collation settings.
The structure of the system is:
1 main SQL database (handles storage for the main product)
1 or more additional SQL databases (handles storage for optional bolt on modules)
All of these databases are set to use unicode data types (nvarchar etc) for tables, procedures and functions.
When the product is installed a SQL server instance is automatically installed. The main SQL database is then created.
The optional module databases are created from backup files that will be prepared here ready for distribution.
My concern here is that the SQL instance will be created with a collation matching the Windows regional settings (Arabic in this case), as I believe this is the default SQL behaviour. This in turn will cause the main SQL db to attract the Arabic_CI_AS (or similar) collation when it is created.
As my module databases are restored from backups they will have my default collation of Latin1_General_CI_AS.
I'm concerned that if I'm right and this scenario happens, any cross database views\procedures will fail with a collation conflict error.
My questions are:
1- Is my concern realistic or have I misunderstood the workings of SQL with regard collations?
2- If the concern is valid, what is the best solution to the problem? I thought of forcing the server and main db to use Latin1 collation when installing, but wouldn't this upset any sortation rules\dates for Arabic customers?
Any advice would be very gratefully received. Oh yeah, and sorry for the novel length post
Ben Cooper