I’ve been messing around with the OleDbProbe module this week, and have hit a couple issues that have been a bit frustrating and cost quite a bit of time. The first issue I ran into is described here, and now I’ll tell you about another problem.
Taking a snippet from the module definition below, we can inspect the registry to set the DatabaseName and ServerName configuration values for the SQL connection string.
<ProbeActionModuleType ID="System.OleDbProbe" Accessibility="Public" PassThrough="false" Batching="false">
<Configuration>
<xsd:element name="ConnectionString" type="xsd:string"/>
<xsd:element name="Query" type="xsd:string" minOccurs="0" maxOccurs="1"/>
<xsd:element name="GetValue" type="xsd:boolean" minOccurs="0" maxOccurs="1"/>
<xsd:element name="IncludeOriginalItem" type="xsd:boolean" minOccurs="0" maxOccurs="1"/>
<xsd:element name="OneRowPerItem" type="xsd:boolean" minOccurs="0" maxOccurs="1"/>
<xsd:element name="DatabaseNameRegLocation" type="xsd:string" minOccurs="0" maxOccurs="1"/>
<xsd:element name="DatabaseServerNameRegLocation" type="xsd:string" minOccurs="0" maxOccurs="1"/>
<xsd:element name="QueryTimeout" type="xsd:integer" minOccurs="0" maxOccurs="1"/>
<xsd:element name="GetFetchTime" type="xsd:boolean" minOccurs="0" maxOccurs="1"/>
</Configuration>
<ModuleImplementation Isolation="Any">
<Native>
<ClassID>B5A35748-86F5-46A3-9BC2-F9A494E36B25</ClassID>
</Native>
</ModuleImplementation>
<OutputType>System.OleDbData</OutputType>
<InputType>System.BaseData</InputType>
</ProbeActionModuleType>
The example registry key path and string name provided on the MSDN page is SOFTWARE\Company\Product\1.0\DatabaseName and SOFTWARE\Company\Product\1.0\ServerName.
Ok – no problem. Good example. The problem is, it’s not an example – the string name must be exactly DatabaseName and ServerName.
It’s not exactly a useful configuration, unless your registry string names happen to conform to the name specified in the native code for the OleDbProbe module (which, of course, is hidden from us). For example, if you want to use this module to query the Operations Manager databases, forget about using this registry configuration option because the string names for either the Operational database or Data Warehouse database do not exactly match what the OleDbModule expects. I can say, without a doubt, this is a design flaw.
If you really want to use a registry location of your choice, you’ll need to add another module before the OleDbProbe to read the registry string name, and then pass that into the connection string element of the OleDbProbe module. (see below for an example)
Here are the events you might see on the target server that attempts to execute the workflow without having the expected registry string names.
- <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
- <System>
<Provider Name="HealthService" />
<EventID Qualifiers="49152">4511</EventID>
<Level>2</Level>
<Task>1</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2016-06-07T02:19:20.000000000Z" />
<EventRecordID>7520157</EventRecordID>
<Channel>Operations Manager</Channel>
<Computer>ms01.scomskills.com</Computer>
<Security />
</System>
- <EventData>
<Data>2012-SP1</Data>
<Data>Your.Workflow</Data>
<Data>ms01.scomskills.com</Data>
<Data>{2C420F32-475D-019B-F7D2-E6F92B60E0C0}</Data>
<Data>OleDbProbe</Data>
<Data>{B5A35748-86F5-46A3-9BC2-F9A494E36B25}</Data>
<Data>The system cannot find the file specified.</Data>
</EventData>
</Event>
- <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
- <System>
<Provider Name="Health Service Modules" />
<EventID Qualifiers="49152">11851</EventID>
<Level>2</Level>
<Task>0</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2016-06-07T02:19:20.000000000Z" />
<EventRecordID>7520156</EventRecordID>
<Channel>Operations Manager</Channel>
<Computer>ms01.scomskills.com</Computer>
<Security />
</System>
- <EventData>
<Data>2012-SP1</Data>
<Data>Your.Workflow</Data>
<Data>ms01.scomskills.com</Data>
<Data>{2C420F32-475D-019B-F7D2-E6F92B60E0C0}</Data>
<Data><Configuration><ConnectionString>Provider=SQLOLEDB;Integrated Security=SSPI</ConnectionString><Query>Your Query</Query><GetValue>true</GetValue><OneRowPerItem>true</OneRowPerItem><DatabaseNameRegLocation>SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup\DataWarehouseDBName</DatabaseNameRegLocation><DatabaseServerNameRegLocation>SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup\DataWarehoueDBServerName</DatabaseServerNameRegLocation><QueryTimeout>60</QueryTimeout><GetFetchTime>false</GetFetchTime></Configuration></Data>
<Data>0x80070002</Data>
<Data>The system cannot find the file specified.</Data>
</EventData>
</Event>
<ProbeAction ID="RegistryProbe" TypeID="Windows!Microsoft.Windows.RegistryProbe">
<ComputerName>$Config/ComputerName$</ComputerName>
<RegistryAttributeDefinitions>
<RegistryAttributeDefinition>
<AttributeName>ServerName</AttributeName>
<Path>SOFTWARE\WhateverYouWant</Path>
<PathType>1</PathType>
<AttributeType>1</AttributeType>
</RegistryAttributeDefinition>
<RegistryAttributeDefinition>
<AttributeName>DatabaseName</AttributeName>
<Path>SOFTWARE\WhateverYouWant</Path>
<PathType>1</PathType>
<AttributeType>1</AttributeType>
</RegistryAttributeDefinition>
</RegistryAttributeDefinitions>
</ProbeAction>
<ProbeAction ID="Query" TypeID="System!System.OleDbProbe">
<ConnectionString>Provider=SQLOLEDB;Server=$Data/Values/ServerName$;Database=$Data/Values/DatabaseName$;Integrated Security=SSPI</ConnectionString>
<Query>$Config/Sql$</Query>
<GetValue>$Config/GetValue$</GetValue>
<OneRowPerItem>true</OneRowPerItem>
<QueryTimeout>$Config/QueryTimeoutSeconds$</QueryTimeout>
<GetFetchTime>false</GetFetchTime>
</ProbeAction>