Comments by Member 14057347

Member 14057347 20-Nov-18 12:10pm View

That was a typo on my part. The actual query has no errors in column names.

Member 14057347 20-Nov-18 11:31am View

The INSERT statement as I am truncating the table and repopulating it every time. Now that I think of it, the Update is redundant here (old code)

Member 14057347 20-Nov-18 11:30am View

Yes, query is trying to convert PartNote to int when it shouldn't. It is defined as nvarchar(500) in the source table, also being cast as nvarchar(500) when creating a temp table (which runs fine on it's own) and is also defined as nvarchar(500) in destination table. Why is SQL trying to convert it to Int? I know this field has Czech characters such as "Forma č" and also Chinese. Will that have anything to do with this error?

Member 14057347 20-Nov-18 10:50am View

Also, when I CAST this column while creating my temp table, it does not throw any errors. I also tried creating a proper table instead of temp table to see if that made a difference.
It only throw an error when it tried to INSERT the rows in the destination table. I have also tried to recreate the destination table to make sure there where no data type conflicts.

Member 14057347 20-Nov-18 10:36am View

Nvarchar(500)

I didn't mean to repost. New to the site and ended up creating 2 questions instead of one. Is there a way to merge them together?

Member 14057347 20-Nov-18 10:28am View

Santosh - The MERGE statement is now corrected. That was never the issue, it was just a typo.

F-ES Sitecore:

The issue is specifically related to PartNote field. If I comment that out, the query runs flawlessly. PartNote field is defined as NVARCHAR(500) in the ERP system, the staging tables and the target tables. So I really can't understand why it would not recognize it in this perticular query. As this is a note field, there are no data type or language restrictions on this field. It could include anything and everything!
Is there any way I can pin point what might be causing it? I have also tried to eliminate and REPLACE special ASCII characters but that hasn't helped either.

Member 14057347 20-Nov-18 10:23am View

First time using this site. Thanks for the suggestion.

Member 14057347 20-Nov-18 10:23am View

My apologies for late reply.
I have been examining the code and every column as you suggested. The issue is specifically with PartNote column. Even if I force CAST or CONVERT that field (the usual remedy for the said error) it doesn't seem work with whatever data that is in this column. If I remove PartNote column from the query, it runs without any issue. As this is a note field, it has no data type restriction for the front end user. Also, the data comes from various countries so it could be mixed with different languages so it is impossible for to see where the issue would be. Is there any way I can cast/convert it or use some other advanced function? When we export it to other applications, we usually convert these to follow UTF-8 data type in SSIS and other integration processes. Can something similar be done in SQL?

Comments by Member 14057347 (Top 8 by date)