Results 1 to 3 of 3

Thread: 'ODBC - Numeric Value out of range'

  1. #1
    Joyce Guest

    '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 &#34;<Paper value = &#34;hi&#34; <!-- --> etc. etc&#34;.

    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



  2. #2
    Guest

    '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&#39;s field 1?

    Perhaps it&#39;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 &#34;<Paper value = &#34;hi&#34; <!-- --> etc. etc&#34;.

    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



  3. #3
    Joyce Guest

    'ODBC - Numeric Value out of range' (reply)

    I was able to find out what was causing the error but dont&#39; 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 &#39;This should be part&#39; 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&#39;s field 1?

    Perhaps it&#39;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 &#34;<Paper value = &#34;hi&#34; <!-- --> etc. etc&#34;.

    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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •