Error converting data type nvarchar to bigint

  • muralikrishna37

    Ten Centuries

    Points: 1233

    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

  • Johan Bijnens

    SSC Guru

    Points: 134254

    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


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

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

    - 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[/url] :alien: but most of the time this is me :hehe:

  • muralikrishna37

    Ten Centuries

    Points: 1233

    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

  • Johan Bijnens

    SSC Guru

    Points: 134254

    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


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

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

    - 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[/url] :alien: but most of the time this is me :hehe:

  • muralikrishna37

    Ten Centuries

    Points: 1233

    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

  • Johan Bijnens

    SSC Guru

    Points: 134254

    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


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

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

    - 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[/url] :alien: but most of the time this is me :hehe:

  • muralikrishna37

    Ten Centuries

    Points: 1233

    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

  • Johan Bijnens

    SSC Guru

    Points: 134254

    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


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

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

    - 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[/url] :alien: but most of the time this is me :hehe:

  • muralikrishna37

    Ten Centuries

    Points: 1233

    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

  • Johan Bijnens

    SSC Guru

    Points: 134254

    You should first check your original column for "non convertable" content.

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

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

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

    - 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[/url] :alien: but most of the time this is me :hehe:

  • muralikrishna37

    Ten Centuries

    Points: 1233

    Hi ALZ

    i didnt guess how to check my original that

    can u give the suggestion

    thanks

    murali

  • Johan Bijnens

    SSC Guru

    Points: 134254

    muralikrishna37 (8/27/2008)


    Hi ALZ

    i didnt guess how to check my original that

    can u give the suggestion

    thanks

    murali

    check the url in my previous reply.

    it has the info.

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

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

    - 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[/url] :alien: but most of the time this is me :hehe:

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply