Database collation importance - HIGHLY TECHNICAL HELP NEEDED

John, you definitely DO NOT want to change the other database to SQL_Latin1_General_CP1_C1_AS.
The reason why Laton1_General_BIN is used is because, amongst other things, it is case-sensitive. In the case of SQL_Latin1_General_CP1_C1_AS, the "CI" means it's case insensitive. Where this causes problems in particular (I've seen this before at some clients and have figure it out, and rectify it) is that you start getting all kinds of strange GEN and LOGAN errors for fields that are indexed and have duplicate values if looked at without case (i.e. Gbi is GBI is GBi, etc.) That is a real-life example, when a client moved to LSF9, there GEN database was set up incorrectly, and they had errors galore--for example srgen would no longer run because "duplicate" key numbers were in the database. Changing the collation on the database is not an easy task (btw, "changing" it on the database doesn't actually "fix" existing data).

Thanks for your help, John. I figured as much but I was hoping there was a way around it. Looks like we have quite a task ahead of us in changing the collation for our application database. The ACTRANS table alone took 8 hours to migrate from one database to another.

Curious--roughly how many rows in ACTRANS? Also, what apps version are you running? If you're still on 8.0.x or 8.1, you can combine this with apps upgrade, which is what I did with one client who had a huge database and needed to fix collation.

We are on apps 8.1, MSP6.  As much as I would like to upgrade the Apps, the boss prefers we do that upgrade later this year (along with going from LAUA to Lawson Security).

Here's a breakdown of our tables, top 10 largest: (in row count)

ACGLACCT - 109,549,863
ACMXVAL - 74,644,904
GLTRANS - 52,916,396
ACTRANS - 52,243,844
GTMXVALUE - 37,445,824
ACOVERRRIDE - 33,669,456
PRDISTRIB - 17,065,374
PRTIME - 11,496,293
ACBRDNTRAN - 10,789,907
ACMASTER - 10,558,615

Depressing, isn't it?  I should have listened to my mother and become a truck driver....

Hmm. Looks like you're not utilizing new AC structure features in 8.1 :)

You can definitely do it as part of the apps upgrade process. You should probably change your database default collation in the meantime so that new databases (assuming this SQL server instance only supports Lawson databases) are created with Latin1_General_BIN. Since most Lawson tables/forms uppercase the key values in a table (an exception is requesters), there is only a slight risk of key collisions, and if you haven't run into it since your install in 2004, you're probably safe to leave it as is until you do the apps upgrade. It can be done, however, but requires doing it table-by-table, column-by-column, using "ALTER TABLE XX ALTER COLUMN YY COLLATION " statements.

John - Since our LOGAN and GEN databases are using the correct collation (Latin1_General_Bin) and our application product line (PROD1) is not, what potential problems might we run into going forward?  Is there a potential for any kind of data corruption because of the differences in collation?  I ask because I don't know what table joins go on in the background between LOGAN, GEN, and PROD1.

As I mentioned earlier, we plan to upgrade to apps version 9 later this year.  Will our application product line need to be using the correct collation before we embark on that upgrade project or can we do the collation change during the application upgrade?

On the surface of this whole mess, you might think just changing the collation now would be the right thing to do.  I agree that we should be doing it now and get our SQL-Server 2005 platform "speaking the right language."  It's the application outage this would require that's preventing us from moving forward.

I'd appreciate any further suggestions you might have to offer.

There are no direct table joins between the apps product line and LOGAN and GEN. Where you may have difficulty is with forms/tables that allow mixed cases in index fields. For example, I think RQ04/requester allows you to define a requester in upper or lower case, so you could have JIM and Jim and jim. Since you have your apps product line defined with case-insensitive, this would be a "key collision".

Since you have been running since 2004 with the incorrect collation, chances are good that you will not run into any issues between now and your upgrade. To analyze any potential issues would require creating a SQL script to analyze all table columns that are included in indexes, and looking for duplicates in their upper-cased values, e.g. something like (off the top of my head):

SELECT UPPER(REQUESTER), COUNT(UPPER(REQUESTER)) FROM REQUESTER GROUP BY REQUESTER HAVING COUNT(UPPER(REQUESTER))>1

When you do the apps upgrade, assuming you follow the standard procedure of copying PROD to PROD8, you would define both PROD8 and PROD9 as case-sensitive, then use sqldbcopy to copy data from PROD to PROD8 (don't just copy the physical database and reattach). This will put all of the data in PROD8 into the correct collation.