SQL Server: Msg 8114, Level 16, State 5, Error converting data type varchar to numeric

Last Reply on Jul 06, 2014 05:36 PM By Mudassar

Posted on Jul 06, 2014 12:23 AM

Hi 
 
i have issue in stored procedure converting data type
 
Destination table dbo.Z_tbl_CirculationCopy_TEMP has numeric data type
But source table Z_tbl_Import_temp have VarChar Type
 
If I export data from source, I have error message 
 
Msg 8114, Level 16, State 5, Procedure Z_usp_IMPORT_EXCEL_UNPIVOT_1, Line 10
Error converting data type varchar to numeric.
 
Pls help to resolve this issue
 
Thanking you in advance
 
Maideen
 
Below is my store procedure
 

 
INSERT INTO dbo.Z_tbl_CirculationCopy_TEMP([MEMBER],[LANGUAGE],[TYPE],[PLATFORM],[CATEGORY],[MAINAREA],[STATE],[COPIES],
[MONTHNAME],[YEARNO],[REMARKS],[AVGISSUE_PAID],[AVGISSUE_FREE],[BCP_NTT],[BCP_50],[BCP_20],[BCP_20_Below],
[WP_TOTAL],[SABAH_TOTAL],[SARAWAK_TOTAL],[EM_Total])
 
SELECT [MEMBER],[LANGUAGE],[TYPE],[PLATFORM],[CATEGORY],[MAINAREA],[STATE],[COPIES],
[MONTHNAME],[YEARNO],[REMARKS],[AVGISSUE_PAID],[AVGISSUE_FREE],[BCP_NTT],[BCP_50],[BCP_20],[BCP_20_Below],
[WP_TOTAL],[SABAH_TOTAL],[SARAWAK_TOTAL],[EM_Total]
 
FROM (SELECT [MEMBER],[LANGUAGE],[TYPE],[PLATFORM],[CATEGORY],[MAINAREA],[MONTHNAME],[YEARNO],[REMARKS],
[AVGISSUE_PAID],[AVGISSUE_FREE],[BCP_NTT],[BCP_50],[BCP_20], [BCP_20_Below],
[WP_TOTAL],[SABAH_Total],[SARAWAK_Total],[EM_Total],
[Perlis],[Kedah],[Penang],[Perak],[Kelantan],[Terengganu],[Pahang],[Selangor],[WP],[NegriSembilan],[Melaka],[Johor],
[KotaKinaBalu],[sandakan],[Tawau],[Kudat],[Pendalaman],[Labuan],[SABAH_Other],
[Kuching],[Miri],[Sibu],[Bintulu],[SriAman],[KotaSemerahan],[Sarikei],[SARAWAK_Others],[Others]
FROM dbo.Z_tbl_Import_temp ) P
 
UNPIVOT (Copies FOR State IN ([Perlis],[Kedah],[Penang],[Perak],[Kelantan],[Terengganu],[Pahang],[Selangor],
[WP],[NegriSembilan],[Melaka],[Johor],
[KotaKinaBalu],[Sandakan],[Tawau],[Kudat],[Pendalaman],[Labuan],[SABAH_Other],
[Kuching],[Miri],[Sibu],[Bintulu],[SriAman],[KotaSemerahan],[Sarikei],[SARAWAK_Others],[Others]
))AS unpvt

 

Posted on Jul 06, 2014 05:36 PM

There is some field in your table which is numeric while you are trying to save varchar or string value in it.

So print out all variables and check.