Solved

Conversion failed when converting the nvarchar value to data type int.

Posted on 2009-04-14
12
1,017 Views
Last Modified: 2012-05-06
I get back the following SQL Error when I run the below mentioned code. Any help is appreciated.

Conversion failed when converting the nvarchar value 'A4216' to data type int.
update ProceduresFeeSchedule 
set ProceduresFeeSchedule.Allowed = DMEModified.Allowed 
FROM    DMEModified 
             INNER JOIN ProceduresFeeSchedule ON DMEModified.HPCPC = ProceduresFeeSchedule.ProceduresId
Where ProceduresFeeSchedule.FeeScheduleID = 11

Open in new window

0
Comment
Question by:Jeff S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 15

Expert Comment

by:MNelson831
ID: 24143204
Either DMEModified.Allowed or DMEModified.Allowed has non-numeric data in it.

First try putting quotes in your where clause:

Where ProceduresFeeSchedule.FeeScheduleID = '11' (this makes the 11 a string instead of a number)

If that doesn't do it then let me know
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24143208
ok, in which field is the value A4216?

0
 
LVL 7

Author Comment

by:Jeff S
ID: 24143227
DMEModified.HPCPC
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24143246
then, this change should do:
update ProceduresFeeSchedule 
set ProceduresFeeSchedule.Allowed = DMEModified.Allowed 
FROM    DMEModified 
             INNER JOIN ProceduresFeeSchedule ON DMEModified.HPCPC = cast(ProceduresFeeSchedule.ProceduresId as varchar(100))
Where ProceduresFeeSchedule.FeeScheduleID = 11

Open in new window

0
 
LVL 15

Expert Comment

by:oobayly
ID: 24143254
You're trying to compare an int column to a varchar column
ie.
SELECT * FROM foo WHERE IntColumn = 'abc';

The above command causes an error as the server is attempting to convert 'abc' to an integer so it can be compared to int column.

Note, this works, as '123' can be converted to an int
SELECT * FROM foo WHERE IntColumn = '123';

Basically, either
ProceduresFeeSchedule.ProceduresId is a VARCHAR column and  DMEModified.HPCPC is an INT column
or
DMEModified.Allowed is a VARCHAR column and ProceduresFeeSchedule.Allowed is an INT column
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24143256
side note: please use table aliases to make your queries more readable:
update pfs
set pfs.Allowed = m.Allowed 
FROM ProceduresFeeSchedule pfs 
JOIN DMEModified m 
  ON m.HPCPC = cast(pfs.ProceduresId as varchar(100))
Where pfs.FeeScheduleID = 11

Open in new window

0
 
LVL 7

Author Comment

by:Jeff S
ID: 24143287
0 rows affected - should have been about 1800 rows. I am not getting the error though and thats positive news.
0
 
LVL 7

Author Comment

by:Jeff S
ID: 24143350
angel
I used your coding (snippet below). It looks like it is the cast you created, it is not linking the tables or at least not linking the fields. I get back 0 rows affected.
 

update pfs
set pfs.Allowed = m.Allowed 
FROM ProceduresFeeSchedule pfs 
JOIN DMEModified m 
  ON m.HPCPC = cast(pfs.ProceduresId as varchar(100))
Where pfs.FeeScheduleID = 11

Open in new window

0
 
LVL 15

Expert Comment

by:MNelson831
ID: 24143377
Your are linking a text field to a number field.  How many rows did you expect to get back?

Is their a field in ProceduresFeeSchedule that would contain 'A4216'  or is there a field in DMEModified that contains ProceduresId?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24143387
well, the error has gone :)

if the query no returns 0 row, so it is.
the problem is likely a logical problem, for example are you sure you want to join on those fields?
it could be also that really, for that join, there are no rows matching...

please double-check
0
 
LVL 7

Author Comment

by:Jeff S
ID: 24143519
OK there is no relationship between the 2 tables I found. When I put both tables in Query Designer nothing linked and i got a CROSS JOIN. Any ways around this?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24145416
can you show some sample row data from the 2 tables, and what the resulting output should be, please?
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question