Modifying the size of a nvarchar column of a data item of an activity

 Applies to: Microsoft BizTalk Server 2004

You have already deployed a BAM definition with a text data item but now you want to change the maximum length of data item. The recommended way would be to remove the bam deployment and re-deploy the BAM definition with the new length for the data item. However, it is not always possible as you may already have data in the BAM tables and cannot lose the data.

This blog will detail how you may modify the length of the text data item without having to remove and re-deploy. Note that this is unsupported by Microsoft.

1.      Modify the columns of tables in Bam Primary Import database

• bam_<Activity Name>_Active
• bam_<Activity Name>_Completed

      ALTER TABLE <Table Name>

            ALTER COLUMN <Data Item Name> NVARCHAR(<New Length>)

      GO

• bam_<View Name>_<Activity Name>View_ActiveInstancesSnapshot if a view has been deployed

     ALTER TABLE <Table Name>

            ALTER COLUMN <Alias Name> NVARCHAR(<New Length>)

      GO

2.      Modify the stored procedure in Bam Primary Import database

• bam_<Activity Name>_PrimaryImport

Modify the stored procedure input parameter for the nvarchar data item to its new length.

3.      Modify the columns of tables in Bam Archive database

• bam_<Activity Name>_Instances    

      ALTER TABLE <Table Name>

            ALTER COLUMN <Data Item Name> NVARCHAR(<New Length>)

   GO

4.      Modify the columns of tables in Bam Star Schema database

• bam_<View Name>_<Activity Name>View _Staging

      ALTER TABLE <Table Name>

            ALTER COLUMN <Alias Name> NVARCHAR(<New Length>)

   GO

• bam_<Cube Name>_Dim_<Data Dimension> 

      ALTER TABLE <Table Name>

            ALTER COLUMN <Data Dimension Member Name> NVARCHAR(<New Length>)

   GO