SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


narrowing down the cause of the failure


narrowing down the cause of the failure

Author
Message
middletree
middletree
Right there with Babe
Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)

Group: General Forum Members
Points: 718 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?
LutzM
LutzM
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23953 Visits: 13559
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
http://groups.google.de/group/microsoft.public.sqlserver.odbc/browse_frm/thread/c8a3123ae2a4cc1f/98b39d5c04687ea3

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...



Lutz
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search