How to return a date time value from the OLEDB provider


I already posted about the inability to return a SQL date time from the OLEDB provider in Operations Manager. My proposed workaround was to cast the datetime field to a nvarchar. Alas this will completely lose the semantic of the datetime field now it is just a string localized accordingly to the SQL Server box. Yesterday I had to write a discovery pulling data form a SQL data source and I definitely wanted to have my datetime back.

I returned to the issue and I checked if CU2 had fixed the issue… nope. I submitted a query like this: select name, id, creationtime, sid, dn from sometable. Where Creationtime is a datetime field.

<DataItem type="System.OleDbData" time="2010-05-28T10:03:46.7951322+02:00" sourceHealthServiceId="1153C747-7965-B882-A57E-66FC1FBA66BA">
  <HRResult>0</HRResult>
  <ResultLength>8</ResultLength>
  <Result>Success</Result>
  <InitializationTime>69</InitializationTime>
  <OpenTime>0</OpenTime>
  <ExecutionTime>9704</ExecutionTime>
  <FetchTime>0</FetchTime>
  <RowLength>1</RowLength>
  <Columns>
    <Column VariantType="8">PRE\DC02$</Column>
    <Column VariantType="3">1</Column>
    <Column VariantType="0"></Column>
    <Column VariantType="8">S-1-5-21-4185495620-1739196763-3906522881-1849</Column>
    <Column VariantType="8">CN=DC02,OU=Domain Controllers,DC=pre,DC=lab</Column>
  </Columns>
  <OriginalDataLength>0</OriginalDataLength>
  <ErrorDescriptionLength>0</ErrorDescriptionLength>
  <ResultCode>0</ResultCode>
</DataItem>

As you can see the third column has a VariantType="0" and no value returned as a consequence my discovery won’t discover anything.

This time instead of simply casting the datetime to a string and modifying my class properties froma  datetime to a string, I tried to convert the datetime to a commonly recognized format leaving the class properties in datetime format. I changed the query like this: select name, id, Convert(nvarchar(50), creationtime, 120), sid, dn from sometable. Where the 120 type the default odbc format for dates.

The returned data this time looked like this:

<DataItem type="System.OleDbData" time="2010-05-28T10:03:46.7951322+02:00" sourceHealthServiceId="1153C747-7965-B882-A57E-66FC1FBA66BA">
  <HRResult>0</HRResult>
  <ResultLength>8</ResultLength>
  <Result>Success</Result>
  <InitializationTime>69</InitializationTime>
  <OpenTime>0</OpenTime>
  <ExecutionTime>9704</ExecutionTime>
  <FetchTime>0</FetchTime>
  <RowLength>1</RowLength>
  <Columns>
    <Column VariantType="8">PRE\DC02$</Column>
    <Column VariantType="3">1</Column>
    <Column VariantType="8">2000-01-01 00:00:00</Column>
    <Column VariantType="8">S-1-5-21-4185495620-1739196763-3906522881-1849</Column>
    <Column VariantType="8">CN=DC02,OU=Domain Controllers,DC=pre,DC=lab</Column>
  </Columns>
  <OriginalDataLength>0</OriginalDataLength>
  <ErrorDescriptionLength>0</ErrorDescriptionLength>
  <ResultCode>0</ResultCode>
</DataItem>

And the discovery data mapper successfully converted the string formatted date into a datetime properties. This format should be generic enough to get rid of the localization nightmare.

About these ads
  1. #1 by Alex on July 5, 2010 - 4:00 pm

    Hi,

    i created a similar discovery but from time to time the values of the properties are set back to the inital value and it stays with it (instance is discovered with another discovery and a default date is set. Afterwards the actual time is discoverd via the oledb discovery). I’m using OpsMgr R2 CU2 and SQL Server 2008 SP1 CU7. For now it looks like this happens when the sql server is reebooted. Please drop me a email if you ever faced this issue
    Thank you
    Alex

    • #2 by Daniele Grandini on July 30, 2010 - 4:29 pm

      Hi Alex,
      I never faced the issue you’re experiencing. It sound really strange if the oledb query fails due to the SQL reboot no disocvery data should be returned. The potential issue here is a flip/flop where disocvred entities get deleted and rediscovered. Indeed this is an implication I didn’t think about, it could become annoying at the least.

      -Daniele

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 329 other followers

%d bloggers like this: