Arabic Language Support in Microsoft SQL Server 2000
Microsoft® SQL Server™ 2000
Summary: Microsoft SQL Server 2000 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 setup, administration, replication, data transformation, and more. (39 printed pages)
Important Concepts for Understanding Arabic Language Support
Arabic Code Page 1256
Domain Name System
Character Data Types
Installing SQL Server 2000 with Arabic Language Support
Creating a New Installation of SQL Server
Upgrading to SQL Server 2000
Creating and Maintaining Databases
SQL Server Enterprise Manager
SQL Query Analyzer
SQL Server Administration
Authentication Modes and User Names
Web Assistant Wizard
Automating Administrative Tasks
Backing Up and Restoring
Creating Full-Text Index
Using Transact-SQL to Set the Default Full-Text Language
Querying Full-Text Indexes with Arabic Characters
XML and Internet Support
Creating IIS Virtual Directories
Using URL Queries
Using URL Queries with Mapping Schemas
Using the OpenXML Function
Data Transformation Services
Code Page, Collation, and Non-Unicode Data Issues
Microsoft® SQL Server™ 2000 was designed to support all the languages supported by the Microsoft Windows® 2000 operating system, including the Arabic language. In this article you will learn more details about this Arabic language support, including information about components, functions, and how the server supports Arabic data in Unicode and non-Unicode formats.
To make best use of the Arabic language support in SQL Server 2000, the following concepts should be clear:
- Reading order
- Arabic code page 1256
- Domain Name System
- Data types
- Hijri dates
Unicode represents the characters of a language with two bytes rather than one. Non-Unicode, 8-bit ASCII is not capable of representing all of the combinations of letters and diacritical marks that are used just with the Roman alphabet. Unicode, by contrast, enables a single character set to represent almost all of the written languages of the world, including Arabic.
For more information about Unicode, see the Unicode Web site at http://www.unicode.org/, the Microsoft Global Software Development Web site, and the International Features of SQL Server 2000 white paper.
Reading order describes the order in which words are displayed in mixed text. Reading order pertains to the word order, not to the order of the entered characters. When Arabic is the keyboard language, new characters will always flow right to left (RTL). Conversely, when Latin is the keyboard language, characters flow left to right (LTR).
Reading order becomes important for mixed text situations where text strings contain words in both Middle Eastern and Latin languages. This concept is illustrated in the following examples using Arabic and English text:
Figure 1. Reading order
- The first two lines 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.
Microsoft Windows 2000 uses code page 1256 for the Arabic language. Figure 2 shows the code page.
Figure 2. Code page 1256
Arabic code page 1256 is used by earlier versions of SQL Server; however, for SQL Server 2000 and later versions, Unicode is recommended.
Currently there is no Internet standard for non-ASCII domain names (Arabic, Chinese, Japanese, and so on); however, the Internet Engineering Task Force (IETF) group is expected to introduce a standard soon.
Microsoft supports the IETF effort to define the standard and will implement it as soon as it has passed. Until the standard is defined, new software developed at Microsoft will put raw Universal Transformation Format 8 (UTF-8) encoded characters online. UTF-8 encoding allows Unicode to be used in a convenient and backward compatible way in ASCII environments. This will allow customers to use non-ASCII characters in their intranet domains.
Because it uses UTF-8, Windows 2000 supports the use of Arabic characters in the Domain Name System (DNS). Users may therefore use Arabic domain names.
Users specify the domain name when installing Microsoft Active Directory® in Windows 2000 Server. A warning message informs the user the Arabic letters do not conform to Internet DNS standard specifications. They do, however, conform to Microsoft specifications.
The physical storage of character strings in SQL Server 2000 is controlled by collations. The term collation refers to a set of rules that determine how character data is sorted.
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.
A sort order specifies the rules used by SQL Server to interpret, collate, compare, and present character data. For example, a sort order defines whether the Arabic character '' is less than, equal to, or greater than ''. It also defines whether the collation is accent-sensitive (for example, whether '' is equal or is not equal to '').
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 will be sorted based on the numeric value represented by their 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 SQL Server 2000, you do not need to separately specify code page and sort order for character (ASCII) data, and the collation for Unicode data. Instead, you specify the collation name and sorting rules to use, as discussed below.
Unlike earlier versions of SQL Server, which supported only one level of code page for the server selected within the installation, SQL Server 2000 can specify a collation for character data types at server, database, and column levels. This feature gives the user the ability to handle multinational applications easily; for example, you can define one database with French collation and another database with Arabic collation, both in an instance of SQL Server with English collation. Even within the same table, you can have different columns with different collations.
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 takes the server collation by default), and the column collation when creating a table (if you do not specify a collation, the column takes the database collation by default).
Note the following Arabic-specific information about collations:
- For insert and update operations in applications, the effective collation level is database collation. Therefore, to insert or update Arabic data with non-Unicode data types, you must set the database collation to Arabic. It is important to set database collation level to Arabic even if your column collation level is set to Arabic, because data may be corrupted when inserting or updating data. Use the "N" prefix to ignore database collation level.
- Selecting accent sensitive with Arabic collations affects the sort order when using the following:
There are two groups of SQL Server 2000 collations: Windows collations and SQL collations. Each group is discussed immediately below.
To set collations for a table column, right-click the table and click Design Table. In the Design Table window, click a column name, and under the Columns tab, select the Collation cell and click the adjacent ellipsis button to display and set the collation settings for that column name.
Note Database-wide collation settings can only be specified at the time a database is created.
Windows collations define rules for storing character data based on the rules defined for an 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 sort order, click either dictionary sort or binary sort. Dictionary sort order offers four additional options; however, only accent sensitivity affects sort order in Arabic. Binary sort is always case sensitive and accent sensitive. Figure 3 shows how to specify an Arabic Windows collation for a table column.
Figure 3. Specifying an Arabic Windows collation for a table column
SQL collations are provided for compatibility with sort orders in earlier versions of SQL Server. If you are not working with an earlier version of SQL Server, use Windows collations instead.
For the Arabic language, there are three choices:
- Arabic_BIN (binary order)
- SQL_Latin1_General_Cp1256_CS_AS (case sensitive, accent sensitive)
- SQL_Latin1_General_Cp1256_CI_AS (case insensitive, accent insensitive)
SQL Server 2000 supports both non-Unicode character data types and Unicode character data types.
Non-Unicode data types
The non-Unicode character data types are char, varchar, and text. These data types use the character representation schema in single- or double-byte code pages. (Arabic code page 1256 uses single-byte representation.) To use non-Unicode data types, the system collation has to be Arabic, or the data will be corrupted.
Unicode data types
The Unicode data types are nchar, nvarchar, 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. Using Unicode data types in your applications is therefore recommended.
Using the "N" prefix with Unicode data types
When using Unicode data types, you can prevent corruption of your Arabic data in English collations by prefixing your Arabic string with "N". Adding the "N" prefix aids Unicode data types in applications that use Microsoft® Visual Basic® or Active Server Pages (ASP), as well as inside stored procedures. If the default database collation is not Arabic, your data will be corrupted if "N" is omitted. The following code illustrates use of the "N" prefix:
- UPDATE TableName SET ColumnName = N'Arabic Text' WHERE id = 1000 INSERT INTO TableName (ColumnName) values(N'Arabic Text')
For an illustration of the relationship between collations and data types, see Code Page, Collation, and Non-Unicode Data Issues.
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. There are two CONVERT styles that are available in SQL Server 2000 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.
For example, to convert a Gregorian date to Hijri format with Transact-SQL, you would use syntax such as the following:
SELECT CONVERT(nchar, GETDATE(), 131)
This query will return a string such as the following in its result set:
The reverse operation is also possible. The following syntax would be used to convert a Hijri date to Gregorian format:
SELECT CONVERT(datetime, ' 7/05/1421 12:14:35:727PM', 131)
This query would convert the date to SQL Server datetime type, which in SQL Query Analyzer would appear as follows:
Note that SQL Server does not use the regional calendar settings included with Windows 2000, and any adjustment made in that regional setting for the Hijri date does not affect the conversion method of SQL Server.
Also, SQL Server date functions such as DateName, DatePart, and DateAdd work with the Gregorian calendar, so you must make your conversions programmatically during development.
To support a full Arabic environment when creating a new installation of SQL Server 2000 or upgrading from an earlier version, set the default locale to Arabic. In Control Panel, double-click Regional Options, click the General tab, and select Arabic in Language settings for the system. Click OK.
Setup provides three options for running the installation program:
- Create a new instance of SQL Server or install the client tools.
- Upgrade, remove, or add components to an existing instance of SQL Server.
- Set advanced options.
The first two options are discussed below.
This section describes basic options for a new installation of SQL Server 2000. It notes the available Arabic support for each step of the installation.
Computer Name page
The Computer Name window offers the choice of installing SQL Server 2000 on your local computer, on a remote computer, or on a virtual server. All options for installing and upgrading are available on the local computer.
Installation Selection page
For new installations, on the Installation Selection window, click Create a new instance of SQL Server, or install Client Tools.
User Information page
The User Information window prompts you to supply your name and company name. These fields support Arabic characters.
Installation Definition page
Use the Installation Definition page to select the components to include in this installation of SQL Server 2000. If you select Client Tools Only or Connectivity Only, Setup proceeds and no additional choices are required, unless you select components when installing client tools. If you select Server and Client Tools, additional setup screens appear.
Instance Name page
Use the Instance Name page to add and maintain instances of SQL Server 2000.
When Default is selected, a default instance of SQL Server 2000 is installed. The name of the default instance is the same as the name of the computer.
Note Only one installation of any version of SQL Server can be the default instance at any one time.
If Default is cleared, you can enter a name for the instance; however, do not set the name in Arabic. Using Arabic characters causes an error when the properties of SQL Server and SQL Server Agent services are accessed.
Setup Type page
When you install from any version of SQL Server other than Developer Edition, Setup offers three installation types in the Setup Type page: Typical, Minimum, and Custom. In addition, you can modify the installation location for both program and data files in this dialog box.
When choosing an installation location, you can select Arabic directory names.
Select Components page
If you choose a Custom option setup type, Setup next displays the Select Components page, where you can choose components and subcomponents to install. You can also choose to reinstall them if you did not set them up initially.
Collation Settings page in Custom Setup
Use the Collation Settings page to modify default collation settings. Select Windows Locale to match collation settings in instances of SQL Server 2000. Select SQL Collations to match settings that are compatible with the sort orders in earlier versions of SQL Server. The Windows and SQL Server 2000 collations include the Arabic collations. For more information, see Collations earlier in this article.
Note The Collation Settings page is only available if you select Custom on the Setup Type page.
Services Accounts page
Use the Services Accounts page to assign an account to the SQL Server service (MSSQLServer) and the SQL Server Agent service (SQLServerAgent). Either the local system or a domain user account may be used; you can use the same account for each service. The default is to use the same account for each service.
Domain user accounts support Arabic characters for both username and password.
Note If logon fails with this password, run Services in Windows and retype the password in the Log On tab of the Properties dialog box.
Authentication Mode page
Use the Authentication Mode page to choose Windows Authentication Mode or Mixed Mode (Windows Authentication and SQL Server Authentication) for logging on. If you choose Mixed Mode, you must choose a password for the system administrator (sa).
SQL Server 2000 supports Arabic usernames and passwords.
If Setup detects an existing installation of SQL Server 7.0, it offers you the option to upgrade. If you choose to upgrade, all SQL Server 7.0 program files are upgraded, and all data stored in SQL Server 7.0 databases is preserved. In addition, SQL Server Books Online for SQL Server 7.0 remains on your computer.
The upgrade is straightforward; the SQL Server 7.0 instance automatically becomes the default instance of SQL Server 2000.
Note When upgrading, collation selection is not available because the upgrade adopts the previous SQL Server 7.0 collation. The new default instance takes the previous collation with the same sort order.
Upgrading databases using Copy Database Wizard
You can also perform an online upgrade of databases and associated metadata. Using Copy Database Wizard, you can move or copy a database from SQL Server 7.0 to an instance of SQL Server 2000 without having to shut down any servers in the process.
Note With SQL Server 2000, the sort order and code page of the database being copied is no longer a concern. SQL Server now handles multiple collations, so a database with Arabic collation will be copied even to an instance of SQL Server 2000 with English collation.
Upgrading databases using SQL Server Upgrade Wizard
You can use SQL Server Upgrade Wizard to convert data from SQL Server version 6.5 to the formats for SQL Server 2000. The wizard upgrades all of your databases, preserving all catalog data, objects, and user data. It also transfers replication settings, SQL Executive settings, and most of the SQL Server 6.5 configuration options. The sort order and Arabic code page of the SQL Server 6.5 databases are copied to the SQL Server 2000 databases.
Note To run the SQL Server Upgrade Wizard, you must have a default instance of SQL Server 2000 already installed on your computer.
All SQL Server objects support Arabic characters in the object name. You can manipulate the objects and administer SQL Server using SQL Server Enterprise Manager.
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 2000 and SQL Server 2000 can create a full Arabic environment in the database and its components.
The user can create tables with Arabic names, columns, relations, indexes, and check constraints. Also, the user can use Arabic names inside queries using SQL Query Analyzer, write stored procedures and user-defined functions in Arabic, and use Arabic in applications such as ASP pages. Figure 4 shows the properties for a table with an Arabic name as displayed in SQL Server Enterprise Manager.
Figure 4. Properties of a sample table with an Arabic name
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 figures 5 and 6.
Figure 5. Stored procedure with Arabic name
Figure 6. Stored procedure with Arabic name (click picture to see larger image)
Users can create a view with an Arabic name. The view can connect to tables with Arabic names and retrieve the data.
Figure 7. Sample query that creates a view (click picture to see larger image)
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 Enterprise Manager.
To view the text of the query that creates the view
- In SQL Server Enterprise Manager, navigate to the view for which you want to see the Transact-SQL query.
- 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 Server Enterprise Manager is the primary administrative tool for SQL Server. SQL Server Enterprise Manager provides a Microsoft Management Console (MMC) compliant 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 Enterprise Manager 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 2000.
Figure 8. Hijri date display in SQL Server Enterprise Manager (click picture to see larger image)
SQL Query Analyzer is used for interactively designing and testing Transact-SQL statements, batches, and scripts. SQL Query Analyzer can be called from SQL Server Enterprise Manager.
Like SQL Server Enterprise Manager, SQL Query Analyzer supports Arabic characters but does not support RTL reading order.
Figure 9. SQL Query Analyzer (click picture to see larger image)
Arabic support is included for various administrative tasks in SQL Server 2000. 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.
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 2000 user account. Mixed Mode Authentication allows users to connect to an instance of SQL Server using either Windows Authentication or SQL Server Authentication. Users who connect through a Windows 2000 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. For SQL Server Authentication, the user can create Arabic user names and passwords, and for Windows Authentication, the user can select from Windows Arabic accounts that are already supported within Windows 2000.
You can use the Web Assistant Wizard to generate standard HTML files from SQL Server data. The Web Assistant Wizard generates HTML files by using Transact-SQL queries, stored procedures, and extended stored procedures. You can use the wizard to generate an HTML file on a one-time basis or as a regularly scheduled SQL Server task. You can also update an HTML file using a trigger.
The Web Assistant Wizard enables you to connect to a full Arabic database with Arabic object names and retrieve Arabic data.
For Arabic language support, there are two relevant choices in the Format the Web Page window of the wizard. For data that is entirely in Arabic, select Arabic (Windows) from the Use Character Set list to:
- Save the HTML file using ANSI encoding with Arabic Windows code page 1256.
- Add the following tag to the HTML:
<META content="text/html; charset="windows-1256" http-equiv=Content-Type>. This tag instructs the browser to use the Arabic code page.
Figure 10. Using the Web Assistant Wizard to generate Arabic Web pages
On the other hand, when dealing with multilingual data through the Internet, UTF-8 encoding is recommended. If you use multilingual data, select UTF-8 from the Use Character Set list to:
- Save the HTML file using UTF-8 encoding.
- Add the following tag to the HTML file:
<META content="text/html; charset="UTF-8" http-equiv=Content-Type>. This tag instructs the browser to use UTF-8 encoding.
If you need to use RTL reading order in your HTML file, edit the HTML file to add the following to the body tag:
<Body Dir="RTL">. You can also use templates to accomplish this. For more information, see the following section.
A template file is any HTML file with the marker
<%insert_data_here%>, which indicates where the query results should be inserted.
In the template, you can insert your title and header and add the RTL direction and encoding tags. Figure 11 shows how to specify a hypothetical template to use in the Web Assistant Wizard.
Figure 11. Specifying a Web page template in the Web Assistant Wizard
The webtemp.htm template file contains the following HTML:
<html> <HEAD> <META CONTENT=TEXT/HTML; CHARSET=WINDOWS-1256_ HTTP-EQUIV=CONTENT-TYPE> </HEAD> <BODY DIR=RTL> <%insert_data_here%> </body> </html>
The actual data in the resulting HTML file will replace the placeholder in the template (
<%insert_data_here%>). You can add whatever you want in the template and use it for multiple files created by Web Assistant Wizard.
When using a template with the wizard, select the option in the Use Character Set list that matches the encoding specified in your template.
SQL Server 2000 provides tools for managing server messages within SQL Server Enterprise Manager. To create and send server messages in Arabic requires an understanding of 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 subsections that follow.
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 for more information.
- Always write to Windows eventlog. 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 with the same error number. See Figure 12.
Figure 12. Arabic SQL Server message (click picture to see larger image)
Sending messages using RAISERROR
You can send SQL Server messages, including Arabic messages, in one of two ways:
- By using the RAISERROR statement
- By using alerts
Using the RAISERROR statement, you can raise your Arabic messages when needed, or when you have specific alerts. For the following example, we will use the Arabic message created before with the number 50002, and with 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 article.
This section explains how to set the server default message language and the user language using SQL Server Enterprise Manager. Setting the default to Arabic allows you to send Arabic versions of messages you create as user-defined messages.
To specify Arabic as the server default language using SQL Server Enterprise Manager
To set the Arabic language to be the default for server messages, follow these steps:
- In SQL Server Enterprise Manager, right-click the server you are working with, and then click Properties.
- On the Server Settings tab, select Arabic from the Default Language for User list.
With SQL Server 2000, 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
To specify Arabic as the language for a given login, do this:
- In the SQL Server Login Properties dialog box, click on the General tab, and select Arabic from the Language list.
Automated administration is the programmed response to a predictable administrative responsibility or server event. By automating administration, you can free time to perform more complex administrative tasks. Automated administration is configured using SQL Server Agent.
For example, if you want to back up all the company servers every weekday after hours, you can create a job to perform this task and schedule the job to run at the required time. If the job encounters a problem, SQL Server Agent can record the event and page you.
Automatic administration components
The three main components of automatic administration are operators, jobs, and alerts.
An operator is an individual 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:
- Pager (through e-mail)
- Network terminal messages
You can create an Arabic operator name and integrate your automation of jobs and alerts (as shown below) with support of Arabic messages and component names.
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, as illustrated in Figure 13.
Figure 13. Job notification with Arabic characters (click picture to see larger image)
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.
Also, 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, as shown in Figure 14.
Figure 14. Creating an alert based on a user-defined message
Assign the alert to an operator, and specify the way to alert the operator. In Figure 15 below, the second operator will be notified by e-mail and by a network message when the alert is triggered.
Figure 15. Specifying the recipient of a user-defined alert
Run the following command from SQL Query Analyzer, and the operator specified will receive the notification.
The following network message will appear on the operator's computer screen.
Figure 16. Network notification containing Arabic characters
As configured in Figure 15, the same message will also appear in the operator's Inbox.
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 domain user account used to log in 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 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:
Note SQL Server also uses the SQLServerAgent service to send e-mail. SQLServerAgent does not use SQL Mail to send e-mail.
For more information, see "SQL Mail" in SQL Server Books Online.
The backup and restore architecture of SQL Server 2000 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
With earlier versions of SQL Server, the sort order and code page of the database being copied were important. Because SQL Server 2000 supports multiple collations, however, the sort order and code page of the database no longer relevant.
When you restore a database with SQL Server 2000, 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 with 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 with Arabic collation back up and restore transparently under SQL Server 2000.
Nonetheless, when backing up or restoring a database using 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 version 7.0 database backup to a SQL Server 2000 database.
- You cannot restore a SQL Server version 6.5 database backup to a SQL Server 2000 database.
- You cannot restore a SQL Server 2000 database backup to a SQL Server 7.0 or SQL Server 6.5 database.
SQL Server 2000 provides a rich text-data retrieval system and an enhanced full-text search service that enables you to perform a linguistic search of character data in tables enabled for full-text search. A linguistic search operates on words and phrases. The linguistic search is not supported for Arabic, but other parts of full-text search are.
To accomplish a full-text search, you must create a full-text index on the specific column you wish to search. To index a column in a table you are viewing, use the Full-Text Indexing Wizard. In the Select Table Columns page, find the column you wish to index and, in the Language for Word Breaker list, select Neutral. See Figure 17. Choosing 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.
Figure 17. Indexing Arabic databases using the Full-Text Indexing Wizard
To set the default language for a full-text index to neutral, use the sp_configure stored procedure and set the default full-text language option to 0, using the code sample below:
USE master EXEC sp_configure 'default full-text language', '0' RECONFIGURE EXEC sp_configure
There are four predicates that you can use to search data that has been full-text indexed. The following examples show how to use the most common predicate, CONTAINS, with Arabic characters.
Using CONTAINS with a single word
This example finds all names that contain the word "":
Using CONTAINS with a phrase
This example finds all names that contain the word "" or "":
Using CONTAINS with a prefixed wildcard string
This example returns all names with at least one word starting with the "" character:
Using CONTAINS with a proximity operator
This example returns all names that have the word "" near the word "":
Using CONTAINS with a variable
This example uses a variable instead of a specific search term:
SQL Server 2000 introduces new features that support XML functionality. The combination of these features makes SQL Server 2000 an XML-enabled database server. These new features include:
- The ability to access SQL Server using HTTP.
- Support for XML-Data Reduced Language (XDR) schemas and the ability to specify XPath queries against these schemas.
- The ability to retrieve and write XML data.
- Enhancements to the SQL Server 2000 OLE DB Provider (SQLOLEDB) that allow XML documents to be set as command text and to return result sets as a stream.
Before you can access SQL Server 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 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.
Figure 18. IIS virtual directory (click picture to see larger image)
After configuring the virtual directory, you can use URL queries with 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.
Figure 19. URL queries with Arabic parameters
To support Arabic characters within the XML template, you must specify Arabic or Unicode encoding 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 example shows an XML template with a simple SELECT query.
Figure 20 shows the result when accessing the template from the URL.
Figure 20. Template example result
You can use XDR files containing a mapping schema with annotated Arabic names. Doing so enables you to connect to Arabic database objects and retrieve Arabic data. The following example retrieves a single row using XPath through a URL:
Figure 21 shows the result.
Figure 21. Result of URL query with mapping schema
The OpenXML function in SQL Server 2000 is an extension to Transact-SQL that allows stored procedures to process XML and generate rowsets from the data for use by Transact-SQL statements.
To use stored procedures to process XML
- Execute the sp_xml_preparedocument stored procedure to prepare the XML document for use by Transact-SQL statements.
- Use the OPENXML-generated rowset in one or more queries.
- Execute sp_xml_removedocument to remove the prepared XML document from memory.
To add Arabic characters within XML, you must add Arabic encoding or use the "N" prefix for Unicode support:
- exec sp_xml_preparedocument @h OUTPUT, '<?xml version="1.0" encoding="windows-1256"?>< [XMLARABIC] /> ' - exec sp_xml_preparedocument @h OUTPUT, N'<root>< [XMLARABIC] /> </root>'
This example shows rowset retrieval using OpenXML:
Figure 22. Query using the OpenXML function
SQL Server 2000 replication allows you to copy, distribute, and modify data across your enterprise. SQL Server 2000 includes several 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 2000 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 using different character sets, SQL Server 2000 does not convert any of the replicated data and may mistranslate the data as it is replicated, because it is impossible to map all characters between character sets.
To guarantee successful data replication, therefore, servers are best configured using the same Arabic code pages and comparison styles.
- Generally, if you have an environment where you have different character sets including the Arabic character set, you should 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.
SQL Server 2000 Data Transformation Services (DTS) provide a set of graphical tools and programmable objects that let you extract, transform, and consolidate data from disparate sources into one or more destinations.
DTS offers the following connections:
- A data source connection. These are connections to standard databases such as Microsoft SQL Server 2000, Microsoft Access 2000, Oracle, dBase, or Paradox; ODBC data sources; Microsoft Excel 2000 spreadsheet data; HTML sources; and other OLE DB providers.
- A text file connection. DTS provides additional support for text files. When specifying a text file connection, you also specify the format of the file. For example, you should specify:
- Whether a text file is in delimited or fixed field format.
- Whether the text file is in Unicode or ANSI format.
- The row delimiter and column delimiter if the text file is in fixed-field format.
- The text qualifier.
- Whether the first row contains column names.
- A data link connection. These are connections in which an intermediate file outside of SQL Server stores the connection string.
When DTS is used to copy data between SQL Server databases with different code pages and collations, data may be lost or incorrectly translated. If the code pages used for a source and destination column match, no data loss occurs in non-Unicode columns. When data is copied between non-Unicode columns, and the source and destination code pages do not match, loss of data can result.
To avoid translation issues, store international data in Unicode. After it is converted to Unicode, the data can be easily transferred in any code page or collation, including Arabic. There is no data loss or incorrect translation to any SQL Server 2000 or SQL Server 7.0 database.
Import text file example
This example illustrates the behavior of DTS with different collations. The example imports a text file using the DTS Import/Export Wizard. The text file is imported into SQL Server 2000 using different collations with both Unicode and non-Unicode data types.
- Start with the following semicolon-delimited text containing four fields with the same Arabic data, '', as shown in Figure 23.
Figure 23. Arabic text used in example
- Create the CollTst table with the settings listed below:
Column Data type Collation coll1 nvarchar (Unicode) Arabic coll2 nvarchar(Unicode) French coll3 varchar (non-Unicode) Arabic coll4 varchar (non-Unicode) French
- Use the DTS Import/Export Wizard to import the text file into the CollTst table.
- Select Semicolon on the Specify Column Delimiter page.
- Select Append Rows to Destination Table in the Column Mappings and Transformations page.
- Use SQL Query Analyzer to retrieve the data that was imported from the text file into SQL Server 2000, as shown in Figure 24 below:
Figure 24. Results of importing data with various data types and collations
As can be seen from the example, when importing data Unicode and non-Unicode data types, DTS works as follows:
- With Unicode data types (coll1, coll2), there is no conversion between different code pages, and the column collation is not considered through transformation. Notice that in coll2, with French collation, the data copied successfully.
- With non-Unicode data types, consider using columns with Arabic collation or your Arabic data will be corrupted, as happened to the data in coll4.
Microsoft SQL Server™ 2000 was designed to support all the languages supported by the Microsoft Windows® 2000 operating system, including the Arabic language. In this article you learned about Arabic language support for SQL Server 2000, including information about Arabic language code page, collations, and server support for Arabic data in Unicode and non-Unicode formats, as well as feature-specific information concerning setup, administration, replication, data transformation, and more. If you need more information on any of the subjects, see the relevant topics in SQL Server Books Online.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place or event is intended or should be inferred.
© 2002 Microsoft Corporation. All rights reserved.
Microsoft, Active Directory, Visual Basic, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.