User unable to update NSPS subparts in SSPP application

[dougwaldron]

Reported by Wendy Troemel

I’ve got a source and I was trying to update the Rule Applicability. My source - Occidental Chemicals, AIRS 24500023 - is subject to 40 CFR 60 Subpart CC (glass manufacturing), but there is a 40 CFR 60 Subpart Cc ( municipal solid waste landfills) that the Platform thinks are one and the same. I’m trying to delete the 40 CFR 60 Subpart Cc but it keeps taking both to the removed column after you hit “Save NSPS Data.” How do I fix this?

Imported from JIRA: [IAIP-1092] User unable to update NSPS subparts in SSPP application (original by Douglas Waldron)

[dougwaldron]

dbo.APBSUBPARTDATA.STRSUBPART requires a case sensitive search/join.

Database collation is SQL_Latin1_General_CP1_CI_AS which is case-insensitive.

Fix is to either change all join/search statements to use collate SQL_Latin1_General_CP1_CS_AS (requires deploying IAIP update) or to modify DB column and all related columns to use SQL_Latin1_General_CP1_CS_AS collation (will need to be applied to all columns involved in joins).

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

[dougwaldron]

Update database columns:

alter table dbo.APBSUBPARTDATA
    alter column STRSUBPART varchar(20)
collate SQL_Latin1_General_CP1_CS_AS;

alter table dbo.LOOKUPSUBPART60
    alter column STRSUBPART varchar(20)
collate SQL_Latin1_General_CP1_CS_AS;

alter table dbo.LOOKUPSUBPART61
    alter column STRSUBPART varchar(20)
collate SQL_Latin1_General_CP1_CS_AS;

alter table dbo.LOOKUPSUBPART63
    alter column STRSUBPART varchar(20)
collate SQL_Latin1_General_CP1_CS_AS;

alter table dbo.LOOKUPSUBPARTSIP
    alter column STRSUBPART varchar(20)
collate SQL_Latin1_General_CP1_CS_AS;

alter table dbo.SSPPSUBPARTDATA
    alter column STRSUBPART varchar(20)
collate SQL_Latin1_General_CP1_CS_AS;

alter table dbo.LK_ICIS_PROGRAM_SUBPART
    alter column LK_SUBPART_CODE varchar(20)
collate SQL_Latin1_General_CP1_CS_AS;

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

[dougwaldron]

Collation conflict arises when concatenating strings from columns with different collation, e.g., in DAL.Rules.GetRuleSubpartsAsDataTable.

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

[dougwaldron]

alter table dbo.LK_ICIS_PROGRAM_SUBPART
    alter column ICIS_PROGRAM_SUBPART_DESC varchar(400)
collate SQL_Latin1_General_CP1_CS_AS;

alter table dbo.LOOKUPSUBPARTSIP
    alter column STRDESCRIPTION varchar(4000)
collate SQL_Latin1_General_CP1_CS_AS;

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

[dougwaldron]

IAIPFacilityLookUpTool.SearchBy should be modified to be case-insensitive

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

[dougwaldron]

The linked issue - IAIP-1093 Done has been resolved

by 557058:f58131cb-b67d-43c7-b30d-6b58d40bd077

[dougwaldron]

alter table dbo.LOOKUPSUBPART60
    alter column STRDESCRIPTION varchar(4000)
collate SQL_Latin1_General_CP1_CS_AS;

alter table dbo.LOOKUPSUBPART61
    alter column STRDESCRIPTION varchar(4000)
collate SQL_Latin1_General_CP1_CS_AS;

alter table dbo.LOOKUPSUBPART63
    alter column STRDESCRIPTION varchar(4000)
collate SQL_Latin1_General_CP1_CS_AS;

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41