Conversion error from varchar to int

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Conversion error from varchar to int

Author

Topic

ejoeyz_85
Yak Posting Veteran

54 Posts

Posted - 2008-01-12 : 19:38:53

I am getting an issue when I am using a stored proc from a view.
I am to returning values, one being a varchar(50).

When I run the view, the values for this column and all others are returned fine.
When I run the stored procedure, the following error is shown:

Conversion failed when converting the varchar value 'ejoy' to data type int.

All other values return fine bar this one column and as I said its already a varchar in the table so I don't know why sql server (2005) thinks I want to convert it, I don't and at no point have tried to.
Below is my query statement

quote:
SELECT u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.Period_of_monitoring, m.Ongoing_maintenance,
m.Savings_for_inverter_replacement, m.Monitoring, m.Total_anual_maint_and_monitor
FROM PerformanceData p, MonitoringCost m, Photovoltaic pv, Users u
WHERE p.Performance_id=m.MonitoringCost_id and
pv.PV_id=p.Performance_id and
pv.PV_id=m.MonitoringCost_id and
u.User_id =p.Performance_id and
u.User_id =pv.PV_id and
u.User_id = m.MonitoringCost_id

This error has been displayed

quote:
Conversion failed when converting the varchar value 'ejoy' to data type int.

Any ideas much appreciated...

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-12 : 19:45:27

How can you convert that string to number?

ejoeyz_85
Yak Posting Veteran

54 Posts

Posted - 2008-01-12 : 19:46:46

ermm..what do u mean?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-12 : 19:50:12

Are 'ejoy' numbers?

ejoeyz_85
Yak Posting Veteran

54 Posts

Posted - 2008-01-12 : 19:51:38

ejoy is varchar

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-12 : 19:53:35

How can you convert them to numbers?

ejoeyz_85
Yak Posting Veteran

54 Posts

Posted - 2008-01-12 : 19:56:35

i tot there is something mistake in my sql command... i dunno what happen.. i tried to solve, but still display the same error

Error converting data type varchar to int

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-12 : 19:59:49

Check data type of all columns you used in the query, ensure you join on columns with same data type.

ejoeyz_85
Yak Posting Veteran

54 Posts

Posted - 2008-01-12 : 20:03:59

im really sure... i've checked all data type of all columns... im very sure that my join on columns with same data type..

rdelacruz
Starting Member

3 Posts

Posted - 2008-02-29 : 17:17:10

Have you resolved this yet? I'm having the same issue and would like some ideas.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-03 : 02:08:51

You need to check if there are strings

Where varchar col not like '%[^0-9]%'

Madhivanan

Failing to plan is Planning to fail

rdelacruz
Starting Member

3 Posts

Posted - 2008-03-03 : 09:11:47

Please allow me to qualify, which I should have done in the first place:
My view works fine on my local database, as well as a development server. However, on a QA box, it gives me the conversion error. I'm simply doing a "SELECT * FROM SpecSheetView" on all three boxes. Works on two, fails on the third. The data is pretty much the same on all three boxes. When clear what I thought was bad data in one column, it tries to convert data in a different column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-03 : 09:17:47

quote:
Originally posted by rdelacruz

Please allow me to qualify, which I should have done in the first place:
My view works fine on my local database, as well as a development server. However, on a QA box, it gives me the conversion error. I'm simply doing a "SELECT * FROM SpecSheetView" on all three boxes. Works on two, fails on the third. The data is pretty much the same on all three boxes. When clear what I thought was bad data in one column, it tries to convert data in a different column.

Are you sure you dont have any non integer data in your QA server? also if you're giving this select as a part of INSERT, are you sure you're specifying columns in correct order?

rdelacruz
Starting Member

3 Posts

Posted - 2008-03-03 : 09:26:28

I'm very sure that there is. And there is on my box and the development box as well. There's supposed to be varchar data in the varchar field. The problem is that on one box, it's trying to convert this data to an int.

LODEN
Starting Member

1 Post

Posted - 2009-05-01 : 11:18:33

hi all
i have same error but my error displayed in trigger create it for a table and thats my trigger:

ALTER TRIGGER [Insert_Auditing] ON [dbo].[Dim_Airplane]
For INSERT
AS
DECLARE @AirplaneID INT;
DECLARE @AirlineID INT;
DECLARE @AirplaneTypeID int;
DECLARE @AirplaneCode CHAR(6);
DECLARE @ModificationDate DATETIME;
DECLARE @str nVARCHAR (4000)

set @str='AirplaneID : '+@AirplaneID+' ,AirlineID : ' +@AirlineID+' ,AirplaneTypeID : '+@AirplaneTypeID+' ,ModificationDate : '+@ModificationDate;

SELECT @AirplaneID=i.AirplaneID FROM inserted i;
SELECT @AirlineID=i.AirlineID FROM inserted i;
SELECT @AirplaneTypeID=i.AirplaneTypeID FROM inserted i;
SELECT @AirplaneCode=i.AirplaneCode FROM inserted i;
SELECT @ModificationDate=i.ModificationDate FROM inserted i;

INSERT INTO Dim_InsertAudit(RowInserted,TableName)
VALUES (@str,'Dim_Airplane')

and i have stored proc for insert data in a table ,this proc was working before i create the trigger and i cheched all col and all data types in the table and every thing back right when i delete trigger and the error is:
Conversion failed when converting the varchar value ' ,AirlineID : ' to data type int.
help me cuz i hava i semi project at my uni must Present it wish u help me.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-05-01 : 11:49:01

quote:
Originally posted by ejoeyz_85

When I run the stored procedure, the following error is shown:

Conversion failed when converting the varchar value 'ejoy' to data type int.

All other values return fine bar this one column and as I said its already a varchar in the table so I don't know why sql server (2005) thinks I want to convert it, I don't and at no point have tried to.

This error has been displayed

quote:
Conversion failed when converting the varchar value 'ejoy' to data type int.

Any ideas much appreciated...

It is doing an IMPLICIT conversion. One of your columns is an INT value, the other is a varchar value. Try doing ANSI joins and insuring that you are joining on the correct columns.
Try like this:



SELECT 
	u.User_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring
	, p.Period_of_monitoring, m.Ongoing_maintenance,m.Savings_for_inverter_replacement
	, m.Monitoring, m.Total_anual_maint_and_monitor
FROM 
	PerformanceData p
INNER JOIN
	MonitoringCost m
ON
	p.Performance_id=m.MonitoringCost_id
INNER JOIN
	Photovoltaic pv
ON
	pv.PV_id=p.Performance_id
AND
	pv.PV_id=m.MonitoringCost_id
INNER JOIN
	Users u
ON
	 u.[User_id] =p.Performance_id 
AND 
	  u.[User_id] =pv.PV_id 
AND 
	  u.[User_id] = m.MonitoringCost_id

Some of the joins look like overkill to me. Follow the first link in my signature, and provide some sample DDL and DML, and perhaps someone can offer a better solution.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-01 : 11:57:19

quote:
Originally posted by LODEN

hi all
i have same error but my error displayed in trigger create it for a table and thats my trigger:

<snip>the error is:
Conversion failed when converting the varchar value ' ,AirlineID : ' to data type int.

Look up CAST in books online. You are trying to concatenate a string but you are trying to combine VARCHAR and INT data. SQL is doing an Implicit conversion from VARCAHR to INT (as mentioned by DonAtWork) and that obviously doesn't work in this case.

You need to CAST you INT values to a VARCHAR of the proper length.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources