Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Error converting data type nvarchar to bigint Expand / Collapse
Author
Message
Posted Monday, August 25, 2008 10:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 16, 2009 6:05 AM
Points: 39, Visits: 99
Hi

i am using sql server 2005 to convert data type nvarchar to bigint

but i show below error

Error:Error converting data type nvarchar to bigint

Thanks
murali
Post #558588
Posted Monday, August 25, 2008 11:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:42 AM
Points: 6,731, Visits: 8,480
nvarchar to bigint requires your nvarchar content to be convertable to numbers !

check your nvarchar columns content for non numeric content or special characters, ...


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #558604
Posted Tuesday, August 26, 2008 12:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 16, 2009 6:05 AM
Points: 39, Visits: 99
Hi i checked nvarchar columns content

i am giving one column content

customer key(nvarchar(50)
-------------
500910000000000000
500903000000000000
500909000000000000
500903000000000000
500805000000000000
500911000000000000
500905000000000000
500911000000000000
500905000000000000
500911000000000000

for this i create one new column with newcustomer key(bigint)

and update the new column with old column

like update tablename set newcustomer key=customer key

it show error

error converting datatype nvarchar to bigint

thanks
murali
Post #558633
Posted Tuesday, August 26, 2008 1:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:42 AM
Points: 6,731, Visits: 8,480
Inspect your data !

Your example works perfect as shown next.
create table #mytb1  (col1 nvarchar(50) not null);

insert into #mytb1
Select '500910000000000000'
union all Select '500903000000000000'
union all Select '500909000000000000'
union all Select '500903000000000000'
union all Select '500805000000000000'
union all Select '500911000000000000'
union all Select '500905000000000000'
union all Select '500911000000000000'
union all Select '500905000000000000'
union all Select '500911000000000000';
-- (10 row(s) affected)

Alter table #mytb1
add colnew bigint null ;
go

update #mytb1
set colnew = cast( col1 as bigint )
where colnew is null ;
-- (10 row(s) affected)

drop table #mytb1;



Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #558669
Posted Tuesday, August 26, 2008 6:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 16, 2009 6:05 AM
Points: 39, Visits: 99
Hi ALZ

i did what u said but it did affect any row it shows

0 affected rows

my old column is (col1 nvarchar(50) null)
my new column is (newcol bigint null)

but u taken old column (col1 nvarchar(50) not null)

thanks for help
murali
Post #558795
Posted Tuesday, August 26, 2008 6:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:42 AM
Points: 6,731, Visits: 8,480
Did you test the exact example code I posted ?

Can you post the results of this query ?
select count(*)  as newcol_NULL_count
from yourtable
where newcol is null ;



Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #558820
Posted Tuesday, August 26, 2008 8:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 16, 2009 6:05 AM
Points: 39, Visits: 99
Hi ALZ


1.select count(*) as newcol_NULL_count
from yourtable
where newcol is null ;

if i implement above query it shows the follwing error
Incorrect syntax near '_NULL_Count'.

and i changed the query

2.select count(*) as newcol from yourtable where newcol is null;

it shows

newcol
------
5999
Post #558958
Posted Tuesday, August 26, 2008 10:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:42 AM
Points: 6,731, Visits: 8,480
strange, now it finds 5999 rows where newcol is null.

So the update statement should have target rows to modify.

update yourtable
set newcol = oldcol
where newcol is null



Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #559038
Posted Tuesday, August 26, 2008 10:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 16, 2009 6:05 AM
Points: 39, Visits: 99
Hi ALZ

when i test my original data

at the time alter table it show below warning

Warning: The table "samples" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

thats y it didnt convert

tell my any other way
Post #559327
Posted Wednesday, August 27, 2008 2:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:42 AM
Points: 6,731, Visits: 8,480
You should first check your original column for "non convertable" content.

check http://www.sqlservercentral.com/Forums/Topic533291-5-1.aspx#bm533528


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #559430
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse