Symptom

Defining the wrong collation during manual installation of any MS SQL Server version might cause functional problems in certain situations. Examples:

• When you try to use any functionality of Drag & Relate, the following error message will be displayed: ‘No matching record found’.
• When you run the Sales\Purchase Analysis Report for Business Partner, the report might not retrieve any results even though it should.
• When you use Crystal reports, it may result in the error ‘Invalid argument provided’.

Solution

Please make sure to choose the collation setting SQL_Latin1_General_CP1_CI_AS when installing MSSQL 2005 / 2008 / 2012 / 2014  / 2016 Server, or start the SAP Business One Installer and follow the wizard for installing the SQL Server once so SQL Server could be found.
In case you are planning to upgrade your existing MSSQL 2005 / 2008 to a higher SQL version, we recommend to change the collation settings prior to the upgrade.

In order to check what the collation is set to in your server, run the following query:

Collation of your Server:
select SERVERPROPERTY (‘collation’)
(This query gets the collation settings of MSSQL Server’s System Database “Master”)
Expected answer : SQL_Latin1_General_CP1_CI_AS

In case you have already installed MSSQL 2005 or 2008 Server with any collation other than SQL_Latin1_General_CP1_CI_AS, please follow the steps below:

Change your server collation to SQL_Latin1_General_CP1_CI_AS.
You can find Microsoft’s instructions for changing a SQL server collation here:
http://msdn2.microsoft.com/en-us/library/ms179254.aspx

Please note that only the collation settings of Database “Master” need to be changed. All SAP Business One Databases must not be changed! These must remain on SQL_Latin1_General_CP850_CI_AS or any other collation that is set by default when creating SAP Business One databases (i.e. Japanese_Unicode_CI_AS for Japan localization databases).

If your SQl Server is on MSSQL 2005 and you need any clarifications with this process, please refer to the attachment of this note.
If your SQL Server is on MSSQL 2008 or higher, follow exactly the description of the attachment, but use the following command (if needed adjust the path to setup.exe and/or the SA Password (=SAPB1Admin in the below example):

“C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\setup.exe” /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=”Builtin\Administrators” /SAPWD=SAPB1Admin /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

Note: The /QUIET switch will not allow you to see any progress. You need to wait until the command prompt appears again within the CMD window.
If you would like to see the progress you might want to use the following command:

“C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\setup.exe” /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=”Builtin\Administrators” /SAPWD=SAPB1Admin /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

In this case your interaction is required by the setup.

If you have Servicepack 1 for MSSQL Server 2005 installed, but the Servicepack 1 is not included into your DVD MSSQL 2005 Server, proceed as follows:

1. Uninstall the Servcicepack 1.
2. Change Collation Settings as per the attachment.
3. Install Servicepack 1 again.

If you have Servicepack 2 for MSSQL Server 2005 installed, but the Servicepack 2 is not included into your DVD MSSQL 2005 Server, proceed as follows:

1. Detach all SAP Business One related databases, including SBO-Common.
2. Move all these database files (*.mdf and *.ldf to a secure location.
3. Uninstall the SQL Server; afterwards we recommend to reboot.
4. Install SQL Server 2005 again, considering the proper collation settings for the Master database.
5. Move the *.mdf and *.ldf files back to the data folder of SQL Server.
6. Attach each DB back to the newly installed SQL Server.
7. Apply SP2 again.

If you have MS SQL 2012 or higher installed, using any collation setting other than SQL_Latin1_General_CP1_CI_AS, or you do not want to carry out the fixing steps on MSSQL 2005 / 2008 Server, we recommend the following:

1. Back up all SAP Business One related databases.
2. Uninstall the MSSQL 20xx version completely.
3. Install it again using the correct collation settings SQL_Latin1_General_CP1_CI_AS.
4. Restore the Databases again.

————————————

Tài nguyên khác để gia tăng kiến thức và hiểu biết của bạn về hệ thống ERP SAP Business One (SAP B1)

• Các hướng dẫn sử dụng hệ thống ERP SAP B1
• Tài liệu bài học chuẩn của ERP SAP B1
• Download các tài nguyên khác của ERP SAP Business One
• Bài trắc nghiệm kiến thức về SAP Business One