将nvarchar值转换为数据类型int时，sql转换失败。 [英] Sql conversion failed when converting the nvarchar value to data type int.

问题描述

大家好，

我已经打了好几天了。我正在使用SQL SERVER v17.5



我尝试了很多来自互联网的建议和解决方案，但我似乎无法摆脱这个错误。在我尝试添加Part.Note列（根据新业务要求）之前，此过程一直运行良好。它作为nvarchar（500）存储在源表和我正在使用的每个其他表中。我尝试过COLLATION，CAST，CONVERT，ISNUMERIC（），甚至是单独的更新声明！似乎没什么用。

我在这里结束了我的智慧。有人可以对此有所了解吗？我查看了与CLR相关的内容，并且不确定它是否与MERGE STATEMENT和/或它试图达到的服务器有关。请帮帮忙！



提前谢谢:)



这是我的疑问：=

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] OR
		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]
			,[PartNote]
			,[Col4]
			,[Col5]
			,[Col6]
			,[Col7]
			,[Col8]
			,[Col9]
			,[Col10]
			,[CurrentRecord])

VALUES (    SOURCE.[PlantID]
           ,SOURCE.[PartID]
           ,SOURCE.[PartNo]
           ,SOURCE.[Col1]
           ,SOURCE.[Col2]
           ,SOURCE.[Col3]
           ,SOURCE.[PartNote]
           ,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

我的尝试：



我是使用SQL SERVER v17.5

我试过：



CONVERT，

CAST，

ISNUMERIC（）

UPDATE语句

COLLATIONS，

What I have tried:

I'm using SQL SERVER v17.5
I have tried:

CONVERT,
CAST,
ISNUMERIC()
UPDATE statements
COLLATIONS,

推荐答案

阅读错误信息：

Read the error message:

Sql conversion failed when converting the nvarchar value to data type int.

很明显。

在某个地方大量代码，NVARCHAR值（可能来自列）正在转换为INT - 但它包含的值不是数字（或它的浮点）并且无法转换。这很可能是一个数据问题，我们无法访问您的数据。



所以首先要仔细查看您已紧密汇总的声明：删除

It's pretty clear.
Somewhere in that bulk of code, a NVARCHAR value (probably from a column) is being converted into an INT - but the value it contains is not numeric (or it's floating point) and can't be converted. That's a data problem, most likely, and we don't have any access to your data.

So start by looking at the statement you have assembled closely: remove the

exec sp_executesql @sql

line and replace它带有PRINT语句。仔细看看印刷的内容。弄清楚可能转换的内容及其来源。



我们不能为你做任何事情！

line and replace it with a PRINT statement. Look at what is printed very closely. Work out what might be converted and where it comes from.

We can't do any of that for you!

这篇关于将nvarchar值转换为数据类型int时，sql转换失败。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！