SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Conversion error from varchar to int
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ejoeyz_85
Yak Posting Veteran

Malaysia
54 Posts

Posted - 01/12/2008 :  19:38:53  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 01/12/2008 :  19:45:27  Show Profile  Reply with Quote
How can you convert that string to number?
Go to Top of Page

ejoeyz_85
Yak Posting Veteran

Malaysia
54 Posts

Posted - 01/12/2008 :  19:46:46  Show Profile  Reply with Quote
ermm..what do u mean?
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 01/12/2008 :  19:50:12  Show Profile  Reply with Quote
Are 'ejoy' numbers?
Go to Top of Page

ejoeyz_85
Yak Posting Veteran

Malaysia
54 Posts

Posted - 01/12/2008 :  19:51:38  Show Profile  Reply with Quote
ejoy is varchar
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 01/12/2008 :  19:53:35  Show Profile  Reply with Quote
How can you convert them to numbers?
Go to Top of Page

ejoeyz_85
Yak Posting Veteran

Malaysia
54 Posts

Posted - 01/12/2008 :  19:56:35  Show Profile  Reply with Quote
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
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 01/12/2008 :  19:59:49  Show Profile  Reply with Quote
Check data type of all columns you used in the query, ensure you join on columns with same data type.
Go to Top of Page

ejoeyz_85
Yak Posting Veteran

Malaysia
54 Posts

Posted - 01/12/2008 :  20:03:59  Show Profile  Reply with Quote
im really sure... i've checked all data type of all columns... im very sure that my join on columns with same data type..

Go to Top of Page

rdelacruz
Starting Member

3 Posts

Posted - 02/29/2008 :  17:17:10  Show Profile  Reply with Quote
Have you resolved this yet? I'm having the same issue and would like some ideas.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 03/03/2008 :  02:08:51  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
You need to check if there are strings

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

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

rdelacruz
Starting Member

3 Posts

Posted - 03/03/2008 :  09:11:47  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/03/2008 :  09:17:47  Show Profile  Reply with Quote
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?
Go to Top of Page

rdelacruz
Starting Member

3 Posts

Posted - 03/03/2008 :  09:26:28  Show Profile  Reply with Quote
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.
Go to Top of Page

LODEN
Starting Member

Syria
1 Posts

Posted - 05/01/2009 :  11:18:33  Show Profile  Click to see LODEN's MSN Messenger address  Reply with Quote
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.
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 05/01/2009 :  11:49:01  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/01/2009 :  11:57:19  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000