Solved

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

Posted on 2009-04-14
12
1,010 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 142

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
 
LVL 142

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 142

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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 142

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 142

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Script to find duplicates 16 20
Slow SQL query 12 26
Query 14 0
SQL Query for Periods 3 0
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now