Let’s paint a scenario for you and see if it sounds familiar:
You are a Sage ERP Accpac 6.0 user and you are looking up a customer’s name in A/R, let’s say Bargain Mart. You type it in but it yields no results. You think that is strange because you know without a doubt Bargain Mart is in your system database. You realized that when you typed in the company name, you used all lower case letters so you try again but use upper case for the B and for the M. When you do that, you get a hit in the search results.
So does it sound familiar? If so, this usually occurs if you have moved from a Pervasive SQL Server database to a Microsoft SQL Server database.
When you set up a database, you are supposed to specify a collation method for your SQL database. In Microsoft SQL Server, the default collation method is SQL_Latin1_General_CP1_CI_AS.
The CI in this collation method is important to note as the CI equals case insensitive. What this means is if you were to do a search in Sage 300 ERP using all lower case, it will return any value, which was upper case and lower case for that search term; it wouldn’t have to be an exact case search.
The problem is Pervasive treated upper case and lower case as two separate records. What happens is when these customers moved over to SQL Server, they encountered a situation where they have units of measure, like ea. and Ea. for one item. However, in SQL Server, you cannot have duplicate units of measure but because Pervasive allowed it, when customers moved over they would get an error message that there was a duplicate key.
When you transfer from a Pervasive database to SQL Server, it sets the collation method as Latin1_General_BIN. When you use this collation method, it is actually case sensitive. So what this means is when you use the Finder in Sage 300 ERP and search for upper case Ea. you would have use the capital. So in our example above, if a user wanted to find Bargain Mart, they had to type in the customer name exactly as it appears in Sage 300 ERP. As you can imagine, a lot of people found this annoying.
So what we did was if you use Microsoft SQL with case-sensitive collation, you can now set up the Finder to show records in search results that do not exactly match the case of text you enter in the Filter field.
To set up the Finder so that search results include all case variations of text you specify:
a. In the Sage 300 ERP Runtime folder, edit a4w.ini.
b.Under [A4WSQLS], add "ForceCaseInsensitiveLike=yes".
You save that and when you launch Sage 300 ERP, even though you are using the Latin1_General_BIN collation method, which if you remember is case sensitive, if you typed in ea it will return ea and capital Ea in the finder.
This fix was added to Sage 300 ERP 2012 and put in Sage Accpac ERP 6.0 Product Update 2.
If you are encountering this issue, apply the tips in this blog post. If you have any questions, post them in our comments section below.
