Conversion failed when converting the nvarchar value to datatype int in stored procedure

See more:

SQL

SSMS

Hello All,
I have been fighting this for a few days now. I'm using SQL SERVER v17.5

I have tried many suggestions and solutions from across the internet but I can't seem to get rid of this error. This procedure has been running flawlessly until I try to add Part.Note column (as per new business requirement). It is stored as nvarchar(500) in the source table and every other table that I am working with. I have tried COLLATION, CAST, CONVERT, ISNUMERIC(), even a separate update statement! Nothing seems to work.
I am at my wit's end here. Can someone shed a light on this? I looked at something related to CLR's and not sure if it has anything do with the MERGE STATEMENT and/or the server it is trying to reach. Please help!

Thank you in advance :)

This is my query:=

USE [SomeDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_RegionalPart]
	
@RegionID as int 
AS
BEGIN

Declare @sql as nvarchar(max)
Declare @sqltrunc as nvarchar(max)
Declare @dbase as nvarchar(100)

if @RegionID = 1 
	Select @dbase = 'USE NorthAmericanDW'
if @RegionID = 2
	Select @dbase = 'USE SouthAmericanDW'
if @RegionID = 3 
	Select @dbase = 'USE EuropeanDW'
if @RegionID = 4
	Select @dbase = 'USE AsianPacificDW'

If OBJECT_ID('tempdb.dbo.#tmp') is not null drop table tempdb.dbo.#tmp

Create Table #tmp
	([PlantID] [INT] NULL,
	[PartID] [int] NULL,
	[PartNo] [nvarchar] (100) NULL,
	[Col1] [int] NULL,
	[Col2] [int] NULL,
	[Col3] [int] NULL,
	[PartNote] [nvarchar] (500) NULL,
	[Col4] [int] NULL,
	[Col5] [float] NULL,
	[Col6] [nvarchar](20) NULL,
	[Col7] [datetime] NULL,
	[Col8] [int] NULL,
	[Col9] [varchar](1) NULL,
	[Col10] [float] NULL,
	[CurrentRecord] [bit] NULL)

PRINT 'Temp Table Created'

Select @sqltrunc = 'Truncate Table ' + Replace(@dbase,'USE ',Space(0)) + '.dbo.RegionalPart'
exec(@sqltrunc)

PRINT 'Table Truncated'

Select @Sql = @dbase + space(1) +
				'SELECT 
				PlantID,
				PartID,
				PartNo,
				Col1,
				Col2,
				Col3,
				part.Note,
				Col4,
				Col5,
				Col6,
				Col7,
				Col8,
				Col9,
				Col10,
				CurrentRecord

				FROM PartTABLE part
				INNER JOIN 1
				INNER JOIN 2
				INNER JOIN 3
				INNER JOIN 4
				INNER JOIN 5
				LEFT JOIN 1
				WHERE 1=1 
				AND join2.regionid =  ' + cast(@RegionID as nvarchar(1)) 

PRINT @sql

Insert Into #tmp 
exec sp_executesql  @sql

PRINT 'Insert values in #Tmp'


Select @sql = @dbase + space(1) +
'MERGE dbo.RegionalPart AS TARGET 
USING #tmp AS SOURCE 
ON (TARGET.PlantID = SOURCE.PlantID and TARGET.PartID = SOURCE.PartID ) 

WHEN MATCHED AND 
	   (TARGET.[PartNo] <> SOURCE.[PartNo] OR
		TARGET.[Col1]  <> SOURCE.[Col1] OR
		TARGET.[Col2]  <> SOURCE.[Col2] OR
		TARGET.[Col3]  <> SOURCE.[Col3] OR
		TARGET.[PartNote] <> SOURCE.[PartNote],
		TARGET.[Col4]  <> SOURCE.[Col4] OR
		TARGET.[Col5]  <> SOURCE.[Col5] OR
		TARGET.[Col6]  <> SOURCE.[Col6] OR
		TARGET.[Col7]  <> SOURCE.[Col7] OR
		TARGET.[Col8]  <> SOURCE.[Col8] OR
		TARGET.[Col9] <> SOURCE.[Col9] OR
		TARGET.[Col10]  <> SOURCE.[Col10])
THEN 
	UPDATE 
	SET TARGET.[PartNo]  = SOURCE.[PartNo] 
		,TARGET.[Col1]  = SOURCE.[Col1] 
		,TARGET.[Col2]  = SOURCE.[Col2] 
		,TARGET.[Col3]  = SOURCE.[Col3] 
		,TARGET.[PartNote] = SOURCE.[PartNote],
		,TARGET.[Col4]  = SOURCE.[Col4] 
		,TARGET.[Col5]  = SOURCE.[Col5] 
		,TARGET.[Col6]  = SOURCE.[Col6] 
		,TARGET.[Col7]  = SOURCE.[Col7] 
		,TARGET.[Col8]  = SOURCE.[Col8] 
		,TARGET.[Col9] = SOURCE.[Col9] 
		,TARGET.[Col10]  = SOURCE.[Col10]

WHEN NOT MATCHED BY TARGET THEN 
INSERT 
           ([PlantID],
			[PartID],
			[PartNo],
			[Col1],
			[Col2],
			[Col3],
			[part.Note],
			[Col4],
			[Col5],
			[Col6],
			[Col7],
			[Col8],
			[Col9],
			[Col10],
			[CurrentRecord])

VALUES (    SOURCE.[PlantID]
           ,SOURCE.[PartID]
           ,SOURCE.[PartNo]
           ,SOURCE.[Col1]
           ,SOURCE.[Col2]
           ,SOURCE.[Col3]
           ,SOURCE.[part.Note]
           ,SOURCE.[Col4]
           ,SOURCE.[Col5]
           ,SOURCE.[Col6]
           ,SOURCE.[Col7]
           ,SOURCE.[Col8]
           ,SOURCE.[Col9]
		   ,SOURCE.[Col10]
           ,SOURCE.[CurrentRecord]);'


PRINT  'INSERT STATEMENT: ' 
PRINT @SQL

exec sp_executesql  @sql


PRINT  'INSERT STATEMENT AFTER EXECUTION: ' 
PRINT @SQL


Drop Table #tmp

End

What I have tried:

I have tried:

CONVERT
CAST
ISNUMERIC
COLLATION

NOTE --> This specific field has special characters phonetic from different languages. I am not sure why SQL cannot recognize this in something similar to UTF-8 format. We have many fields like that in other tables and they work and display data fine.