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 2000 Forums
 Transact-SQL (2000)
 Error converting data type varchar to bigint field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 07/01/2014 :  11:44:53  Show Profile  Reply with Quote
Good Morning, i need your help pls,

While executing a SP failed with an error:Error converting data type varchar to bigint

PRO_DETALLE_ALTAMIRA_POGE_TARJETAS 2738,748523,''

OutPut:
UPDATE DETALLE_ALTAMIRA_POGE_TARJETAS
SET IRM_ABONADO = TPT.IRM_ABONADO
,FEC_ACTUALIZACION = GETDATE ()
FROM DETALLE_ALTAMIRA_POGE_TARJETAS DPT
INNER JOIN TEMP_ALTAMIRA_POGE_TARJETAS TPT
ON TPT.NUM_CELULAR = DPT.NUM_CELULAR
AND TPT.IRM_ABONADO <> DPT.IRM_ABONADO
1 row(s) affected)
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
THIS IS THE STRUCTURE FOR BOTH TABLES:
DETALLE_ALTAMIRA_POGE_TARJETAS|dbo|user table|2008-08-06 17:30:39.997

Column_name|Type|Computed|Length|Prec|Scale|Nullable|TrimTrailingBlanks|FixedLenNullInSource|Collation
NUM_CELULAR|bigint|no|8| | |yes|(n/a)|(n/a)|
IRM_ABONADO|varchar|no|8| | |yes|no|no|SQL_Latin1_General_CP1_CI_AS
FEC_INSERCION|datetime|no|8| | |yes|(n/a)|(n/a)|
FEC_ACTUALIZACION|datetime|no|8| | |yes|(n/a)|(n/a)|
Identity|Seed|Increment|Not For Replication
No identity column defined.|||
RowGuidCol
No rowguidcol column defined.

TABLE TEMP_ALTAMIRA_POGE_TARJETAS|dbo|user table|2006-09-28 18:29:04.303
Column_name|Type|Computed|Length|Prec|Scale|Nullable|TrimTrailingBlanks|FixedLenNullInSource|Collation
NUM_CELULAR|varchar|no|15| | |yes|no|no|SQL_Latin1_General_CP1_CI_AS
IRM_ABONADO|varchar|no|8| | |yes|no|no|SQL_Latin1_General_CP1_CI_AS
Identity|Seed|Increment|Not For Replication
No identity column defined.|||

RowGuidCol
No rowguidcol column defined.
THS IS A SAMPLE FOR BOTH TABLES:

table TEMP_ALTAMIRA_POGE_TARJETAS
NUM_CELULAR|IRM_ABONADO
3155210020|SDP004
3157873500|SDP004
3185787120|SDP006
3165178340|SDP006

table DETALLE_ALTAMIRA_POGE_TARJETAS
NUM_CELULAR|IRM_ABONADO|FEC_INSERCION|FEC_ACTUALIZACION
3184448621|SDP006|2012-02-14 10:10:26.717|2012-02-14 10:10:26.717
3184339720|SDP005|2012-02-14 10:10:26.717|2012-02-14 10:10:26.717
3183013770|SDP006|2012-02-14 10:10:26.717|2013-09-17 06:16:11.347
3184305879|SDP006|2012-02-14 10:10:26.717|2014-03-16 11:25:56.590
3184339088|SDP005|2012-02-14 10:10:26.717|2012-02-14 10:10:26.717
3184787782|SDP004|2012-02-14 10:10:26.717|2012-10-07 06:49:34.797

Acording the yielded error this problem is likely to be on DATA from table TEMP_ALTAMIRA_POGE_TARJETAS and field NUM_CELULAR while converting to datatype bigint, isnt it ?

if so How do i identify invalida data on field TEMP_ALTAMIRA_POGE_TARJETAS.NUM_CELULAR in order to be discarded ?

Thanks for your help in advanced

tkizer
Almighty SQL Goddess

USA
36983 Posts

Posted - 07/01/2014 :  12:37:47  Show Profile  Visit tkizer's Homepage  Reply with Quote
You can use the ISNUMERIC() function to determine which rows are not valid.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30249 Posts

Posted - 07/01/2014 :  12:46:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
WHERE Col1 NOT LIKE '%[^0-9]%' -- Gives all rows with positive integer values only


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
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.08 seconds. Powered By: Snitz Forums 2000