converting varchar to money part 2

Hello experts:

I orginally asked this question here:  http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21708078.html


Since then I'm having problems with this DTS.  It now fails on the line I use to convert my weird "0*100*0" rows to true money fields.  
This is the error I get now:  Cannot convert a char value to money. The char value has incorrect syntax.

This worked back when I first tried it and now I'm coming back to this DTS package and its failing on these conversions.  Any ideas?  I scanned the entire table looking for something out of the ordinary and I found nothing.  The fields in my temp table are in varchar format.  Final table is set to money.  

help!

jay-areAsked:
Who is Participating?
 
illCommented:
-- one more replace is needed as there is no "*0" in the field after replacing "0*"
update SERVICEINVOICE_final set TXBL = convert(money,cast(replace(replace(replace(serviceinvoice.TXBL,'0*',''),'*0',''),'*', '') as money) / 100,1) from serviceinvoice where serviceinvoice.ronum = serviceinvoice_final.ronum
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
What is the contents of the file?
Is the DTS trying to convert to money into the temp table directly?
are there any NULL values?
0
 
illCommented:
-- please, check rows returned from this
select * from SERVICEINVOICE
where isnumeric(cast(replace(replace(TXBL,'0*',''),'*0','') as money) / 100) <>1
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jay-areAuthor Commented:
angelIII:  You can see some examples of what's in the file in the link above.  The DTS is trying to convert to money on the insert/update statements.  Convert from the temp table to the final table.  I checked through 16k rows for anything out of the ordinary and couldn't find it.  I found one amount that was "23000*0*23500".  I didn't know if that would screw with the replace so I deleted that specific row and the insert still failed...

ill:  I will run your select shortly.
0
 
illCommented:
--when i'm looking at the statement now, i can see, that it solves nothing, as it already contains convert. This one may help you to detect "bad" lines
select * from SERVICEINVOICE
where isnumeric(replace(replace(TXBL,'0*',''),'*0','') ) <>1
0
 
jay-areAuthor Commented:
Ok ill, I ran your select and it comes up with a line where the txbl field is "1450*0*45522"

I ran this across the 2 other columns that are using this same convert and I came up with more.  There are 3 lines in total that have more than one amount in these "0*0*0" columns...How can I get around this?
0
 
jay-areAuthor Commented:
How difficult would it be to add the amounts together in these 3 lines?
50*0*100 to 1.50
etc

??
It seems to be a data error.  The user entered the amounts on the wrong line so I have these 3 lines with bad amounts...
0
 
jay-areAuthor Commented:
Any ideas?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.