SQL Server Technical Article
Writer:
Emad Norbert
Published:
February 2008
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.
.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.
.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.
.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.
.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.
.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 '.gif)
'
is less than, equal to, or greater than '.gif)
'.
It also defines whether the collation is accent-sensitive (for example, whether
''
is equal or is not equal to '.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.
.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'))
.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.
.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.
.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.
.png)
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.
.png)
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.
.png)
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.
.png)
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
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.
.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.
.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.
.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.
.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.
.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.
.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.
.png)
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.
.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.
.gif)
Figure 22: Dimensions
2.
Determine the required measures as shown in Figure 23.
.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.
.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