BA error: Data types ntext and ntext are incompatible in the equal to operator

[winvarma]

Hi ,

while building a business adapter using the BA studio using vendor and contract nodes getting the below error.

"Data types ntext and ntext are incompatible in the equal to operator"

Attached the snapshot and BA studio is 2020R1 version.

Any suggestions what to verify.

Regards

[ChrisG]

If you set the log file to generate a "debug" log (rather than "info"), then you will get much more information which will point more precisely to what is going on here.

(This assumes that you're using FlexNet Manager Suite On-premises - I don't think debug logging can be done with Cloud imports.)

[winvarma]

Hi,

Thank you for the suggestion ,Yes using an on prem setup, and will change the log Detail level to Debug.

Thanks

[adrian_ritz1]

Hi @winvarma ,

What is your source for your BA import, is a xls file or sql table?

If it's a sql table may be you can change the column type in nvarchar.

The ntext data type is deprecated in favor of the nvarchar(max) data type. If you can change the data type in the table, that would be the best solution.

[winvarma]

Hi @adrian_ritz1 ,

The source is an xls file.

[adrian_ritz1]

Hi @winvarma ,

Try to change the column directly in xls, and put text and then try to import again, for me it looks like a conversion error. 

[kclausen]

@winvarma - It is likely that the field you are using to match on in your spreadsheet (Contract Number) is being interpreted by the Microsoft Access Engine driver as a numeric field as the first few rows of data in the spreadsheet have a numeric value.  In the FlexNet Manager schema this is a Text field.

Open up your source spreadsheet in Excel.

Highlight the entire Contract Number column.  Right-click on the column and select Format Cells.

Select Text as the category.

Save your Excel spreadsheet and try your Business Adapter again.

[adrian_ritz1]

Thank you @kclausen  that you explained this issue better then me. 

I wanted to mention the following: When you import in SQL from a xls file, by default the Microsoft driver will check 1st 10 lines and try to identify the filed type, if for example the 1st 10 lines are numbers, the type will be float or some numerical type, and if the rest oft the file have character and then numbers, the character cell will imported as blank, I found this very annoying and take some time till I figured out, because I 1st import the xls file in a staging table, run some normalization and then run the BA import.  

[mfranz]

You can usually get around this by actively casting/converting your ntext to another data type and apply your operator to this value.