|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to narrow down the failure message
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? Did you try to apply the ISDATE() function on these columns to select the
rows where this function does not return 1? ISDATE() is not 100% foolproof. But it just may help you identify the row(s). Linchi Show quoteHide quote "JamesWilliams" wrote: > 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? > > "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message I don't know if that will be possible in this case. It's got like a hundred news:943E9396-3BCA-4C90-BFBC-F5B6E810DAF5@microsoft.com... > Did you try to apply the ISDATE() function on these columns to select the > rows where this function does not return 1? ISDATE() is not 100% > foolproof. > But it just may help you identify the row(s). > columns, so the SELECT portion of the job does a SELECT * You can easily replace it with a column list by opening Object Explorer and
dragging the "Columns" node from the tree into a query window. Then you can create a similar SELECT query while selectively adding ISDATE() pseudo-columns to represent each datetime/smalldatetime column. On 6/17/09 3:42 PM, in article 135AED9B-9B3D-44E6-8CF4-F14CB07DC***@microsoft.com, "JamesWilliams" <middlet***@hotmail.com> wrote: Show quoteHide quote > > "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message > news:943E9396-3BCA-4C90-BFBC-F5B6E810DAF5@microsoft.com... >> Did you try to apply the ISDATE() function on these columns to select the >> rows where this function does not return 1? ISDATE() is not 100% >> foolproof. >> But it just may help you identify the row(s). >> > > > I don't know if that will be possible in this case. It's got like a hundred > columns, so the SELECT portion of the job does a SELECT * > If you want to find teh offending rows, 100 columns shouldn't cause you not
to check. YOu can easily generate a SELECT statement with ISDATE() applied to the columns--that are supposded to contain datetime data--in teh WHERE clause by querying syscolumns. Linchi Show quoteHide quote "JamesWilliams" wrote: > > "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message > news:943E9396-3BCA-4C90-BFBC-F5B6E810DAF5@microsoft.com... > > Did you try to apply the ISDATE() function on these columns to select the > > rows where this function does not return 1? ISDATE() is not 100% > > foolproof. > > But it just may help you identify the row(s). > > > > > I don't know if that will be possible in this case. It's got like a hundred > columns, so the SELECT portion of the job does a SELECT * > "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message That makes sense. However, I am doing the SELECT from the Progress Db (the news:156D8AA4-B2FA-43AC-BFB3-896ACE3BCC0E@microsoft.com... > If you want to find teh offending rows, 100 columns shouldn't cause you > not > to check. YOu can easily generate a SELECT statement with ISDATE() applied > to > the columns--that are supposded to contain datetime data--in teh WHERE > clause > by querying syscolumns. > > Linchi > source db). So is ISDATE a standard SQL command that will work across platofrms? I don't knowanything about Progress DB. But if I have to do it, my typical
approach would be to first dump out the data from Progress DB into a simple format (such as a straightforward text file with proper field delimiters), and then process that text file to validate the datetime data. This is efficient because you only need to scan through the text file once, and it gives you ultimate flexibility because you can use whatever programming or scripting language to process that text file. It would be very trivial to apply some regular expressions to find the culprit rather quickly. Linchi Show quoteHide quote "JamesWilliams" wrote: > > "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message > news:156D8AA4-B2FA-43AC-BFB3-896ACE3BCC0E@microsoft.com... > > If you want to find teh offending rows, 100 columns shouldn't cause you > > not > > to check. YOu can easily generate a SELECT statement with ISDATE() applied > > to > > the columns--that are supposded to contain datetime data--in teh WHERE > > clause > > by querying syscolumns. > > > > Linchi > > > > That makes sense. However, I am doing the SELECT from the Progress Db (the > source db). So is ISDATE a standard SQL command that will work across > platofrms? > This is exactly what worked. Specifically, I created a temporary table and
used the ISDATE function and found the source of the problem that way. Thanks very much!  
Other interesting topics
|
|||||||||||||||||||||||
