Configuring the MSSQL database

This task determines whether your MSSQL database is using a character set that is compatible with the ISO Latin-1 character set.

To configure the MSSQL database:

1. Log on as a regular user to the database that you want to configure.
2. To determine which character set, code page, and sort order are currently in use, 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)

   Check the command output to determine whether your attribute values match the values in the table:

   Attribute	Value
   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.

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

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

5. Drop the database:

   util -U user -P password -clean_database

6. 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'

   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()

   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

7. Rebuild the database:

   util -U user -P password -build_schema

8. Load the data:

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

   exportFile is the name of the file exported.

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