EcomCountries and EcomCountryText tables

I am trying to add the states for Australia. Both of these tables restrict the CountryRegionCode to 2 characters on Dw8.9.2.0. I see that this has been expanded to 3 characters on Dw9 solutions. What is the impact of increasing to nvarchar(3) for these tables so I can add all of the Australian states?

Here is the query I am running but only the 2 letter states are created:

INSERT INTO [dbo].[EcomCountries] ([CountryCode2] ,[CountryCode3] ,[CountryCurrencyCode] ,[CountryNumber] ,[CountryCultureInfo] ,[CountryRegionCode]) VALUES ('AU','','','0','','ACT')
INSERT INTO [dbo].[EcomCountries] ([CountryCode2] ,[CountryCode3] ,[CountryCurrencyCode] ,[CountryNumber] ,[CountryCultureInfo] ,[CountryRegionCode]) VALUES ('AU','','','0','','NSW')
INSERT INTO [dbo].[EcomCountries] ([CountryCode2] ,[CountryCode3] ,[CountryCurrencyCode] ,[CountryNumber] ,[CountryCultureInfo] ,[CountryRegionCode]) VALUES ('AU','','','0','','NT')
INSERT INTO [dbo].[EcomCountries] ([CountryCode2] ,[CountryCode3] ,[CountryCurrencyCode] ,[CountryNumber] ,[CountryCultureInfo] ,[CountryRegionCode]) VALUES ('AU','','','0','','QLD')
INSERT INTO [dbo].[EcomCountries] ([CountryCode2] ,[CountryCode3] ,[CountryCurrencyCode] ,[CountryNumber] ,[CountryCultureInfo] ,[CountryRegionCode]) VALUES ('AU','','','0','','SA')
INSERT INTO [dbo].[EcomCountries] ([CountryCode2] ,[CountryCode3] ,[CountryCurrencyCode] ,[CountryNumber] ,[CountryCultureInfo] ,[CountryRegionCode]) VALUES ('AU','','','0','','TAS')
INSERT INTO [dbo].[EcomCountries] ([CountryCode2] ,[CountryCode3] ,[CountryCurrencyCode] ,[CountryNumber] ,[CountryCultureInfo] ,[CountryRegionCode]) VALUES ('AU','','','0','','VIC')
INSERT INTO [dbo].[EcomCountries] ([CountryCode2] ,[CountryCode3] ,[CountryCurrencyCode] ,[CountryNumber] ,[CountryCultureInfo] ,[CountryRegionCode]) VALUES ('AU','','','0','','WA')

INSERT INTO [dbo].[EcomCountryText] ([CountryTextCode2] ,[CountryTextLanguageID] ,[CountryTextName] ,[CountryTextRegionCode]) VALUES ('AU','ENU','Australian Captial Territory','ACT')
INSERT INTO [dbo].[EcomCountryText] ([CountryTextCode2] ,[CountryTextLanguageID] ,[CountryTextName] ,[CountryTextRegionCode]) VALUES ('AU','ENU','New South Wales','NSW')
INSERT INTO [dbo].[EcomCountryText] ([CountryTextCode2] ,[CountryTextLanguageID] ,[CountryTextName] ,[CountryTextRegionCode]) VALUES ('AU','ENU','Northern Territory','NT')
INSERT INTO [dbo].[EcomCountryText] ([CountryTextCode2] ,[CountryTextLanguageID] ,[CountryTextName] ,[CountryTextRegionCode]) VALUES ('AU','ENU','Queensland','QLD')
INSERT INTO [dbo].[EcomCountryText] ([CountryTextCode2] ,[CountryTextLanguageID] ,[CountryTextName] ,[CountryTextRegionCode]) VALUES ('AU','ENU','South Australia','SA')
INSERT INTO [dbo].[EcomCountryText] ([CountryTextCode2] ,[CountryTextLanguageID] ,[CountryTextName] ,[CountryTextRegionCode]) VALUES ('AU','ENU','Tasmania','TAS')
INSERT INTO [dbo].[EcomCountryText] ([CountryTextCode2] ,[CountryTextLanguageID] ,[CountryTextName] ,[CountryTextRegionCode]) VALUES ('AU','ENU','Victoria','VIC')
INSERT INTO [dbo].[EcomCountryText] ([CountryTextCode2] ,[CountryTextLanguageID] ,[CountryTextName] ,[CountryTextRegionCode]) VALUES ('AU','ENU','Western Australia','WA')

Hi Terri

Yes, we changed that for 9.3 exactly to support aus regions. The change is strictly in the database and a very small change for the UI.

This is the sqls we ran - you might be able to do the update a little less complicated when being a DBA.:

<package version="2027" releasedate="14-02-2017" RunOnce="True">
        <database file="Ecom.mdb">
            <EcomCountries>
                <sql conditional="">ALTER TABLE [EcomCountryText] DROP CONSTRAINT [PK_EcomCountryText];</sql>
                <sql conditional="">ALTER TABLE [EcomCountryText] DROP CONSTRAINT [DW_PK_EcomCountryText];</sql>
                <sql conditional="">ALTER TABLE [EcomCountryText] DROP CONSTRAINT [FK_EcomCountryText_EcomCountries];</sql>
                <sql conditional="">ALTER TABLE [EcomCountryText] DROP CONSTRAINT [DW_FK_EcomCountryText_EcomCountries];</sql>
                <sql conditional="">ALTER TABLE [EcomCountryText] DROP CONSTRAINT [DW_DF_EcomCountryText_RegionCode];</sql>
                <sql conditional="">ALTER TABLE [EcomCountries] DROP CONSTRAINT [PK_EcomCountries];</sql>
                <sql conditional="">ALTER TABLE [EcomCountries] DROP CONSTRAINT [DW_PK_EcomCountries];</sql>
                <sql conditional="">ALTER TABLE [EcomCountries] DROP CONSTRAINT [DW_DF_EcomCountries_RegionCode];</sql>
                <sql conditional="">ALTER TABLE [EcomFees] DROP CONSTRAINT [DW_DF_EcomFees_RegionCode];</sql>
                <sql conditional="">ALTER TABLE [EcomMethodCountryRelation] DROP CONSTRAINT [DW_DF_EcomMethodCountryRelation_RegionCode];</sql>
                <sql conditional="">
                    ALTER TABLE [EcomCountries] ALTER COLUMN [CountryRegionCode] [nvarchar](3) NOT NULL;
                    ALTER TABLE [EcomCountryText] ALTER COLUMN [CountryTextRegionCode] [nvarchar](3) NOT NULL;
                    ALTER TABLE [EcomFees] ALTER COLUMN [FeeRegionCode] [nvarchar](3) NOT NULL;
                    ALTER TABLE [EcomMethodCountryRelation] ALTER COLUMN [MethodCountryRelRegionCode] [nvarchar](3) NOT NULL;
                </sql>
                <sql conditional="">
                    ALTER TABLE [EcomCountries] ADD CONSTRAINT [DW_PK_EcomCountries] PRIMARY KEY NONCLUSTERED ([CountryCode2],[CountryRegionCode])
                    ALTER TABLE [EcomCountries] ADD CONSTRAINT [DW_DF_EcomCountries_RegionCode] DEFAULT (N'') FOR [CountryRegionCode]
                    ALTER TABLE [EcomCountryText] ADD CONSTRAINT [DW_PK_EcomCountryText] PRIMARY KEY NONCLUSTERED ([CountryTextCode2],[CountryTextRegionCode],[CountryTextLanguageId])
                    ALTER TABLE [EcomCountryText] WITH CHECK ADD CONSTRAINT [DW_FK_EcomCountryText_EcomCountries] FOREIGN KEY([CountryTextCode2], [CountryTextRegionCode]) REFERENCES [EcomCountries] ([CountryCode2], [CountryRegionCode])
                    ALTER TABLE [EcomCountryText] ADD CONSTRAINT [DW_DF_EcomCountryText_RegionCode] DEFAULT (N'') FOR [CountryTextRegionCode]
                    ALTER TABLE [EcomFees] ADD CONSTRAINT [DW_DF_EcomFees_RegionCode] DEFAULT (N'') FOR [FeeRegionCode]
                    ALTER TABLE [EcomMethodCountryRelation] ADD CONSTRAINT [DW_DF_EcomMethodCountryRelation_RegionCode] DEFAULT (N'') FOR [MethodCountryRelRegionCode]
                </sql>
            </EcomCountries>
        </database>
    </package>

In this file, change XXX : /Admin/Module/eCom_Catalog/dw7/edit/EcomCountry_Edit.aspx, change <asp:TextBox runat="server" ID="colCode" Text="" CssClass="std" Width="100" MaxLength="3" Style="margin-left: 5px;" /> to MaxLength=3 instead of 2.

BR Nicolai

Developer forum

EcomCountries and EcomCountryText tables

Replies