Error 8114

• Richard D. Cushing

  Ten Centuries

  Points: 1296

  More actions

  January 7, 2005 at 9:19 am

  #102340

  We have a client that has been running a particular Transaction import process without a hitch since about 29 August 2004.  Suddenly, on 29 December 2004, it decided to start failing.  Nothing has changed in the file format that it is processing, and the process is throwing out some red-herring excuse (error) about NULL not being accepted in the account segment override (from the accounting software to which it is uploading).

   

  However, a trace we ran shows a data exception error

  Exception Error: 8114, Severity: 16, State: 2 Mas500 Application Interface  SSSI     1828 90 2005-01-05 14:16:15.230

   

  After the error, one of the transaction API's return values is garbage.

   

  When we look it up in SQL Server help, it indicates that maybe a table in the in database has a date outside the SQL server date range (see below).

   

  Error 8114

  Severity Level 16

  Message Text

  Error converting data type %ls to %ls.

   

  Explanation

  If the error text refers to both DBTYPE_DATE and datetime, respectively, this error message indicates that a DBTYPE_DATE OLE DB data type column from a remote table could not be converted to a datetime value in Microsoft® SQL Server™. The DBTYPE_DATE column most likely has a value outside the range supported by the datetime data type (datetime values must be from January 1, 1753, through December 31, 9999). Because the range of values of the DBTYPE_DATE data type is larger than that of the SQL Server datetime data type, such errors can occur if the column contains values outside of the range supported by SQL Server.

   

  Action

  Remove the remote table column of DBTYPE_DATE data type from the query select list or predicate list.

   

   

  When, however, we look at the underlying data there does not appear to be any dates out-of-range or any other apparent issues with the data.

   

   

  Have you ever heard of SQL Server acting like this in any of your forum participation?  Any ideas about what would make this suddenly start to happen after months of normal operation?

   

  If it makes any difference, the client is running VERITAS Backup Exec with SQL Server extensions and, curiously, it started throwing out some exceptions during its backups on the MAS 500 databases on exactly the same date that our process went awry.

   

  Ideas???

  
  Richard D. Cushing
  Sr. Consultant
  SYNGERGISTIC SOFTWARE SOLUTIONS, LLC

• Kathi Kellenberger

  SSChampion

  Points: 11811

  More actions

  January 9, 2005 at 2:49 am

  #536580

  Take a look at the Language setting at the server level, it determines the date format.  US date format is month before day: today is 1/9/2005 or 2005-01-09.  I notice in your question you are using day first.

  Also, is your import process using DTS?  If so, check to see if any transform formatting the dates is defined that may be incorrect.

  The error message is not saying that a value in the database is out of range, it is saying that a value you are trying to import is out of range.

   

   

   

  Aunt Kathi Data Platform MVP
  Author of Expert T-SQL Window Functions
  Simple-Talk Editor