none
Ampersand causes overflow error? RRS feed

  • Question

  • Hit a very odd problem today and I wonder if this is just me: had a working SSIS package that loads invoice header records into an accounting database, with no issues. OLEDB source from SQL Server to an OLEDB Command component, which calls a stored proc to perform the inserts.

    This week, one invoice row came along that refuses to load, and gives this error:

    Error Code: -1071607699
    Column: 0
    Message: Conversion failed because the data value overflowed the type used by the provider. 

    So, I am looking at numbers and dates in the data (overflow, I am thinking) and everything is super clean. I am stumped.

    Then a buddy of mine, looking over my shoulder, notices that the invoice number column (type varchar(64), DT_STR in SSIS) has this value:

    24032 &24173 &24032 - DMC

    A little strange, but it's text, right?

    He says, "why not remove those ampersand characters and see what happens."

    This fixes the problem. I am at the same time relieved and baffled. Is there a bug in the OLEDB provider that makes it refuse the ampersand character?


    Friday, May 30, 2008 9:42 PM

All replies

  • The & is a bitwise operator in SQL Server. Put a:

    SELECT 24032&24173&24032

     in an SSMS query window - you'll get an integer back. It might be, for some reason, the column is not being treated as a string, but instead a series of integers. Can you trace the SQL generated and see if there are single quotes around the column?

    Saturday, May 31, 2008 12:55 AM
    Moderator
  • in your query, try using either the t-sql CAST or CONVERT functions to explicitly return a string.

     

    hth

    Monday, June 2, 2008 8:16 AM
    Moderator