SQL Server: Varchar to Numeric Conversion Error - No Numeric

Question

I am encountering data-conversion error on a table-insert and am baffled. I'm writing a script to do a quick-and-dirty reconciliation of freight costs from pre-consolidation and post-consolidation databases. I have two db tables created by importing CSV files. To keep things simple for the DBA, all the data from the CSV files will go into varchar columns in the tables - the recon scripts will handle conversion/casting as needed. The client is on SQL Server 2008 R2.

Here are abbreviated definitions for the tables in question:

CREATE TABLE [dbo].[NOCOMMIT_PROD](
[SORT_DELTA_TOTAL] [varchar](50) NULL,
[rec_type] [varchar](50) NULL,
[price_id] [varchar](50) NULL,
[quote_id] [varchar](50) NULL,
[shipto_org_id] [varchar](50) NULL,
...
[trace] [varchar](8000) NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[NOCOMMIT_TEST](
[SORT_DELTA_TOTAL] [varchar](50) NULL,
[rec_type] [varchar](50) NULL,
[price_id] [varchar](50) NULL,
[quote_id] [varchar](50) NULL,
[shipto_org_id] [varchar](50) NULL,
...
[trace] [varchar](8000) NULL
) ON [PRIMARY]

Here is the temp-table definition and the Insert/Select statement that's erroring:

if OBJECT_ID('tempdb..#tmp1') is not null drop table #tmp1
create table #tmp1(
price_ID bigint
,quote_ID bigint
,shipTo_ID bigint
,product_ID bigint
,trmnl_ID bigint
,carrier_ID bigint
,test_freight_pricing_rt decimal(15,7)
,prod_freight_pricing_rt decimal(15,7)
,test_trace varchar(8000)
,prod_trace varchar(8000)
,test_carrierID_found bigint
,prod_carrierID_found bigint
,test_surcharge  decimal(15,7)
,prod_surcharge  decimal(15,7)
,test_xtra_surcharge  decimal(15,7)
,prod_xtra_surcharge  decimal(15,7)
,test_miles int
,prod_miles int
,test_rate decimal(15,7)
,prod_rate decimal(15,7)
,explanation varchar(400)
)

insert into #tmp1(
price_ID
,quote_ID
,shipTo_ID
,product_ID
,trmnl_ID
,carrier_ID
,test_freight_pricing_rt
,prod_freight_pricing_rt
,test_trace
,prod_trace
)
select
cast(p.price_ID as bigint)
,cast(p.quote_ID as bigint)
,cast(p.shipto_org_id as bigint)
,cast(q.product_id as bigint)
,cast(p.cust_term_relation_id as bigint)
,cast(p.carrier_org_ID as bigint)
,cast(t.freight as decimal(15,7))
,cast(p.freight as decimal(15,7))
,t.trace
,p.trace
from NOCOMMIT_PROD p
join NOCOMMIT_TEST t on p.price_id = t.price_id
join tbl_fuel_cost_quote q on q.quote_id = p.quote_id
join tbl_fuel_cust_price cp on cp.price_id = p.price_id
where p.price_id != 'NULL' and p.price_id <> -1

When I execute the above, it returns "Msg 8114, Level 16, State 5, Line 54 Error converting data type varchar to numeric.". If I comment out the insert of the prod_trace column, it executes just fine. I'm baffled by this, since the source for prod_trace is the NOCOMMIT_PROD.trace columns (same size and type). I cannot find anything wrong with the source data. Can someone please provide insight on why I'm getting this error when there is no numeric involved?

Thanks for any help and insight you can furnish!!

try to replace each column with 0 one by one into a similar test table. Once you stop getting errors, you will know which column causes the error — t-clausen.dk
– t-clausen.dk, Commented Jul 22, 2015 at 13:10
Check the data of that column(p.trace). If it is only numeric then you will not get error. Please recheck your data. — knkarthick24
– knkarthick24, Commented Jul 22, 2015 at 13:18
I think the decimal datatype is interchangeable with the numeric datatype, so the error message could be referring to one of your decimal columns. — Tab Alleman
– Tab Alleman, Commented Jul 22, 2015 at 13:23

Tab Alleman · Accepted Answer · 2015-07-22 13:27:32Z

1

Yes, execute the following script:

  DECLARE @t TABLE (col1 decimal(10,2));

  INSERT INTO @t (col1) VALUES ('Hi');

And you will get this error message:

Msg 8114, Level 16, State 5, Line 3 Error converting data type varchar to numeric.

The error lies in the data in one of your decimal columns.

answered Jul 22, 2015 at 13:27

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TexasPrd · Accepted Answer · 2015-07-22 14:50:25Z

0

It turns out that there were null values in NOCOMMIT_TEST.price_id. I worked back to that after restructuring the script, and getting the error again when I added the join to NOCOMMIT_TEST without referencing any NOCOMMIT_TEST columns other than the join on price_id. What confused me in the initial case was why SQL Server only threw the error when I referenced p.trace (NOCOMMIT_PROD.trace) - that didn't, and still doesn't, make any sense. But I know where the issue is now. Thanks for your help!

answered Jul 22, 2015 at 14:50

TexasPrd

113 bronze badges

Collectives™ on Stack Overflow

SQL Server: Varchar to Numeric Conversion Error - No Numeric

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related