'ODBC - Numeric Value out of range'
Help! I am bcp-ing in and out large amounts of data. It is a 2 column table, the 2nd column being a text field of length 16. In this text field is a large amount of XML data, about a page when I look at the text file. It has lots of text with lots of tags. It looks something like this "<Paper value = "hi" <!-- --> etc. etc".
I have no trouble BCP OUT but when I try to BCP IN, I get this error:
Starting copy...
SQLstate = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLstate = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLstate = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLstate = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
Help!
Joyce
'ODBC - Numeric Value out of range' (reply)
<<Invalid character value for cast specification>>
This is a conversion error. Basically you are trying to take text and shove it into a numeric field.
What's field 1?
Perhaps it's a numeric id and you are passing the text to it instead?
------------
Joyce at 3/9/00 3:56:50 PM
Help! I am bcp-ing in and out large amounts of data. It is a 2 column table, the 2nd column being a text field of length 16. In this text field is a large amount of XML data, about a page when I look at the text file. It has lots of text with lots of tags. It looks something like this "<Paper value = "hi" <!-- --> etc. etc".
I have no trouble BCP OUT but when I try to BCP IN, I get this error:
Starting copy...
SQLstate = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLstate = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLstate = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLstate = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
Help!
Joyce
'ODBC - Numeric Value out of range' (reply)
I was able to find out what was causing the error but dont' have a solution. You are right. What it is doing is trying to insert text into an integer field. Why it is doing that is because it is interpreting the text incorrectly. I have a table with 2 fields
Create Table{
id int
comments text}
Much of the data in the Comments field already has carriage return in it. When I use bcp out -c, it uses
as a new record. Hence, when it detects the carriage return in the user data(comments) itself, it is pushing the rest of the the text to the 2nd line. Then, when I try to BCP IN, it is trying to push it into ID column!
The text file looks like this:
ID COMMENTS
-- ---------
1 This is a very long comment with a line return now.
This should be part of the previous record but gets detected as a new row
2 Some Text
3 Some Text
So, in the BCP IN, it is trying to put 'This should be part' into the ID column and I am getting an error.
Is there any way I can not get BCP to recognize
in user data as end of a record??
Thanks
Joyce
at 3/9/00 5:32:16 PM
<<Invalid character value for cast specification>>
This is a conversion error. Basically you are trying to take text and shove it into a numeric field.
What's field 1?
Perhaps it's a numeric id and you are passing the text to it instead?
------------
Joyce at 3/9/00 3:56:50 PM
Help! I am bcp-ing in and out large amounts of data. It is a 2 column table, the 2nd column being a text field of length 16. In this text field is a large amount of XML data, about a page when I look at the text file. It has lots of text with lots of tags. It looks something like this "<Paper value = "hi" <!-- --> etc. etc".
I have no trouble BCP OUT but when I try to BCP IN, I get this error:
Starting copy...
SQLstate = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLstate = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLstate = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLstate = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
Help!
Joyce
