/********************************************************************************
 *
 * Program Name:	fix_glcontrol_attachments.sql
 *
 * Program Description: Suppose your run your database with a case INSENSITIVE
 *			collation and Lawson is expecting case SENSITIVE. For
 *			everything but Lawson, case SENSITIVE is a huge pain in
 *			the ass. Eventually you will be burned by the attachment
 *			tables, whose L_INDEX value starts at zzzz (lowercase 'z'
 *			has the highest ASCII value of any English letter) and
 *			descends in ASCII order to AAAA ('A' has an ASCII value 
 *			of 65). The only comprimise is to change the collation 
 *			settings on just the attachment columns, which is what
 *			was done here for GLCONTROL. The indicator is given by
 *			the examine log from the job below:
 *
 *#################################################################################
 * --------------------------------------------------------------------------------
 * User: XXXXXXXX                             Job: GL199           Queue: JOBQUEUE1
 * --------------------------------------------------------------------------------
 * BEGIN:  Job Submitted:  Thu Mar 18 18:16:25 2004
 * 
 *   Step  1:  GL199 Started. . . . . .:  Thu Mar 18 18:16:25 2004
 *             Token Command. . . . . .:  D:\LAW\APPS\prod\obj\GL199.gnt
 *             Executable Command . . .:  C:\NetExpress\BASE\Bin\run.exe D:\LAW\APP
 *             Process ID . . . . . . .:  7760
 *             Program Messages:
 * ** Creating .prt and .dtl files
 * Closing company 0001
 * File PROD/L_HGLC Index L_HGLCSET1.
 * StoreDBRec error is Record not locked (8).
 *  L_Index        = zzsZ
 *  AtchNbr        = zz
 *             Elapsed Time . . . . . .:  00:00:14
 * 
 *   ERROR:    Stopped On Exit 1.
 *   Elapsed Time:  00:00:15
 * 
 * END:    Job Ended:  Thu Mar 18 18:16:40 2004
 *#################################################################################
 *
 *			This record is trying to write to L_HGLC, but there is
 *			already a record for zzsz (which, in a case insensitive
 *			system is the same a zzsZ) and throws an error. 
 *
 * Usage:		(1) Drop Indexes from LID using 'bldXXXddl'
 *			(2) Run SQL Script
 *			(3) Rebuild Indexes from LID
 *
 * Author:              John Eisenschmidt <jeisenschmidt@feldinc.com>
 *
 * Date:                2004-03-18 - Created
 *
 ********************************************************************************/

/*
 Note on collation settings:
	Latin1_General_CI_AI is our default here, which means
		"Latin1-General, 
		case-insensitive, 
		accent-insensitive, 
		kanatype-insensitive, 
		width-insensitive"
	We are switching to Latin1_General_BIN, which means
		"Latin1-General, 
		binary sort"
	In a binary sort, the letter 'Z' comes before 'z' 
*/

/* drop the indexes and constraints in LID, or else this won't work
	bldmsf2000ddl -UDI <productline> gl l_hglc
	bldmsf2000ddl -UDI <productline> gl l_dglc
	bldmsf2000ddl -UDI <productline> gl glcontrol
*/

USE PROD
GO
ALTER TABLE L_HGLC 
	ALTER COLUMN L_INDEX
            CHAR(4)COLLATE Latin1_General_BIN NOT NULL
GO
ALTER TABLE L_HGLC 
	ALTER COLUMN ATCHNBR
            CHAR(2)COLLATE Latin1_General_BIN NOT NULL
GO
ALTER TABLE L_DGLC 
	ALTER COLUMN L_INDEX
            CHAR(4)COLLATE Latin1_General_BIN NOT NULL
GO
ALTER TABLE L_DGLC 
	ALTER COLUMN ATCHNBR
            CHAR(2)COLLATE Latin1_General_BIN NOT NULL
GO
ALTER TABLE GLCONTROL
	ALTER COLUMN L_INDEX
            CHAR(4)COLLATE Latin1_General_BIN NOT NULL
GO

/* rebuild the indexes in LID
	bldmsf2000ddl -UI <productline> gl l_hglc
	bldmsf2000ddl -UI <productline> gl l_dglc
	bldmsf2000ddl -UI <productline> gl glcontrol
*/