SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error converting data type varchar to numeric


Error converting data type varchar to numeric

Author
Message
klss
klss
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 221
Hello,

I have a problem in one of my production server database.

when i execute following query in production database
select TOP 1 Holder_Id,Customer_Account_No from Properties where Original_Dollar_Amt = convert(decimal(15,2),389.83)
and Convert(decimal(15,0),Serial_No) = 128249

it is giving following error:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

The same query executing successfully in Dev server. Both table properties are same on Prod and Dev.

Steps Tried:

When i add "trim" string function to the query
"select TOP 1 Holder_Id,Customer_Account_No from Properties where Original_Dollar_Amt = convert(decimal(15,2),389.83) and Convert(decimal(15,0),ltrim(Serial_No)) = 128249"

query executing successfully and fetching records.

Exported table to the same server on another database query executing successfully and fetching records without "trim" string function.


Both SQL versions are same on Production and Development servers.
Both are 8.00.2282

Serial_No is Varchar(20) data type,

I am trying to convert varchar into decimal data type.


Appricate your help


Kindest Regards,

klss

Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24297 Visits: 13362
It depends on the data stored in the table. Try running this:


SELECT Original_Dollar_Amt , Serial_No FROM Properties where ISNUMERIC(Original_Dollar_Amt) = 0 OR ISNUMERIC(Serial_No) = 0



Does it return rows? If so, fix the values and there you are.

Hope this helps
Gianluca

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
sandeep.cs3
sandeep.cs3
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 30
clean and crisp solution

http://www.a2zmenu.com/MySql/Arithmetic-overflow-error.aspx
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search