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.