Error converting data type varchar to bigint field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Error converting data type varchar to bigint field

Author

Topic

alejo46
Posting Yak Master

157 Posts

Posted - 2014-07-01 : 11:44:53

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

38200 Posts

Posted - 2014-07-01 : 12:37:47

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/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-01 : 12:46:13

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources