Issues with views SQL Server

[PGN]

I have a number of views that we are replicating as snapshots.  Before the May 2023 (2023.5.0.322) release which we installed end of September, the tables that were being created from the views would create target columns as type varchar.  After the release, they are being created as the source has them (char types).  Unfortunately, they contain trailing spaces which is causing havoc on the users.  We didn't notice because we hadn't dropped the tables after their initial creation.  We set the task up as truncate.  We just dropped/created all of them because there was an issue with one of the columns in the tables that had changed size.  I didn't believe it, originally but the target system has an audit of schema changes and I see the original sql script done by Qlik has them cast as varchar whereas the new version casts them as char.  The source views did not change.

So creating them as the original source is actually more accurate, but I'm trying to look for a root cause.  Was this a change made to that release?  I reviewed the notes, but didn't find anything.

[Dana_Baldwin]

Hi @PGN 

It is possible that this change was made as a fix that was caught internally. High impact and customer reported issues that are fixed are always listed in the release notes.

To confirm we would need to check with our internal support team, but we do not have a mechanism for doing that via the Community forum. Could you please submit a support case for this question?

Thanks,

Dana 

[PGN]

Sure.  I'll do that tomorrow.  Thx.

[john_wang]

Hello @PGN ,

Besides @Dana_Baldwin comment, maybe a global transformation helps, for example replace the data type STRING columns value with expression:

trim($AR_M_SOURCE_COLUMN_DATA)

hope this helps.

Regards,

John.

[PGN]

Yes.  I tried that but got some funky errors:

]I: Start loading table 'odn'.'rm_agPlanAFKS' (Id = 56) by subtask 10. Start load timestamp 00060C691725C42C (replicationtask_util.c:755)
00001116: 2023-12-13T14:22:46 [DATA_STRUCTURE ]E: SQLite general error. Code <1>, Message <near ".": syntax error>. [1000504] (at_sqlite.c:326)
00001116: 2023-12-13T14:22:46 [TRANSFORMATION ]E: Failed to init column calculation expression 'trim($PlanID),trim($PlanName),trim($ProductType),trim($ContractNumber),trim($LinkToLineOfBusiness.LineOfBusiness),trim($PBP),'2023-12-13 14:22:10.805263'' [1000504] (manipulator.c:1649)
00001116: 2023-12-13T14:22:46 [TRANSFORMATION ]W: The metadata transformations defined for table 'odn.rm_agPlanAFKS' were not performed as at least one of the transformation expressions contains an error (manipulator.c:164)
00001116: 2023-12-13T14:22:46 [SOURCE_UNLOAD ]E: Cannot refresh source table (56) metadata [1000504] (endpointshell.c:3943)
00009080: 2023-12-13T14:22:46 [DATA_STRUCTURE ]E: SQLite general error. Code <1>, Message <near ".": syntax error>. [1000504] (at_sqlite.c:326)
00009080: 2023-12-13T14:22:46 [TRANSFORMATION ]E: Failed to init column calculation expression 'trim($EntityCaseID),trim($EntityDecision),trim($Reviewer),trim($ExternalCaseReviewLevel),trim($ExternalReviewNarrative),trim($OPMdecision),trim($IfOTnotifiedappropriatedept),trim($DepartmentNotified),trim($LinkToCase.CaseNumber),'2023-12-13 14:22:10.805263'' [1000504] (manipulator.c:1649)
00009080: 2023-12-13T14:22:46 [TRANSFORMATION ]W: The metadata transformations defined for table 'odn.rm_agOPM' were not performed as at least one of the transformation expressions contains an error (manipulator.c:164)
00009080: 2023-12-13T14:22:46 [SOURCE_UNLOAD ]E: Cannot refresh source table (55) metadata [1000504] (endpointshell.c:3943)
00010424: 2023-12-13T14:22:46 [UTILITIES ]I: Mail "[GHPATTA2D1W19v.geisinger.edu\Error - System Error] ODINDEV1_LZ_OnBase_Crp1OnBase-S1:encountered errors" sent successfully (notification_manager.c:1923)

[DesmondWOO · Accepted solution]

Hi @PGN ,

As far as I remember, if the source and target databases are of the same type, an identical table structure is created. However, I am not sure whether this holds true for all databases. 

I have conducted following tests:

My table and view:
CREATE TABLE [dbo].[test] (ID int not null primary key, C1 CHAR(10), V1 VARCHAR(10));
CREATE VIEW [dbo].[test_v] as SELECT * FROM [dbo].[test];

1) SQL Server -> SQL Server
Created target tables [test] and [test_v] are same as source table: CHAR maps to CHAR, VARCHAR maps to VARCHAR.

2) SQL Server -> Oracle Server
CHAR maps to VARCHAR and VARCHAR maps to VARCHAR.

Please try following workaround:

1. Create a global transformation
2. Convert data type STRING to data type STRING. For example:
   Convert data type for %.% with column % and data type STRING to data type STRING and sub type Regular

Hope this helps.

Regards,
Desmond

[RandyWeis]

Thanks DesmondWOO.   This worked prefect!   I added a few more filters to make it fit my needs:

Convert data type for %.% with column % and data type STRING Scope expression: (($AR_M_SOURCE_COLUMN_NAME == 'col_1'
OR $AR_M_SOURCE_COLUMN_NAME == 'col_2')... etc
AND $AR_M_SOURCE_DATATYPE_LENGTH == 255 to data type STRING(255) and sub type Regular )

The columns that were CHAR(255) are now VARCHAR(255) in the Target SQL Server DB).   

The Snowflake conversion already worked as hoped.