Generate data in UID format (mssql database)

Document ID : KB000094166
Last Modified Date : 02/05/2018
Show Technical Document Details
Issue:
Need to generate data. The use case is to use a uniqueidenfier present in an other table.  To access it, execsql and sqlist function are used, but both of the return an SQL error:

ERROR: Publish failed for job 19, Insert into Ecriture failed: UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO [CO].[Ecriture] ([DomaineId].... VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; SQL state [S0002]; error code [8169]; Conversion failed when converting from a character string to uniqueidentifier.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting from a character string to uniqueidentifier.

How could I do to keep the original format? (UID)
Environment:
SQL
TDM Portal 4.5
Cause:
In order to test the insert command I try to publish into a file : the SQL insert launch directly in the SQL client works : 

INSERT INTO CO.Ecriture 
([DomaineId],[DossierId],[ExerciceId],[PeriodeId],[Id],[ProvenanceEcriId],[OrigineEcriId],[TypeDocId],[JnlExoId],[CptContrepartieId],[CollaCreation],[DtCreation],[DtDerniereModif],[DtEcri],[DtFacture],[EstExporte],[Libelle],[MontantCredit],[MontantDebit],[EstModifiable],[EstDansDeclTVA],[NoFacture],[NoFiscal],[NoOrdre],[NoPiece],[EstSaisieLibLibre],[SiteCreation],[TauxChange],[DtValidation],[NoTransfert],[MasqueSaisieEcriId],[ModeReglementId],[DeviseId],[MontantDebitDevise],[MontantCreditDevise]) VALUES (6289,'C30C0CA3-6FDD-11D5-8683-0000E89B5359','E2EBC25F-D8C6-410B-A16F-F0F910F7E1BF','D2ACBDC8-32A9-40B4-8CC1-A85CB75BE2C5','41FAFE52-6344-421B-BA64-3E82E70A0499',1,1,null,'236828B5-ABE3-44D2-BFF7-29C5B9607ECB',null,null,'2018-04-11 00:00:00','2018-04-11 00:00:00','2018-04-11',null,1,null,null,null,1,1,null,null,1,null,1,null,null,null,null,null,null,null,null,null)

but when I use the option to publish to the data target the insert doesn't work

ERROR: Publish failed for job 19, Insert into Ecriture failed: UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO [CO].[Ecriture] ([DomaineId].... VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; SQL state [S0002]; error code [8169]; Conversion failed when converting from a character string to uniqueidentifier.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting from a character string to uniqueidentifier.
Resolution:
Problem is a uniqueidentifier column can be null and portal does not like it. Problem can be resolved by using the variable ~NOVALUE~ when wanting to insert a null value for the uniqueidentifer column

Note: That this is the only way the problem can be resolved. This is because a uniqueidentifier is converted into a char by java. When no column is empty it means that the portal will convert it into an empty string. When we try to insert data (empty string) into a unqueidentifer column you get the error message.  To make sure that you enter a null value rather than an empty string, you should use the system variable ~NOVALUE~
Additional Information:
Video URL on how to open a Support Case - https://communities.ca.com/videos/5898-demo-how-to-open-a-support-case
You can download the latest version of TDM by following the directions in this document: https://support.ca.com/us/knowledge-base-articles.TEC1903942.html.   
To contact support, go to https://www.ca.com/us/services-support/ca-support/contact-support.html