Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

narrowing down the cause of the failure Expand / Collapse
Posted Wednesday, June 17, 2009 11:42 AM


Group: General Forum Members
Last Login: Thursday, September 9, 2010 7:58 PM
Points: 128, Visits: 195
I have a nightly job which copies the entire contents from a Progress (kinda like Oracle) database to a SQL Server 2005 one. Specifically, I look under SQL Server agent in the tree on the left of SSMS, and open it up and expand the Jobs folder. I right-click the job in the list of jobs, select View History, and see the details.

Since I have been here (a few weeks) this job has failed every time. Nobody else who is currently here knows anything about the job. But all agree it needs to not fail.

It has 17 steps, and the last one is the one that fails. that step calls a stored procedure which copies an entire table over.

Here's the error:

Error converting data type DBTYPE_DBTIMESTAMP to datetime. [SQLSTATE 42000] (Error 8114). The step failed.,00:00:32,16,8114,,,,0


So I looked and found 6 fields in the SQL Server DB which are of the datetime datatype. I looked at the source (Progress) database and couldn't see anything weird in the data contained in those fields. Of course, there are 300000 rows, so I didn't see every one. Still, I did some queries where I did a SELECT, sorted by the name of each field, one at a time, and the lowest value and the highest value always seemed normal. So if a row would have been blank, null, or had some garbage data, I would have seen it in my sorted query.

The only thing I could see was that in a couple of the fields, the source DB would have a value like 2/1/2002. In other words, no time, just the date. But I can't imagine that would be a problem.

I am not sure where to go at this point. Is there any way to make a Failure statement pinpoint the exact item that caused the failure? The exact row, the exact value, anything?
Post #736842
Posted Wednesday, June 17, 2009 3:29 PM



Group: General Forum Members
Last Login: Wednesday, February 10, 2016 11:50 AM
Points: 6,897, Visits: 13,559
I just looked up the error on MSDN, but the recommendation doesn't really help ("Remove the remote table column of DBTYPE_DATE data type from the query select list or predicate list." link:

On a different forum the usage of OPENQUERY is recommended (seemed to work for oracle).

To me it looks more like the root cause is an out-of-range date value in the source table (e.g. typo). But that's just guessing... You could narrow it down by run a query against the source table with ISDATE() for col1 through col6 to figure out which values cannot be converted.

Looking deeper into that last step if it's still required or if it can be eliminated is a totally different story...

A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #737026
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse