nchar to numeric - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

nchar to numeric

Author

Topic

Benku
Starting Member

10 Posts

Posted - 2012-07-12 : 11:19:22

Hi,

i have the following sql query working in sql

SELECT [BatchLocation]
FROM [DBxx].[dbo].[tBatchContents]
where [BatchID] = '41102.7768518519'

when i include it into a vbscript and run the script i received the error telling me that an error occured converting nchar to numeric.

the value 41102.7768518519 is coming from a variable from VBscript. so should i write some sort of sql code around it so that sql recognize the expected format? like a replace type of thing?

help is appreciated.

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-12 : 11:35:01

are you converting the BatchLocation column? Are you sure that the problem is in BatchId column?

Benku
Starting Member

10 Posts

Posted - 2012-07-12 : 12:40:32

well the batch ID is the only parameter i am trying to pass on the original sql query so i think this is what is causing te problem.

now if you have any suggestion i am happy to give it a try.

Benku
Starting Member

10 Posts

Posted - 2012-07-12 : 12:42:01

by the way i do get the error at the following line

rs.Open strSQL, cnSS

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-12 : 12:49:31

What is strSQL?
is it
strsql = "SELECT [BatchLocation]
FROM [DBxx].[dbo].[tBatchContents]
where [BatchID] = " & mybatchid

if so then change it to
strsql = "SELECT [BatchLocation]
FROM [DBxx].[dbo].[tBatchContents]
where [BatchID] = '" & mybatchid & "'"

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-12 : 13:04:00

Try the query and tell if you get an error:

SELECT CONVERT(numeric(18,10), [BatchID])
FROM [DBxx].[dbo].[tBatchContents]

PS: sorry my bad english :s

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-12 : 13:21:25

One more test...

SELECT [BatchLocation]
FROM [DBxx].[dbo].[tBatchContents]
where [BatchID] = REPLACE('41102.7768518519', '.', ',')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources