DOCUMENT:Q194146 16-JAN-2002 [sms] TITLE :SMS: Scandinavian SQL Server Sort Orders Cause Inventory Probs. PRODUCT :Microsoft Systems Management Server PROD/VER::1.2 OPER/SYS: KEYWORDS: ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Systems Management Server version 1.2 ------------------------------------------------------------------------------- SYMPTOMS ======== When using the Systems Management Server Administrator program to view the machine records of a Systems Management Server site, certain records that were previously present in the database may appear to have "disappeared" from the site. Upon closer inspection, you will see that missing data has been combined into another inventory record. By selecting the Previous History Record for the inventory record's Identification property, you may observe values relating to a "missing" machine record. CAUSE ===== Microsoft SQL Server is configured to use character set 850 Multilingual and either sort order Scandinavian dictionary order, case-insensitive, uppercase preference (id 58), or sort order Scandinavian dictionary order, case-insensitive (id 60). By definition, these configurations do not differentiate between the characters "V" and "W". Because the unique identifier for Systems Management Server clients (that is, the SMSID) takes advantage of each of the alphanumeric characters, a conflict arises whenever a site has two clients differentiated only by the single character V or W (for example, "SIT0100V" and "SIT0100W"). In this example, assuming that SIT0100V is written to the database first, when Inventory Data Loader attempts to add SIT0100W, the current record for SIT0100V will be added as a history record of SIT0100W. Therefore, the existing inventory record appears to have disappeared from the site database, until an inventory delta-MIF for SIT0100V is produced. WORKAROUND ========== To work around this problem, set SQL Server to use a sort order that differentiates between these characters. To do this, perform complete the following steps, which apply specifically to SQL Server 6.5: 1. Make a complete backup of your system, according to the steps outlined in Chapter 15 of the Systems Management Server Administrator's Guide. 2. Stop all SMS services running on your Systems Management Server site server(s). You can use either the SMS Service Manager or Control Panel Services to stop these services. 3. Install a separate SQL Server running on another server and configure it for another sort order (that is, a sort order other than Scandinavian dictionary order, case-insensitive with or without uppercase preference). NOTE: Because the Nordic sort orders available for the ISO character set are not affected by this problem, you may also want to change the character set you are using, in addition to changing the sort order. 4. Start SQL Enterprise Manager and register the remote server you installed during Step 3 of this procedure. 5. Prepare the target server with proper device files to hold the database and the transaction log. Create the target database spanning the entire space of these two devices. The size of these devices should be at least as large as your SMS database on your current server. 6. In SQL Enterprise Manager, click Tools menu, click "Database/Object Transfer", and then click to select the server that is currently holding your SMS database as the source server and click to select the SMS database as the source database. 7. Make sure the destination SQL Server is selected as the destination server, select the proper target database that you created during Step 5 of this procedure, and then click "Start Transfer". 8. During the transfer process, you may receive errors and warnings during transfer of some objects. For a resolution to these warnings, consult the following article in the Microsoft Knowledge Base: For additional information about resolutions to these warnings, click the article number below to view the article in the Microsoft Knowledge Base: Q189588 SMS: SQL Transfer Manager Errors When Moving SMS Database 9. Start Systems Management Server Setup. Click Operations, click "SMS Database", type a new SQL Server name (and possibly SQL Server login ID and password), and then click OK. 10. Systems Management Server Setup will now reset the system according to the selected changes. When it is done, click Close and then click Exit. The system will now use the new SQL Server and database instead. MORE INFORMATION ================ If you want to maintain the old SQL Server to store the SMS database, you can rebuild Master.dat (to do this, use the Rebuild Master Database option in SQL Setup on the original SQL Server, selecting a new sort order that does differentiate between the letters V and W). However, before doing so, you should determine whether this SQL Server is in use for other databases as well, because this step will purge all existing databases from Master.dat. If it is being used for other databases, you should carefully evaluate what kind of impact this configuration change may have for these databases. If you come to the conclusion that these databases can be successfully transferred to another temporary SQL Server running on a different sort order, you can pursue rebuilding Master.dat in order to change the running sort order. After completing this step, go through the procedure described in the WORKAROUND section of this article to move the SMS database back to the original server again. Also transfer all other databases you temporarily moved over to another server back to the original SQL server. NOTE: It is highly recommended that you go through these steps before you upgrade to Systems Management Server 2.0. Additional query words: prodsms localize Scandinavia resync multi lingual SEM Danish Finnish Norwegian Swedish ====================================================================== Keywords : Technology : kbSMSSearch kbSMS120 Version : :1.2 Issue type : kbbug Solution Type : kbpending ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2002.