Configuring the character set of the MSSQL database

This procedure describes how to configure the character set of your MSSQL database to be compatible with the ISO Latin-1 character set.

To configure the character set of the MSSQL database:

1. Log in to the MSSQL database using regular user credentials.
2. Run the following command from the isql session:

   declare @X varchar(40)

   SELECT @X=CAST(DATABASEPROPERTYEX( db_name() , 'Collation' ) AS VARCHAR)

   SELECT 'Database Collation: ' + description FROM ::fn_helpcollations()
   WHERE name=@X

   SELECT 'Database Code Page ' + CAST(COLLATIONPROPERTY(@X, 'CodePage')
   As VARCHAR)

3. Check the command output to determine whether your attribute values match the following values:
   • Character Set: Latin1-General
   • Sort Order: binary sort
   • Database Code Page: 1252

   If the values match, skip the remaining steps and go to Configuring the MSSQL database client.

4. Shut down all Control-M/EM and Control-M/Server components and applications that might connect to the database server.
5. Export the data by running the following command:

   util -U user -P password -export -type all -file exportFile

6. Drop the database as follows:

   util -U user -P password -clean_database

7. Configure the database server for the ISO-1 character set, code page 1252, and the binary sort order:
   1. Find the database name:

      select db_name()

   2. Verify that the database is empty:

      select count(*) from sysobjects where type='U' and name <>'dtproperties'

   NOTE: If the database is not empty (meaning, the output from the previous query is not equal 0), contact BMC Customer Support.

   1. Verify that no other user is connected to this specific database:

      select count(*) from master..sysprocesses where dbid=DB_ID()

   NOTE: If other users or applications are connected to the database (meaning, the output from the previous query is not 1: you), request that all users disconnect, and shut down all connected applications.

   1. Log on as a system administrator and run the following command:

      Alter database databaseName Collate Latin1_General_BIN

8. Rebuild the database:

   util -U user -P password -build_schema

9. Load the data:

   util -U user -P password -import -replace -type all -file exportFile

   exportFile is the name of the file exported.

10. Restart all components that you shut down.
11. Configure the MSSQL clients as described in Configuring the MSSQL database client.