June 17, 2009 at 11:42 am
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?
June 17, 2009 at 3:29 pm
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: http://msdn.microsoft.com/en-us/library/aa226403(SQL.80).aspx)
On a different forum the usage of OPENQUERY is recommended (seemed to work for oracle).
http://groups.google.de/group/microsoft.public.sqlserver.connect/browse_frm/thread/7511b626120f6b48
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...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply