Solved

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

Posted on 2009-04-14
12
1,013 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
  • 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
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!

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

830 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