Arabic Language Support in SQL Server 2005

SQL Server Technical Article

 

Writer: Emad Norbert

Applies To: SQL Server 2005 SP2

 

Summary: Microsoft SQL Server 2005 supports Arabic data in Unicode and non-Unicode formats. Get information about the Arabic language code page, collations, components, and functions, as well as feature-specific information concerning set up, administration, replication, data transformation, and more.

Download the Microsoft Word version of this article.

 

Table of Contents

Introduction. 1

Important Concepts. 1

Unicode. 1

Arabic Code Page 1256. 2

Data Types 2

Non-Unicode Data Types 2

Unicode Data Types 3

The N Prefix. 3

Hijri Date. 3

Reading Order 4

Sort Order 5

Collation. 5

Types of Collation. 6

Levels of Collation. 8

SQL Server Installation. 10

Instance Name. 10

Collation Settings 11

SQL Server Management Studio. 11

Setting the Default Server Language. 12

Creating and Maintaining Databases. 12

Tables 13

Stored Procedures 13

Views 14

SQL Query Editor. 15

SQL Server Administration. 16

Authentication Modes and User Names 16

Server Messages 16

Message Options 17

User-Defined Arabic Messages 17

Message Language. 17

Automating Administrative Tasks 18

Operators 18

Jobs 18

Alerts 18

SQL Mail 19

Backing Up and Restoring. 19

Replication. 20

Full-Text Search. 20

Language Support 20

Creating a Full-Text Index. 20

Internet Support 22

Creating IIS Virtual Directories 22

Using URL Queries 22

Templates 23

Reporting Services. 24

Creating Reports by Using Report Designer 24

Creating Reports by Using Report Builder 25

Analysis Services. 27

Elements of Multidimensional Models 27

Cubes 28

Measures 28

Dimensions 28

Schema. 28

SSIS. 28

Working with Analysis Services 29

MDX. 29

Cube Browser 29

Conclusion. 30

 

Introduction

Microsoft® SQL Server™ 2005 was designed to support all local languages (other than English) that are supported by the Microsoft Windows Server® 2003 operating system, including the Arabic language. In this article you will learn details about Arabic language support, including information about components, functions, and how the server supports Arabic data in Unicode and non-Unicode formats.

Important Concepts

This section covers concepts important to understanding Arabic language support in SQL Server.

Unicode

Unicode is a standard that was created by the Unicode Consortium, an organization that promotes a single character set for all languages. SQL Server 2005 supports the Unicode Standard, Version 3.2. Version 3.01 of the Unicode Standard is identical to ISO-10646, an international standard that matches all of the code points in Unicode.

Unicode works by supplying a unique code point for every character, regardless of platform, program, or language. A program that supports Unicode can handle data in any language. Because it is designed to cover all the characters of all the languages of the world, there is no need for different code pages to handle different sets of characters.

The easiest way to manage character data in international databases is to always use the Unicode nchar, nvarchar, and nvarchar(max) data types, instead of their non-Unicode equivalents, char, varchar, varchar(max) and text. That way, clients will see the same characters in the data as all other clients. If all the applications that work with international databases also use Unicode variables instead of non-Unicode variables, character translations do not have to be performed anywhere in the system.

UCS-2 is a predecessor of UTF‑16. UCS‑2 is different from UTF‑16; UCS‑2 is a fixed-length encoding that represents all characters as a 16-bit value (2 bytes), and therefore does not support supplementary characters. UCS‑2 is frequently confused with UTF‑16, which is used to internally represent text in the Microsoft Windows® operating systems (Windows NT™, Windows 2000, Windows XP, and Windows CE), but UCS-2 is more limited.

For more information about Unicode visit the Unicode Web site (http://www.unicode.org/). For more information about International Features in Microsoft SQL Server 2005, see International Features in Microsoft SQL Server 2005 on the Microsoft Developer Network (MSDN).

 

Arabic Code Page 1256

Microsoft Windows uses code page 1256 for the Arabic language.

SQL2k5AR01_big.gif

Figure 1: Code page 1256

Arabic code page 1256 is used by versions of SQL Server earlier than SQL Server 2000. However, starting with the release of SQL Server 7.0 Unicode is recommended mainly for multilingual applications. If an application is for Arabic use only, there is no need to adopt Unicode. Code page 1256 is supported by SQL Server 2005 and later.

Data Types

SQL Server 2005 supports both non-Unicode data types and Unicode data types.

Non-Unicode Data Types

The non-Unicode data types are char, varchar, varchar(max),  and text. These data types use the character representation schema in single-byte or double-byte code pages. (Arabic code page 1256 uses single-byte representation.) To use non-Unicode data types, the system collation must be Arabic; otherwise, the data will be corrupted and replaced with question marks (???).

Unicode Data Types

The Unicode data types are nchar, nvarchar, nvarchar(max), and ntext. These data types use Unicode character representation. Code pages do not apply to these data types. Using Unicode data types gives you the ability to use Arabic data even if the system collation is not Arabic (such as during SQL Server installation). Therefore, using Unicode data types in your applications is recommended to avoid data corruption.

 The N Prefix

When dealing with Unicode string constants in SQL Server 2005, you must precede all Unicode strings with a capital letter N. The N prefix stands for National Language in the SQL-92 standard, and must be uppercase. If the N prefix is not used, SQL Server uses the non-Unicode code page of the current database before it uses the string. The following example shows how to use the N prefix in code:

UPDATE TableName SET ColumnName = N'Arabic Text' WHERE id = 1000 INSERT INTO TableName (ColumnName) values(N'Arabic Text')

Hijri Date

The SQL Server datetime data type supports only Gregorian dates, but you can use a conversion function to convert the Gregorian date to the Hijri date based on the Kuwaiti algorithm.

Conversion to and from the Hijri calendar is possible through the CONVERT intrinsic. Two CONVERT styles are available in SQL Server 2005 to support Hijri dates:

·         130—Returns the date using the Hijri calendar, in dd mon yyyy hh:mi:ss:mmmAM format.

·         131—Returns the date using the Hijri calendar, in dd/mm/yy hh:mi:ss:mmmAM format.

The following example shows how the CONVERT function is used to represent the current date in a specified style:

 

SELECT CONVERT(char, GETDATE(), 100) AS [100]

RETURNS:

Aug 16 2000 11:50AM

 

You can then convert the data from a string to a date value in much the same manner:

SELECT CONVERT(datetime, 'Aug 16 2000 11:50AM', 100) AS [100]

RETURNS:

100

2000-08-16 11:50:00.000

 

If you convert dates in Style 130 (Kuwaiti or Hijri) to a char data type, the data may be corrupted if the collation is not one of the Arabic collations that use code page 1256 for Unicode conversions. For example, the following figure shows a column that has been converted to char and a second column that has been converted to nchar. In this example, the client computer uses the EN-US locale. Therefore, when you attempt to use the char data type, Arabic characters are converted into question marks, whereas if you use the nchar data type, Arabic characters are displayed.

Cc295829.SQL2k5ARFig2(en-US,SQL.90).gif

Figure 2: Using CONVERT with date/time data

However, even the string represented using nchar is still not correctly formatted, as it would be on an Arabic client computer, because of limitations in the Query Editor. The following illustration shows how the actual Hijri date string should appear.

Cc295829.SQL2k5ARFig3(en-US,SQL.90).gif

Figure 3: Hijri date string

Arabic cannot be rendered correctly because complex scripts, such as Arabic, have shaping rules that control how the data is rendered. In the case of Arabic, shaping has a more marked effect, because the actual shapes of letters can change depending on the surrounding letters. This problem does not happen in versions of Windows after Windows 2000, or in earlier, Arabic-enabled versions of Windows.

Additionally, the date string that is returned can cause problems in bidirectional cases, because the rules for the layout of bidirectional text used by an application, such as Internet Explorer, causes the date to appear as shown in the following illustration.

Cc295829.SQL2k5ARFig4(en-US,SQL.90).gif

Figure 4: Hijri date string example

This visual order (dd hh:mi:ss yyyy mon :) is not the order that would be expected; the problem can be considered to be a general limitation of the 130 style in the CONVERT function. You can work around this problem by adding the proper Unicode control character in front of the string, as in the following query:

SELECT NCHAR(8207) + CONVERT(nchar, GETDATE(), 130)

 

The NCHAR function returns a character based upon the passed-in Unicode code point; 8207 or hexadecimal 0x200F is the Right-to-Left Marker (RLM), and it causes the string to be correctly displayed.

Reading Order

Reading order is the overall direction of an ordered sequence of text, relating to the word order, not the order of the entered characters. For example, when Arabic is the keyboard language new characters always flow from right to left. When Latin is the keyboard language new characters flow from left to right.

Cc295829.SQL2k5ARFig5(en-US,SQL.90).gif

Figure 5: Reading order

·         The first two lines in Figure 5 demonstrate that no matter which reading order is chosen, text in pure Arabic or Latin appears according to the expected behavior of each language.

·         The third line shows that in an LTR reading order, Arabic text added to existing Latin flows toward the right of the Latin. An RTL reading order displays the added Arabic text to the left of the Latin.

·          The fourth line shows that in an LTR reading order, Latin text added to existing Arabic flows toward the right of the Arabic. An RTL reading order arranges the added Latin text to the left of the Arabic.

Sort Order

Sort order specifies the way that data values are sorted, affecting the results of data comparison. The sorting of data is accomplished through collations, and it can be optimized using indexes.

For example, a sort order defines whether the Arabic character 'https://msdn2.microsoft.com/en-us/library/Aa902664.sql_ArabicSupportText_01(en-us,SQL.80).gif' is less than, equal to, or greater than 'https://msdn2.microsoft.com/en-us/library/Aa902664.sql_ArabicSupportText_02(en-us,SQL.80).gif'. It also defines whether the collation is accent-sensitive (for example, whether 'https://msdn2.microsoft.com/en-us/library/Aa902664.sql_ArabicSupportText_03(en-us,SQL.80).gif' is equal or is not equal to 'https://msdn2.microsoft.com/en-us/library/Aa902664.sql_ArabicSupportText_04(en-us,SQL.80).gif').

Many SQL Server collations use the same code page but have a different sort order for the code page, allowing sites to specify the following options:

·         Whether characters are sorted based on the numeric value represented by their codepage bit patterns. This is known as binary sorting. Binary sorting is the fastest sorting method, and is always case sensitive. However, because the characters in a code page may not be arranged in alphabetical order for a given language, binary sorting does not always sort characters alphabetically.

·         Between case-sensitive and case-insensitive behavior. This setting does not affect Arabic characters.

·         Between accent-sensitive or accent-insensitive behavior. In addition, there are some linguistic sorting rules.

Note In SQL Server 2005 sorting, there is no need to separately specify the code page, sort order for character (ASCII) data, and the collation for Unicode data. Instead, specify the collation name and sorting rules to use.

Collation

A collation specifies the bit patterns that represent each character in a character set. Collations also determine the rules that sort and compare data.

SQL Server 2005 can specify a collation at the server, database, and column levels. This enables the user to handle multinational applications easily. For example, you can define one database with French collation and another database with Arabic collation, in a single instance of SQL Server that uses English collation. You can also have different columns with different collations within the same table.

You can define the server collation during installation, the database collation when creating a new database (if you do not specify a collation, the database uses the server collation by default), and the column collation when creating a table (if you do not specify a collation, the column uses the database collation by default).

Each SQL Server collation specifies three properties:

·         The sort order to use for Unicode data types (nchar, nvarchar, and ntext).

·         The sort order to use for non-Unicode character data types (char, varchar, and text).

·          The code page used to store non-Unicode character data.

Note   Upgrading from SQL Server 7.0 or SQL Server 2000 to SQL Server 2005 keeps the previous SQL Server collation settings; no collation choice is required.

·          The SQL Server collation (specified for a database, for instance, for a column or object) has no OS dependency at all. Errors such as ‘?’ or ‘data corruption’ are caused by conversions between mismatched or incompatible codepages and normally occur in client/server scenarios.

·          For queries that are used with a native client (such as OLEDB, ODBC, and MDAC), binding char or varchar columns without the WCHAR client type causes ‘?’ and ‘data corruption’ errors if the DB column collation’s code page is not equal or compatible with the client’s OS.

·          For insert and update scenarios, if inserted or updated DB columns are char or varchar, ‘?’ and ‘data corruption’ errors could occur for the nchar/nvarchar parameter type (or N’ string literal) if the DB column collation’s code page is not the same as or compatible with the client’s OS, or the char/varchar parameter type (or ‘’ string literal). The same error can also occur if the instance collation’s code page does not match the code page of the string value and the DB column has a different code page from the instance.

·          Linked servers are used in SQL Server distributed queries, which allow remote database queries across different databases and even across different DB vendors (such as Oracle, DB2, and so on). Collation is an important factor of this feature.

Types of Collation

Windows collations

Windows collations define rules for storing character data based on the associated Windows locale. The base Windows collation rules specify which alphabet or language is used when dictionary sorting is applied, as well as the code page used to map non-Unicode character data.

For the Arabic language, select Arabic for all variations of Arabic that use the Arabic character set (code page 1256). For the sort order, select either Dictionary Sort or Binary Sort as shown in Figure 6. The dictionary sort order has four additional options; however, only accent sensitivity affects sort order in Arabic. Binary sort is always case sensitive and accent sensitive. The following figure shows how to specify an Arabic Windows collation for a table column.

Cc295829.SQL2k5ARFig9(en-US,SQL.90).png

Figure 6: Windows collation

Binary collations

Binary collations sort data based on the sequence of coded values defined by the code page. A binary collation in SQL Server defines the language locale and the ANSI code page to use, enforcing a binary sort order. Binary collations are useful for achieving improved application performance due to their relative simplicity. For non-Unicode data types, data comparisons are based on the code points defined in the ANSI code page. For Unicode data types, data comparisons are based on the Unicode code (UCS-2) points. For binary collations on Unicode data types, the locale is not considered in data sorts.

SQL Server collations

SQL Server collations provide sort order compatibility with earlier versions of SQL Server. SQL Server collations are based on legacy SQL Server sort orders for non-Unicode data—such as char and varchar data types—defined by SQL Server. The dictionary sorting rules for non-Unicode data are not compatible with any sorting routine provided by Windows operating systems, but the sorting of Unicode data is compatible with a particular version of Windows sorting rules. Because SQL Server collations use different comparison rules for non-Unicode data and for Unicode data, you may see different results when comparing the same data using the same sorting rules.

Note When you upgrade an instance of SQL Server, SQL Server collations can be specified for compatibility with existing instances of SQL Server. Because the default collation for an instance of SQL Server is defined during Setup, it is important to specify collation settings carefully when:

·         Your application code depends in some way on the behavior of previous SQL Server collations.

·         You are going to use SQL Server 2005 replication with existing installations of SQL Server 6.5 or SQL Server 7.0.

The collation to use in a database when both Unicode and non-Unicode columns are in the database

Using both Unicode and non-Unicode in the same database is not a recommended configuration. If you have a mix of Unicode and non-Unicode columns in your database, you should primarily use Windows collations. Windows collations apply Unicode-based sorting rules to both Unicode and non-Unicode data. This means that SQL Server internally converts non-Unicode data to Unicode to perform comparison operations. This provides consistency across data types in SQL Server and also provides developers with the ability to sort strings in applications that use the same rules that SQL Server uses.

SQL Server collations, on the other hand, apply non-Unicode sorting rules to non-Unicode data, and Unicode sorting rules to Unicode data, by using a corresponding Windows collation for the Unicode data. This difference can cause inconsistent results for comparisons of the same characters. Therefore, if you have a mix of Unicode and non-Unicode columns in your database, they should all be defined by using Windows collations so that the same sorting rules are used across Unicode and non-Unicode data.

Levels of Collation

Server-level Collation

The default collation of a SQL Server instance is set during Setup. The default collation of the instance becomes the default collation of the system databases: master, model, tempdb, msdb, and distribution. After a collation is assigned to any object other than a column or a database, you cannot change the collation except by dropping and re-creating the object. Instead of changing the default collation of a SQL Server instance, you can specify the collation when you create a new database or database column.

To query the server collation for a SQL Server instance, use the following Transact-SQL SERVERPROPERTY function:

SELECT CONVERT (varchar, SERVERPROPERTY('collation'))

To query the server for all available collations, use the following built-in function:

SELECT * from ::fn_helpcollations()

Database-level collations

When a database is created, the COLLATE clause of the CREATE DATABASE statement can be used to specify the default collation of the database. If no collation is specified during database creation, the database is assigned the default collation of the model database. The default collation for the model database is the same as the default collation of the SQL Server instance.

The collation of a user database can be changed with an ALTER DATABASE statement as follows:

ALTER DATABASE myDB COLLATE Arabic_CI_AS

The current collation of a database can be retrieved by using a statement as follows:

SELECT CONVERT (varchar, DATABASEPROPERTYEX('database_name','collation'))

Cc295829.SQL2k5ARFig10(en-US,SQL.90).png

Figure 7: Setting database collation properties

 


Column-level collations

When creating a table, collations for each character-string column can be specified by using the COLLATE clause of the CREATE TABLE statement. If no collation is specified during table creation, the column is assigned the default collation of the database.

The following example shows how to use Transact-SQL to specify a collation for a job description column that is set to Arabic, is case- and accent-insensitive, and is kana-type insensitive.

 

CREATE TABLE jobs

(

   job_id  smallint

      IDENTITY(1,1)

      PRIMARY KEY CLUSTERED,

   job_desc varchar(50)

      COLLATE Arabic_CI_AI_KS

      NOT NULL

      DEFAULT 'New Position - title not formalized yet',

)

 

The collation of a column can be changed with the ALTER TABLE statement as follows:

ALTER TABLE myTable ALTER COLUMN mycol NVARCHAR(10) COLLATE Arabic_CI_AS

 

Expression-level collations

Expression-level collations are set at the time a statement is run, and they affect the way a result set is returned. This allows sorting a result by using a language-specific ORDER BY clause. Use a COLLATE clause such as the following one to implement expression-level collations:

SELECT الإسم FROM العميل ORDER BY الإسم COLLATE Arabic_CI_AS

Arabic-specific information about collations

·         For insert and update operations in applications, the effective collation level is the database-level collation. Therefore, to insert or update Arabic data using non-Unicode data types, you must set the database-level collation to Arabic. It is important to set database-level collation to Arabic even if your column-level collation is set to Arabic, otherwise data may be corrupted when inserting or updating data.

·         Using Arabic accent sensitive data when selecting data from a database using Arabic collations affects the sort order when using the following:

·         Diacritics

·         (أپآ، أ‚آ، أƒآ، أ„آ، أ…آ،أ† )

·         (أ¬آ، أ­)

SQL Server Installation

This section outlines the steps that are necessary to install SQL Server so that it supports Arabic data handling.

Instance Name

Use the Instance Name page to add and maintain instances of SQL Server 2005. When Default is selected, a default instance of SQL Server 2005 is installed. The name of the default instance is the Computer Name. Only one installation of any version of SQL Server can be the default instance at a time. If Default is cleared, you can enter a name for the instance; however, the Instance Name cannot include Arabic characters.

Collation Settings

The Collation Settings page enables the user to configure sorting behavior. By default, the collation designator and sort order match the user’s locale. For the Arabic user, the default value is Arabic as shown in the following figure.

SQL2k5AR07_big.gif

Figure 8: Collation Settings page in Setup

SQL Server Management Studio

SQL Server Management Studio is the primary administrative tool for SQL Server. It provides a user interface that allows users to:

·         Define groups of instances of SQL Server

·         Register individual servers in a group

·         Configure all SQL Server options for each registered server

·         Create and administer all SQL Server databases, objects, logins, users, and permissions in each registered server

·         Define and execute all SQL Server administrative tasks on each registered server.

·         Design and test SQL statements, batches, and scripts interactively by invoking SQL Query Analyzer

·         Invoke the various wizards defined for SQL Server

SQL Server Management Studio supports Arabic characters but it does not support RTL reading order. When set to Arabic, SQL Server Enterprise Manager displays the Hijri date provided by the regional setting in Windows Server 2003.

Setting the Default Server Language

To set the default server language

1.    In Microsoft SQL Server Management Studio, right-click the server name.

2.    Click Properties.

3.    Click Advanced.

Cc295829.SQL2k5ARFig12(en-US,SQL.90).png

Figure 9: Setting the default server language

Creating and Maintaining Databases

All SQL Server objects support Arabic characters in the object name. You can manipulate the objects and administer SQL Server by using SQL Server Management Studio.

Arabic characters are supported in the names of all of the following: server groups, database names, physical data and log files names, file groups, diagrams, tables (columns, check constraints, relations, indexes, and so on), stored procedures, views, user-defined functions, rules, and defaults.

Because of this comprehensive support, a user with Windows Server 2003 and SQL Server 2005 can create a full Arabic environment in the database and its components.

Tables

The user can create tables with Arabic names, columns, relations, indexes, and check constraints. The user can also use Arabic names inside queries using SQL Query Editor, write stored procedures and user-defined functions in Arabic, and use Arabic in applications such as ASP pages. The following figure shows the properties for a table with an Arabic name as displayed in SQL Server Management Studio.

SQL2k5AR08_big.gif

Figure 10: Database, table, and columns with Arabic names

Stored Procedures

Users can create stored procedures with Arabic names. These stored procedures can connect to tables with Arabic names and retrieve Arabic data from them, as shown in the following figure.

SQL2k5AR09_big.gif

Figure 11: Stored procedure

Views

Users can create a view with an Arabic name. The view can connect to tables with Arabic names and retrieve Arabic data from it. If the visual tools in Design mode are not enough for your business query and you want to write the query yourself, you can view the query that creates the view from SQL Server Management Studio.

SQL2k5AR10_big.gif

Figure 12: Views

 

To view the text of the query that creates the view

1.    In SQL Server Management Studio, navigate to the view in which you want to see the Transact-SQL query.

2.     Right-click the view, and then click Properties.

Note With both tables and views, the user can use Arabic for diagrams, user-defined functions, rules, user-defined data types, and defaults.

SQL Query Editor

SQL Query Editor is used to interactively design and test Transact-SQL statements, batches, and scripts.

Like SQL Server Management Studio, SQL Query Editor supports Arabic characters but does not support RTL reading order.

SQL2k5AR12_big.gif

Figure 13: Query Editor

SQL Server Administration

Arabic support is included for various administrative tasks in SQL Server 2005. This section provides information about using Arabic in administrative scenarios such as configuring security and SQL Mail, publishing to the Web, managing server messages, automating administrative tasks, and backing up and restoring.

Authentication Modes and User Names

SQL Server can operate in one of two authentication modes: Windows Authentication or Mixed Mode Authentication.

Windows Authentication mode allows a user to connect through a Windows 2003 user account. Mixed Mode Authentication allows users to connect to an instance of SQL Server by using either Windows Authentication or SQL Server Authentication. Users who connect through a Windows Server 2003 user account can make use of trusted connections in either Windows Authentication Mode or Mixed Mode.

Both modes support Arabic characters in user names and passwords. When using SQL Server Authentication, the user can create Arabic user names and passwords. When using Windows Authentication, the user can select from Windows Arabic accounts that are supported by Windows Server 2003.

Server Messages

SQL Server Management Studio provides tools for managing server messages. Creating and sending server messages in Arabic requires that you understand the following concepts:

·         The SQL Server message language property

·         Error message language constraints

·         The message language default setting

·          The user language

These concepts are discussed in the following sections.

Message Options

SQL Server messages have the following properties:

·         Error number. This property specifies the user-defined error message number. User-defined error message numbers must be greater than 50,000.

·         Severity. This property specifies the SQL Server severity level of the message. Severity levels are between 1 and 25.

·         Message text. This property specifies the text of the message. The maximum number of characters is 255.

·         Language. This property specifies the language of the message. To create an Arabic language message, set the Language property to Arabic.

Note   You must create an English version of the message before you can create the message in Arabic or any other language. See the following section, User-Defined Arabic Messages.

·          Always write to Windows event log. This property specifies that the message should be written to the Windows application log. Select this option if you want your user-defined message to be monitored for alert purposes by SQL Server Agent.

User-Defined Arabic Messages

Users can add new messages in Arabic as long as there is another, English version of the message that has the same error number.

Sending messages by using RAISERROR

You can send SQL Server messages, including Arabic messages, in one of two ways:

·         By using the RAISERROR statement

·          By using alerts

By using the RAISERROR statement, you can raise Arabic messages when you have specific alerts, or as otherwise needed. The following example uses an Arabic message that has the number 50002, and has a severity of 10. To localize the session to Arabic, use the SET LANGUAGE command to send the Arabic version of the message. Otherwise the default English version is sent.

SET LANGUAGE Arabic

RAISERROR (50002,10,1)

To learn about using alerts to send Arabic messages, see Alerts later in this white paper.

Message Language

This section explains how to set the server default message language and the user language by using SQL Server Enterprise Manager. Setting the default to Arabic enables you to send Arabic versions of messages you create as user-defined messages.

To set the Arabic language to be the default for server messages

1.    In SQL Server Management Studio, right-click the server you are working with, and then click Properties.

2.     On the Server Settings tab, select Arabic from the Default Language for User list.

With SQL Server 2005, you can define the user language for system messages. For example, you can use Arabic for Arabic users, English for English users, and so on.

To specify Arabic as the language for a given login

·          In the SQL Server Login Properties dialog box, click the General tab, and then select Arabic from the Language list.

Automating Administrative Tasks

Automated administration is the programmed response to a predictable administrative responsibility or server event. By automating administration, you save time. Automated administration is configured using SQL Server Agent.

For example, if you want to back up the company servers every weekday after hours, you can create a job to perform this task and schedule the job to run at a specified time. If the job encounters a problem, SQL Server Agent can record the event and page you by sending an e‑mail.

The three main components of automatic administration are operators, jobs, and alerts.

Operators

An operator is an individual who is responsible for the maintenance of one or more instances of SQL Server. Operators are notified of alerts in one or more of the following ways:

·         E-mail

·         Pager (through e-mail)

·          Network terminal messages

The user can create an Arabic operator name and integrate the automation of jobs and alerts with support of Arabic messages and component names.

Jobs

A job is a specific series of operations performed sequentially by SQL Server Agent. Use jobs to define an administrative task that can be executed one or more times and monitored for success or failure each time it executes. Jobs can be executed in different ways:

·         They can run on one local server or on multiple remote servers.

·         They can run according to one or more schedules.

·          They can be triggered by one or more alerts.

Whichever way a job is run, SQL Server Agent can notify you when the job executes. You cannot change the job notification language from English to Arabic, but you can create Arabic job names and Arabic schedule names. These Arabic names will display correctly within the job notification.

Alerts

An alert signals a designated operator that an event has occurred. For example, an event can be a job starting or system resources reaching a threshold. You define the conditions under which an alert is generated. You also define which of the following actions the alert can take:

·         Notify one or more operators.

·         Forward the event to another server.

·          Execute a job.

You can trigger an alert through a user-defined Arabic message. For example, you can create an alert based on user-defined message number 50002, Arabic version.

Assign the alert to an operator, and specify the way to alert the operator.

Run the following command from SQL Query Editor and the specified operator will receive the notification.

RAISERROR (50002,10,1)

SQL Mail

SQL Mail provides a way to receive e-mail messages generated by SQL Server. SQL Mail can connect with Microsoft Exchange Server, Microsoft Windows NT® Mail, or a Post Office Protocol 3 (POP3) server.

To reach an operator, SQL Mail requires a post office connection, a mail store (mailbox), a mail profile, and a Windows NT 4.0 or Windows 2000/2003 domain user account that is used to log on to an instance of SQL Server. SQL Mail consists of a number of stored procedures, which are used by SQL Server to process e-mail messages that are received in the designated SQL Mail account mailbox or to reply to e-mail messages that are generated by the stored procedure xp_sendmail.

You can use the extended stored procedure xp_sendmail to send an e-mail message in Arabic, as shown in the following example:

Exec xp_sendmail 'name@microsoft.com',

@message=رساله تحذيريه',

@subject='رساله'

 

Note SQL Server also uses the SQLServerAgent service to send e-mail. SQLServerAgent does not use SQL Mail to send e-mail.

 

Database mail stored procedures such as sysmail_add_principalprofile_sp and msdb.dbo.sp_send_dbmail can be used to send Arabic messages as in the following example:

 

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'Test Administrator',
 @recipients = 'test@testserver.com',
 @body = 'هذا للتجربة فقط',
 @subject = 'تجربة' ;

Backing Up and Restoring

The backup and restore architecture of SQL Server 2005 provides an important safeguard for protecting critical data stored in SQL Server databases. With proper planning, you can recover from many failures, including:

·         Media failure

·         User errors

·          Permanent loss of a server

Additionally, backing up and restoring databases is useful for other purposes, such as copying a database from one server to another.

Using the Arabic collation with the BACKUP and RESTORE commands

In earlier versions of SQL Server, the sort order and code page of the database that was being copied were important. Because SQL Server 2005 supports multiple collations, the sort order and code page of the database are no longer relevant.

When you restore a database with SQL Server 2005, the RESTORE command uses the collation of the source database that was recorded in the backup file. The restored database therefore has the same collation as the original database that was backed up. Database objects that use different collations also retain their original collations. The database can therefore be restored even if the instance on which you run the RESTORE command has a different default collation from the instance on which the BACKUP command was run. This means that databases that use the Arabic collation back up and restore transparently under SQL Server 2005.

When backing up or restoring a database that uses the Arabic collation, note that:

·         You must verify that the Arabic collation of the database is supported by the instance of SQL Server.

·         You can restore a SQL Server 7.0 or SQL Server 2000 database backup to a SQL Server 2005 database.

·         You cannot restore a SQL Server 6.5 database backup to a SQL Server 2005 database.

·          You cannot restore a SQL Server 2005 database backup to a SQL Server 7.0 or SQL Server 6.5 database.

Replication

SQL Server 2005 replication allows you to copy, distribute, and modify data across your enterprise. SQL Server 2005 includes a number of methods and options for replication design, implementation, monitoring, and administration to give you the functionality and flexibility needed for distributing data and maintaining data consistency.

Replication offers various benefits depending on the type of replication and the options you choose, but the common benefit of SQL Server 2005 replication is the availability of data when and where it is needed.

When you have Arabic data, consider the following:

·         If replication is implemented between servers that use different character sets, SQL Server 2005 does not convert any of the replicated data and may mistranslate the data when it is replicated, because it is impossible to map all characters between character sets. Therefore, to guarantee a successful data replication, it is best to configure servers to use the same Arabic code pages and comparison styles.

·          Generally, in an environment that uses different character sets including the Arabic character set, consider using Unicode data types, which do not require conversion. Even in this case, however, varying behavior may result if different sort orders are used. Keep in mind that the subscriber and publisher both must use the same collation

Full-text search allows fast and flexible indexing for keyword-based querying of text data stored in a Microsoft SQL Server database. In contrast to the LIKE predicate, which only works on character patterns, full-text queries perform linguistic searches against text data, by operating on words and phrases based on rules of a particular language.

Language Support

In Microsoft SQL Server 2005, full-text queries can use languages other than the default language for the column that contains the data to search by using full-text queries. As long as the language is supported and its resources are installed, the language specified in the LANGUAGE language_term clause of the CONTAINS, CONTAINSTABLE, FREETEXT, and FREETEXTTABLE query is used for word breaking, stemming, and thesaurus and noise-word processing. However, Arabic is not supported.

Creating a Full-Text Index

Setting up full-text indexing capability on a table in Microsoft SQL Server 2005 is a two-step process:

1. Create a full-text catalog to store full-text indexes.

2.     Create full-text indexes.

Create a full-text catalog to store full-text indexes

To create a full-text catalog named AdvWksDocFTCat, use the CREATE FULLTEXT CATALOG statement as follows:

CREATE FULLTEXT CATALOG AdvWksDocFTCat

Create full-text indexes

To index a column in the current table

1.    Start the Full-Text Indexing wizard.

2.    Select the unique index you created, and then click Next.

3.    On the Select Table Columns page, find the column you want to index. In the Language for Word Breaker list, select Neutral as shown in the following figure. This option enables your index to work with Arabic even though that language is not included in the list of supported languages. In general, use this option when a column contains data in multiple languages or in an unsupported language. Click Next.

SQL2k5AR14_big.gif

Figure 14: Choosing the language for word breaker

4.    Follow the Full-Text Indexing wizard steps until you finish creating the full text indexing.

 

Following are examples of the Full-Text Search feature.

Using CONTAINS with a single word

This example finds all names that contain the word "احمد":

SELECT اسم_المسؤل from عملاء

WHERE CONTAINS(اسم_المسؤل, 'احمد')

 

Using CONTAINS with a phrase

This example finds all names that contain the word "احمد منير فريد" or "حمدى":

SELECT اسم_المسؤل from عملاء

WHERE CONTAINS(اسم_المسؤل, '"احمد منير فريد" OR " حمدى"')

 

Using CONTAINS with a prefixed wildcard string

This example returns all names with at least one word starting with the "ك" character:

SELECT اسم_المسؤل from عملاء

WHERE CONTAINS(اسم_المسؤل, '"ك*"')

 

Using CONTAINS with A word near another word

This example returns all names that have the word "منير" near the word "احمد":

SELECT اسم_المسؤل from عملاء

WHERE CONTAINS(اسم_المسؤل,' احمد NEAR منير')

 

Note Because SQL Server 2005 does not have an Arabic word breaker out of the box, text and documents that are in Arabic should be indexed and queried by using a different word breaker (such as the neutral word breaker). This might cause poor or bad recall results because the word breaker will not be aware of the linguistic rules of the Arabic language at indexing or query time.

Internet Support

Use Microsoft Internet Information Services (IIS) to grant users access through the Web.

Creating IIS Virtual Directories

Before you can access SQL Server by using a URL, a virtual directory must be set up on the machine running Microsoft Internet Information Services (IIS). The IIS Virtual Directory Management utility instructs IIS to create an association between the new virtual directory and a specific installation of SQL Server, including a database, along with the necessary connection information (user name, password) and access information.

The virtual directory name and the virtual names, including template, schema, and dbobject names all support Arabic characters. However, the IIS Virtual Directory Management utility does not support RTL reading order.

Using URL Queries

After the virtual directory is configured, you can use URL queries that use Arabic characters to connect to Arabic database objects and retrieve Arabic data; you can also use an Arabic root tag name. You can pass Arabic characters as parameters. In Microsoft Internet Explorer, use the RTL option to display the data correctly. The following figure illustrates Arabic parameter passing and RTL data display.

SQL2k5AR17_big.gif

Figure 15: URL queries with Arabic parameters

Templates

To support Arabic characters, you must specify Arabic or Unicode encoding in the XML template as follows:

·         <?XML version = "1.0" encoding="windows-1256" ?>

·         <?XML version = "1.0" encoding="UTF-8" ?>

 

If you use the second tag you must save the XML file with UTF-8 encoding.

The following example shows an XML template with a simple SELECT query.

<?xml version="1.0" encoding="windows-1256" ?>

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">

<sql:query>SELECT اسم_الشركة, اسم_المسؤل, وظيفة_المسؤل

FROM موردون

FOR XML AUTO

</sql:query></ROOT>

 

The following figure shows the result when accessing the template from the URL.

SQL2k5AR18_big.gif

Figure 16: Template example result

The following example shows a URL query with mapping schema.

 

<?xml version="1.0" encoding="UTF-8"?>

<Schema xmlns="urn:schemas-microsoft-com:xml-data"

        xmlns:sql="urn:schemas-microsoft-com:xml-sql">

  <ElementType name="عميل" sql:relation="عملاء" >

    <!-- attribute declarations for columns-->

    <AttributeType name="مدينة" />

    <AttributeType name="رقم" />

    <AttributeType name="اسم" />

    <AttributeType name="العنوان" />

    <AttributeType name="منطقة" />

    <!-- declare the instances  -->

    <attribute type="مدينة" sql:field="مدينة" />

    <attribute type="رقم" sql:field="رقم_العميل" />

    <attribute type="اسم" sql:field="اسم_الشركة" />

    <attribute type="العنوان" sql:field="عنوان" />

    <attribute type="منطقة" sql:field="منطقة"/>

  </ElementType>

</Schema>

 

The following figure shows the result.

SQL2k5AR19_big.gif

Figure 17: Result of URL query with mapping schema

Reporting Services

Microsoft SQL Server 2005 Reporting Services is a server-based solution for building enterprise reports that draw content from a variety of relational and multidimensional data sources, viewing reports in various formats, and centrally managing security and subscriptions. The reports that you create can be viewed over a Web-based connection or as part of a Microsoft Windows application or Microsoft SharePoint® portal.

Creating Reports by Using Report Designer

To create a report using the Report Wizard

1.    On the Project menu, click Add New Item. Alternatively, right-click the Reports folder in the project in Solution Explorer, point to Add, and then click Add New Item.

2.    In the Categories list, click Report Server Project.

3.    In the Templates list, click Report Wizard.

4.    Type a name for the report, and then click Open.

5.    On the Welcome to the Report Wizard page, click Next.

6.    On the Select the Data Source page, click New Data Source, type a name for the data source, select a data type, and then type the connection string for the data source. To build the connection string, click Edit. To supply credentials, click Credentials. When the string is complete, click Next.

7.    In the Design the Query page, type the query string to use for the report. To build a query string, click the build (...) button. After the string is built, click Next.

8.    On the Select the Report Type page, select Tabular or Matrix, and then click Next.

·         Tabular produces a report with data arranged in a table.

·         Matrix produces a report with data arranged in a matrix, or crosstab. Click Next.

9.    The next page depends on what you selected in the Select the Report Type page.

a.    On the Design the Table page, click a field in the Fields list, and then click the Page, Group, or Details button. Alternatively, drag the field into the appropriate box. When all fields are chosen, click Next.

b.    On the Choose the Table Layout page, select a layout for the table, and then click Next.

c.    On the Design the Matrix page, click a field in the Fields list and then click the Page, Columns, Rows, or Details button. Alternatively, drag the field into the appropriate box. When all fields are chosen, click Next.

10. On the Choose the Table Style or Choose the Matrix Style page, select a style to apply to the report, and then click Next.

11. On the Choose the Deployment Location page, type the report server and folder to which you want to publish the report. Click Next.

12.  On the Completing the Report Wizard page type a name for the report, verify the information, and then click Finish.

SQL2k5AR23_big.gif

Figure 18: Report editing

Creating Reports by Using Report Builder

Use Report Builder to create ad-hoc reports. Report Builder is a ClickOnce Windows Forms application that users download from the report server to their local computer. Users create reports by dragging fields from predefined report models onto a predesigned report layout template. Users can format, group, sort, and filter their data. In addition, they can edit or define formulas. With Report Builder users don't need to understand the underlying structure of the data source and they don't need to understand any complex computing languages. They simply must be familiar with the data in their data sources.

Ad-hoc reporting is based on models that you define in advance and then publish to a report server. A new type of project called a report model has been added to create the report models used by the Report Builder client. To work with a report model, you use Model Designer, which runs in Business Intelligence Development Studio. Model Designer provides wizards to help you specify data sources and data views, and to generate models.

Note Arabic is not available as a Model Language.

SQL2k5AR21_big.gif

Figure 19: Report Module Wizard

Arabic is not fully supported in the Report Model Wizard in the area of rules that control how metadata is generated from the data source (see the report model generation rules in Figure 19). This affects the report that is generated because Total and Filter appear in English (the selected model language) instead of Arabic. You can manually edit the model generated by the wizard to change these values.

SQL2k5AR22_big.gif

Figure 20: Sample Arabic report

Analysis Services

Online Analytical Processing (OLAP) provides fast and interactive access to aggregated data and the ability to drill down to detail. OLAP enables users to view and interrogate large volumes of data (often millions of rows) by pre-aggregating the information. It puts the data that is needed to make strategic decisions directly into the hands of the decision makers, not only through pre-defined queries and reports, but also because it gives end users the ability to perform their own ad-hoc queries, minimizing user dependence on database developers.

Cc295829.SQL2k5ARFig24(en-US,SQL.90).gif

Figure 21: Arabic support in BI Development Studio

Elements of Multidimensional Models

To fully leverage the SQL Server 2005 Business Intelligence Workbench platform, you must understand the basic elements of multidimensional modeling. The basic elements of a multidimensional cube are: measures, dimensions, and schema.

Cubes

A cube is a multidimensional database. An OLAP cube is built for decision-support queries.

Measures

Measures are similar to key performance indicators that you want to evaluate. To help you determine which of the numbers in the data might be a measure, generally, if a number makes sense when it is aggregated, it is a measure. For example, it is logical to aggregate daily volume to month, quarter, and year. Aggregating zip codes or telephone numbers is not logical; zip codes and telephone numbers are not measures. Typical measures include volume, sales, and cost. Measures and key performance indicators (KPIs) are related but they are not the same. Measures are the numeric data that users would like to analyze.

Dimensions

Dimensions are categories of data analysis. Generally, when a report is requested by something, that something is usually a dimension. For example, in a revenue report by month and by sales region, the two dimensions needed are time and sales region. Typical dimensions include product, time, and region.

Dimensions are arranged in hierarchical levels, with unique positions within each level. For example, a time dimension may have four levels, such as Year, Quarter, Month, and Day. Or the dimension might have only three levels, such as Year, Week, and Day. The values in each level are called members. For example, the years 2004 and 2005 are members of the level Year in the Time dimension.

Schema

The dimensions and measures are physically represented by a star schema. The most basic star schema arranges the dimension tables around a central fact table that contains the measures.

A fact table contains a column for each measure as well as a column for each dimension. Each dimension column has a foreign-key relationship to the related dimension table, and the dimension columns taken together are the key to the fact table.

Determine the measures, dimensions, and schema by using the BI Workbench. Then decide where the data aggregation is to be stored. Historically, there were three basic storage options: Multidimensional OLAP (MOLAP), Relational OLAP (ROLAP), or Hybrid OLAP (HOLAP). The introduction in SQL Server 2005 of the Unified Dimensional Model (UDM), which leverages the best of relational and OLAP cube technologies, allows the designer many more storage options. Unlike SQL Server 2000, in SQL Server the UDM enables you to combine them in the same solution.

SSIS

Microsoft SQL Server 2005 Integration Services (SSIS) is a platform for building high performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing.

Integration Services includes:

·         Graphical tools and wizards for building and debugging packages

·         Tasks for performing workflow functions such as FTP operations, for executing SQL statements, or for sending e-mail messages

·         Data sources and destinations for extracting and loading data

·         Transformations for cleaning, aggregating, merging, and copying data

·         A management service, the Integration Services service, for administering Integration Services

·         Application programming interfaces (APIs) for programming the Integration Services object model

Integration Services replaces Data Transformation Services (DTS), which was first introduced as a component of SQL Server 7.0.

Working with Analysis Services

After you identify the dimensions and measures you wish to analyze, you can use Analysis Services to construct an OLAP cube. Analysis Services has built-in wizards that make the process of creating dimensions fairly easy, especially if you are already familiar with SQL Sever 2000 Analysis Services. SQL Server 2005 Analysis Services has an additional step—you must create a data source view to import your database objects.

MDX

Just as you use SQL to query relational databases, you use MultiDimensional Expressions (MDX) to query a multidimensional cube. MDX is used to create calculated measures that would be too complex or impossible to do in SQL. For example, suppose the VP of Sales wants to know what the average sales price of each product is. Unfortunately, average sales price is not a measure in the Sales cube; however, Store Sales and Sales Count are available. Because you can calculate Average Sales Price by dividing Store Sales by Sales Count, you can calculate the measure by using MDX. Here's the MDX code.

 

   WITH

      MEMBER Measures.[ متوسط سعر البيع] AS

      'Measures.[ مبيعات مخزنه] / Measures.[ عدد المباع]'

   SELECT

      { Measures.[ متوسط سعر البيع] } ON COLUMNS,

   FROM مبيعات

 

Some third-party tools are available that enable users to create calculated measures that may have been intentionally omitted from the original cube design, such as commission or bonus calculations. One of these tools, Proclarity, is now owned by Microsoft and is offered as part of Microsoft Office PerformancePoint™ Server.

Cube Browser

After you create the cube, you need a cube browser to connect to the cube and display the data. Cube browsers usually provide user-friendly tree-structured dimension filters and drag-and-drop interfaces that allow end users to interrogate the cube. You can set up predefined queries or enable ad-hoc querying by allowing users to combine the various measures with dimensions.

Dimensions can have multiple levels (such as year, quarter, and month). Users can mix and match members within the same dimension. Furthermore, some cube browsers enable developers to export a cube browser as a Web part so that they can easily include it in a portal site or digital dashboard.

To create cube browser:

1.     Determine the required dimensions as shown in the following figure.

Cc295829.SQL2k5ARFig25(en-US,SQL.90).gif

Figure 22: Dimensions

2.     Determine the required measures as shown in Figure 23.

Cc295829.SQL2k5ARFig26(en-US,SQL.90).gif

Figure 23: Measures

3.    Use SQL Server 2005 Integration Services (SSIS) to extract data from your source databases, transforming the data as needed and loading the finished data into the cube.

4.    Build the measures, dimensions, and schema. See Figure 24.

Cc295829.SQL2k5ARFig27(en-US,SQL.90).gif

Figure 24: Building measures, dimensions, and the schema

5.    Provide cube browsers for your users so they can select and view reports. If necessary, write MDX queries or use automated tools, such as Excel PivotTables to query the cube.

Conclusion

For more information:

·         SQL Server Web site

·         SQL Server TechCenter

·         SQL Server Developer Center