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?