December 1, 2021 at 5:55 pm
I work on SQL server 2014 I get error when run statement below
error say
Conversion failed when converting the nvarchar value '24VAC/DC' to data type int.
I got error when execut dynamic sql
EXEC (@SQL)
so how to solve this error please
data sample
IF OBJECT_ID('dbo.TAllfeatures') IS NOT NULL
DROP TABLE dbo.TAllfeatures
IF OBJECT_ID('dbo.TCondition') IS NOT NULL
DROP TABLE dbo.TCondition
IF OBJECT_ID('dbo.TPartAttributes') IS NOT NULL
DROP TABLE dbo.TPartAttributes
IF OBJECT_ID('dbo.TAllData') IS NOT NULL
DROP TABLE dbo.TAllData
CREATE TABLE [dbo].[TAllfeatures](
[ZPLID] [int] NULL,
[ZfeatureKey] [bigint] NULL,
[FeatType] [int] NULL,
[AcceptedValueID] [int] NULL,
[IsNumericValues] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (75533, NULL, 0, 0, 0)
INSERT [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (75533, 1505730001, 2044, 155, 0)
INSERT [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (75533, 1505730011, 2044, 274, 1)
INSERT [dbo].[TAllfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (75533, 1505730036, 2044, 271, 0)
CREATE TABLE [dbo].[TCondition](
[TradeCodeControlID] [int] NOT NULL,
[VersionYear] [int] NULL,
[Version] [float] NULL,
[CodeTypeID] [int] NULL,
[RevisionID] [bigint] NULL,
[Code] [varchar](20) NULL,
[ZPLID] [int] NULL,
[ZfeatureKey] [bigint] NULL,
[ZfeatureType] [nvarchar](200) NULL,
[EStrat] [nvarchar](2500) NULL,
[EEnd] [nvarchar](2500) NULL
) ON [PRIMARY]
INSERT [dbo].[TCondition] ([TradeCodeControlID], [VersionYear], [Version], [CodeTypeID], [RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (8123, 2020, 26, 849774, 307683692, N'8535400000', 75533, 1505730001, NULL, N'In(''Surge Protector'',''Surge Protector for Media Lines Only'',''Outlet Strip, Surge Protector'',''PDU, Surge Protector'',''Surge Lightning Arrester'',''Surge Arrester'',''Surge Protection Module'',''Lightning Arrester'',''Lightning Current Arrester'',''Protection Device'',''Surge Voltage Equipment'',''Isolated'',''Surge Protection'',''Coaxial'',''Base Element'')', N'')
INSERT [dbo].[TCondition] ([TradeCodeControlID], [VersionYear], [Version], [CodeTypeID], [RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (8124, 2020, 26, 849774, 307683692, N'8535400000', 75533, 1505730036, NULL, N'>1000', N'')
CREATE TABLE [dbo].[TPartAttributes](
[PartID] [int] NOT NULL,
[ZfeatureKey] [bigint] NULL,
[AcceptedValuesOption_Value] [float] NULL,
[Name] [nvarchar](500) NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (1128078, 1505730036, 24, N'24VAC/DC')
INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (1128078, 1505730001, NULL, N'Surge Voltage Equipment')
INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (1128089, 1505730036, 5, N'5V')
INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (1128089, 1505730001, NULL, N'Attachment Plug')
INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (27912821, 1505730001, NULL, N'Surge Protection Module')
INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (27912821, 1505730036, 480, N'480V')
INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (32817870, 1505730001, NULL, N'Surge Protector')
INSERT [dbo].[TPartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [Name]) VALUES (32817870, 1505730036, NULL, N'120V, 240V')
CREATE TABLE dbo.TAllData
(
PartID INT,
Code VARCHAR(20),
CodeTypeID INT,
RevisionID BIGINT,
ZPLID INT,
ConCount INT
)
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
DECLARE @ConStr nvarchar(max)= STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' , ' And Name ' ) , CAST(EStrat AS NVARCHAR(2500)),')') --ValueName
FROM dbo.TCondition CC INNER JOIN dbo.TAllfeatures AL with(nolock) ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues =0
FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')
DECLARE @Sql nvarchar(max)= CONCAT('INSERT INTO dbo.TAllData(PartID,Code,CodeTypeID,RevisionID,ZPLID ,ConCount)',' SELECT PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount
FROM
dbo.TPartAttributes PM with(nolock)
INNER JOIN dbo.TCondition Co with(nolock) ON Co.ZfeatureKey = PM.ZfeatureKey ',
'Where (1=1 and ' , @ConStr,' ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID ' ,
' Having Count(1)>= ',(SELECT COUNT(1) FROM TCondition with(nolock)))
--print @SQL
EXEC (@SQL) December 1, 2021 at 6:10 pm
I would start by taking out that "EXEC" line and use the PRINT line to see what is actually being run. You are comparing OR inserting an NVARCHAR with an INT. I suspect it is in your @CONSTR variable where the problem is and that you are comparing an NVARCHAR to an INT value without casting your INT as NVARCHAR first, but that is just guessing without running your code.
I would take up debugging it by utilizing that PRINT @SQL that you have and determine where the problem is and correct it. If you CAN'T determine from the printed output, try running the printed output to see where it is failing and use that.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 1, 2021 at 6:43 pm
i try as below :
DECLARE @ConStr nvarchar(max)= STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ''', CC.ZfeatureKey , '''', IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' , ' And Name ' ) , LEFT(EStrat, 1), '''', SUBSTRING(EStrat, 2, LEN(EStrat) -1), ''')') --ValueName
FROM dbo.TCondition CC INNER JOIN dbo.TAllfeatures AL ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues =0
FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')but issue still exist
December 1, 2021 at 7:08 pm
again, I would check the result of your print statement and see what is actually being executed and where the error likely is.
Running your query with the exec commented out and the PRINT uncommented gets you:
INSERT INTO #TAllData(PartID,Code,CodeTypeID,RevisionID,ZPLID ,ConCount) SELECT PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount
FROM
#TPartAttributes PM with(nolock)
INNER JOIN #TCondition Co with(nolock) ON Co.ZfeatureKey = PM.ZfeatureKey Where (1=1 and (PM.ZfeatureKey= 1505730001 And Name In('Surge Protector','Surge Protector for Media Lines Only','Outlet Strip, Surge Protector','PDU, Surge Protector','Surge Lightning Arrester','Surge Arrester','Surge Protection Module','Lightning Arrester','Lightning Current Arrester','Protection Device','Surge Voltage Equipment','Isolated','Surge Protection','Coaxial','Base Element')) Or (PM.ZfeatureKey= 1505730036 And Name >1000) ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID Having Count(1)>= 2
Which is a little hard to read, but the key part is you have the following in there:
And Name >1000
You are comparing an NVARCHAR to an INT which will fail. You need to cast that INT to an NVARCHAR OR put some ' around it.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 1, 2021 at 7:27 pm
Here is the problem !
Your final query is
INSERT INTO dbo.TAllData(PartID
, Code
, CodeTypeID
, RevisionID
, ZPLID
, ConCount
)
SELECT PartID
, Code
, Co.CodeTypeID
, Co.RevisionID
, Co.ZPLID
, COUNT(1) AS ConCount
FROM dbo.TPartAttributes AS PM WITH(NOLOCK)
INNER JOIN dbo.TCondition AS Co WITH(NOLOCK)
ON Co.ZfeatureKey = PM.ZfeatureKey
WHERE(1 = 1
AND (PM.ZfeatureKey = 1505730001
AND Name IN('Surge Protector', 'Surge Protector for Media Lines Only', 'Outlet Strip, Surge Protector', 'PDU, Surge Protector', 'Surge Lightning Arrester', 'Surge Arrester', 'Surge Protection Module', 'Lightning Arrester', 'Lightning Current Arrester', 'Protection Device', 'Surge Voltage Equipment', 'Isolated', 'Surge Protection', 'Coaxial', 'Base Element'))
OR (PM.ZfeatureKey = 1505730036
AND Name > 1000 )
)
GROUP BY PartID
, Code
, Co.CodeTypeID
, Co.RevisionID
, Co.ZPLID
HAVING COUNT(1) >= 2;
The problem is the condition " AND Name > 1000 "
Name is a NVARCHAR](500) column ! the compared value is integer ( 1000 )
Hence all column content will get converted to INTEGER !!!
You need to sort this out !!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 1, 2021 at 7:54 pm
thanks for support and help
issue done where Name>1000
instead of
Name >'1000'
i need to make my code accept single quotes
when compare name with any value
DECLARE @ConStr nvarchar(max)= STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' , ' And Name ' ) , CAST(EStrat AS NVARCHAR(2500)),')') --ValueName
FROM dbo.TCondition CC INNER JOIN dbo.TAllfeatures AL with(nolock) ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues =0
FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')
December 1, 2021 at 9:45 pm
My opinion - this is a data problem, not something to be solved in your ConStr variable. I would fix this in the TCondition table as it contains the ">1000" string. I am not sure of a good way to reliably capture and correct the bad data in TCondition once data is already in there. Prior to data getting in there, I would have your stored procedure count the number of ' characters in the string and if it is 0 or an odd number, then the insert should fail. If it is even and non-zero then it could succeed. Mind you, you could still have bad data go in like
IN ('hello' ' world')
it is bad data because you have no comma between hello and world.
Mind you, I am also not a big fan of dynamic SQL generated from data that can be inserted into a table like what you have as it introduces HUGE risk. What if someone put some bad code in there like a DROP TABLE? If I wanted to be a jerk, I could insert a value like "= name)) ; TRUNCATE TABLE dbo.TCondition;" and your table would get truncated (note I didn't test this so it may not work without a bit more tweaking, but you get the idea - I am terminating the current query and then tossing in something malicious to get executed).
I would be careful with dynamic SQL...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 2, 2021 at 7:21 am
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply