Resolving View Creation Issues in Pivotal Admin Console After SQL Server Upgrade

Overview

After upgrading to a newer version of Pivotal Admin Console and SQL Server (e.g., moving from SQL Server 2014 to SQL Server 2019), and performing ACC, you may encounter view creation failures involving memo fields. These issues arise because SQL Server 2019 enforces stricter data type handling, particularly with implicit conversions and memo-type fields.

Information

When attempting to create views that worked previously (e.g., in 6.6.1), you may now see errors such as:

Could not create view 'F5_FA_Payment' because BM Table fields and ED View fields do not match.  
[Field definition of 'F5_FA_Payment.Payment_Comments' in BM and ED does not match.]  

This happens because:

• SQL Server 2019 defaults casts like CAST(... AS NVARCHAR) to NVARCHAR(30) if no length is specified.

• Memo fields in BM tables are stored as text in SQL, so mismatches occur when the ED view definition doesn’t align with BM table definitions.

Solution Steps

1. Identify the Problem Fields
   Locate memo fields (e.g., Payment_Comments) in your views that cause mismatches.

2. Cast Memo Fields as TEXT
   Update your SQL queries so memo fields are explicitly cast to TEXT. For example:

   CAST(Payment_Comments AS TEXT) AS Payment_Comments
   

3. Ensure Consistent Lengths for Other Fields
   If you are concatenating or casting other string fields, specify the correct length to match the BM table definition. For example:

   CAST(esa2.Workshop_Preference AS NVARCHAR(2000))  
   

   Then ensure the final cast of the entire expression matches the BM table’s field type (e.g., NVARCHAR(2000) or VARCHAR(2000)).

4. Recreate the View
   After adjusting casts, re-run the view creation by performing ACC in Pivotal Admin Console. The mismatch error should no longer occur.

By explicitly casting memo fields as TEXT and aligning lengths with BM definitions, you can successfully create views in Pivotal Admin Console on SQL Server 2019.

Frequently Asked Questions

Why did the view fail after the upgrade?
SQL Server 2019 enforces stricter rules on data type conversions and lengths, which can expose mismatches between BM table definitions and ED view definitions.

What changed with memo fields?
Memo fields in Pivotal are effectively stored as text in SQL. In SQL Server 2019, these fields must be explicitly cast as TEXT to align with BM tables.

Do I always need to specify lengths for string fields?
Yes, when casting fields (e.g., VARCHAR or NVARCHAR), explicitly set the length to match the BM table definition to prevent mismatches.