Error converting data type nvarchar to bigint

Question

Post reply

Error converting data type nvarchar to bigint

muralikrishna37

Ten Centuries

Points: 1233
More actions
August 25, 2008 at 10:43 pm

#191484

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

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

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

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

Learn to play, play to learn !

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 but most of the time this is me

muralikrishna37 Ten Centuries Points: 1233 More actions · Answer 2

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: 135369 More actions · Answer 3

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

Learn to play, play to learn !

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 but most of the time this is me

muralikrishna37 Ten Centuries Points: 1233 More actions · Answer 4

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: 135369 More actions · Answer 5

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

Learn to play, play to learn !

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 but most of the time this is me

muralikrishna37 Ten Centuries Points: 1233 More actions · Answer 6

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: 135369 More actions · Answer 7

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

Learn to play, play to learn !

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 but most of the time this is me

muralikrishna37 Ten Centuries Points: 1233 More actions · Answer 8

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: 135369 More actions · Answer 9

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

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

Johan

Learn to play, play to learn !

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 but most of the time this is me

muralikrishna37 Ten Centuries Points: 1233 More actions · Answer 10

Hi ALZ

i didnt guess how to check my original that

can u give the suggestion

thanks

murali

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 11

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

Learn to play, play to learn !

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 but most of the time this is me