OleDbProbe – VariantType=”0” – Empty

Today I found an issue with the OleDbProbe module in which I found no solution in my searches, so it was one of those cases where I needed to step through various debugging tools and scour through code to solve the problem. It’s can be rewarding, because it gives me something to write about, but it sure can be time consuming. I’ve used this module many times in the past, but this is the first time I’ve seen odd behavior in the results.

The Problem

I had plugged my query into the module and simulated the workflow, and the results of each column was <Column VariantType=”0” />, as shown below.

- <DataItems>
- <DataItem type="System.OleDbData" time="2016-06-04T14:15:45.9305344-05:00" sourceHealthServiceId="D4E9691B-9F54-0F31-786C-BAF110FB769F">
<HRResult>0</HRResult>
<ResultLength>8</ResultLength>
<Result>Success</Result>
<InitializationTime>909</InitializationTime>
<OpenTime>0</OpenTime>
<ExecutionTime>20</ExecutionTime>
<FetchTime>0</FetchTime>
<RowLength>5</RowLength>
- <Columns>
<Column VariantType="0" />
</Columns>
- <Columns>
<Column VariantType="0" />
</Columns>
- <Columns>
<Column VariantType="0" />
</Columns>
- <Columns>
<Column VariantType="0" />
</Columns>
- <Columns>
<Column VariantType="0" />
</Columns>
<OriginalDataLength>0</OriginalDataLength>
<ErrorDescriptionLength>0</ErrorDescriptionLength>
<ResultCode>0</ResultCode>
</DataItem>
</DataItems>

As we can see from the results, the query succeeded. One would assume with a successful query comes valid and filled columns. Wondering why the results were all empty, I expanded the query selection to include all columns, and what I found was quite interesting. Below is just the columns section. As we can see here, about half the columns filled with data and with their corresponding types. And about half did not fill, with VariantType=”0”, which means it’s empty.

<Columns>
<Column VariantType="3">147</Column>
<Column VariantType="3">1</Column>
<Column VariantType="8">{A6B2A91A-BA8E-6D80-06BD-3F12BF28652D}</Column>
<Column VariantType="3">64</Column>
<Column VariantType="3">147</Column>
<Column VariantType="0" />
<Column VariantType="0" />
<Column VariantType="0" />
<Column VariantType="0" />
<Column VariantType="0" />
<Column VariantType="0" />
<Column VariantType="3">64</Column>
<Column VariantType="8">{EA99500D-8D52-FC52-B5A5-10DCD1E9D2BD}</Column>
<Column VariantType="3">8</Column>
<Column VariantType="8">Microsoft.Windows.Computer</Column>
<Column VariantType="8">Windows Computer</Column>
<Column VariantType="0" />
<Column VariantType="3">273</Column>
<Column VariantType="3">147</Column>
<Column VariantType="0" />
<Column VariantType="0" />
<Column VariantType="0" />
<Column VariantType="1" />
<Column VariantType="0" />
<Column VariantType="0" />
</Columns>

I thought there might be some sort of pattern to this, but I haven’t discovered it. I looked at the column properties in the database, and did not find anything resembling a consistent pattern that would indicate any reason why some columns are filled during execution and some are not. I gave up on trying to answer the question of “why” some columns refuse to fill, and found the solution by casting in the sql SELECT statement.

The Solution

The problem was solved by casting the columns that did not fill in the SELECT statement.

Here is the first query that returned empty columns.

SELECT me.Name
FROM vManagedEntity AS me
inner join vManagedEntityType AS met ON met.ManagedEntityTypeRowId = me.ManagedEntityTypeRowId
inner join vManagedEntityProperty AS mep ON mep.ManagedEntityRowId = me.ManagedEntityRowId
WHERE met.ManagedEntityTypeSystemName = 'Microsoft.Windows.Computer' AND
mep.ToDateTime IS NULL

And here is the modified query with CAST in the SELECT statement.

SELECT CAST (me.Name as NVARCHAR)
FROM vManagedEntity AS me
inner join vManagedEntityType AS met ON met.ManagedEntityTypeRowId = me.ManagedEntityTypeRowId
inner join vManagedEntityProperty AS mep ON mep.ManagedEntityRowId = me.ManagedEntityRowId
WHERE met.ManagedEntityTypeSystemName = 'Microsoft.Windows.Computer' AND
mep.ToDateTime IS NULL

That’s it folks. Hope this helps if you find yourself in the same bind as me.

2 thoughts on “OleDbProbe – VariantType=”0” – Empty”

  1. Hello Jonathan,

    I have the same Issue but when we use the cast solution it seem shrink the data in the field.

    Do you Know if the Column was limited??

  2. Try nvarchar(max)? I do believe there is an inherent max payload size built into the module, but I don’t recall what it is. If you’ve got columns that are more than, say, 500 characters, then you just might be reaching the module limitations, not the actual TSQL nvarchar(max) limitations…

Comments welcome (links require moderation)