discovery data manager fails while processing a ddr

seems like a good day to blog about sms.  this happened with sms 2003.  yes, i realize there’s a configmgr.  very soon, i’ll be using it!  :)  for the rest of the unfortunate few, here’s the background:

i received this error in mom today.  this is one of those indicators that should immediately tell you that a great day is about to ensue.

mySMSServer - SMS 2003 Perf Threshold: Site Server DDR Backlog > 10,000 over 3 hours.

SMS Discovery Data Manager: Total DDRs Enqueued: value = 8700. The average over last 12 samples is 5280.83.

that’s just bad.  further investigation showed that the server failed to process ddrs since 11/28/08.  i checked around to see if anything changed, but there wasn’t anything unusual.  so … off to the status messages.  here’s what i found:

Microsoft SQL Server reported SQL message 242, severity 16: [22007][242][Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

now you just have to know that can’t be a good thing.  i presumed it was a generalized error message, checking the table for congruency against a working sms server.  everything looked fine.  off to the logs (ddm.log in particular)…

Update base table: System_DISC : execute sql update System_DISC set ItemKey = 17602, DiscArchKey = 5, User_Name0 = "myUser", User_Domain0 = "myDomain", SMS_UUID_Change_Date0 = "01/01/1601 08:01:44"  where ItemKey = 17602                

*** update System_DISC set ItemKey = 17602, DiscArchKey = 5, User_Name0 = "myUser", User_Domain0 = "myDomain", SMS_UUID_Change_Date0 = "01/01/1601 08:01:44"  where ItemKey = 17602

*** [22007][242][Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

that looks bad, particularly the field for sms_uuid_change_date0.  i tailed this back to the .ddr it was complaining about and found the property.  i just changed 1601 to a value it could accept… like 2008.  it actually happened on two or three.  i happened to see those come in to ddm.log when i did some syntax highlighting to show them in real time.  after making the modification to the ddrs, they processed through.  all ~8000 or so…

yes, you can test this exact scenario!  knowing that it was running a datetime conversion, i ran the following query and got the following response.  looks eerily similar eh?

SELECT convert(datetime,'01/01/1601 08:01:44')

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

there are probably plenty of reasons why discovery data manager will choke.  i know i ran into plenty of articles about it.  interesting that they weren’t tossed out as bad ddrs.  honestly, i have no idea why the date came in as that format.