Software Inventory is appending "00" to two digit year install dates on Windows Patches. Thus the NSEs fail to process with error "CommitBatch: Couldn't update table dbo.[Inv_SW_Patch_Windows].".

book

Article ID: 157912

calendar_today

Updated On:

Products

Inventory Solution

Issue/Introduction

Some of the Microsoft patches show an installed date with a two digit year such as "10" for "2010" in the registry. When Inventory runs it populates the NSE with a four digit year by prepending "00" to the two digit year, such as "0010". When the data loader attempts to load that NSE it fails because the InstallDate column in the Inv_SW_Patch_Windows table expects a "datetime" format which requires the year to be between 1753 and 9999.
 

The NSEs have several entries like:
<row c0="KB971513" c1="Update for Microsoft Windows (KB971513)" c2="tomasv02" c3="0010-11-12T00:00:00" c4="SP10" c5="Update" c6="SOFTWARE\Microsoft\Updates\Windows Server 2003\SP10\KB971513" hash="1B2M2Y8AsgTpgAmY7PhCfg=="/>

These are all for the SW Patch Windows dataclass, Inv_SW_Patch_Windows table. The column 3 entry is the the "Installed Date".
The lines in the NSE have a entry for the installed date as "c3="0010-11-12T00:00:00"". The "0010" is the install year.

When the server attempts to process the NSE the a.logs show the following error:
Process: AeXSvc (13984)
Thread: 56
Module: AeXSVC.exe
Source:
Altiris.NS.ResourceManagement.Database.BatchSqlClientInserter.CommitBatch
Description: CommitBatch: Couldn't update table dbo.[Inv_SW_Patch_Windows].

( Exception Details: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

 

 

Severity: 1
Date: 01-Apr-13 09:21:08
Tick Count: 1207762376
Host Name: xxxxxx
Process: AeXSvc (13984)
Thread: 56
Module: AeXSVC.exe
Source: Altiris.NS.ResourceManagement.Database.BatchSqlClientInserter.CommitBatch
Description: CommitBatch: Couldn't update table dbo.[Inv_SW_Patch_Windows].
**CEDUrlStart** :http://entced.symantec.com/entt?product=SMP?version=Unknown?language=en?module=i6RlT5KD4rLKHg+eRJT1v3VyYQYOuYFhnmVjmA5/PBsNLs5iRTWKpVzBhjsel2kH?error=919441469?build=**CEDUrlEnd**


( Exception Details: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
   at System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value)
   at System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value)
   at System.Data.SqlClient.MetaType.FromDateTime(DateTime dateTime, Byte cb)
   at System.Data.SqlClient.TdsParser.WriteValue(Object value, MetaType type, Byte scale, Int32 actualLength, Int32 encodingByteSize, Int32 offset, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.WriteBulkCopyValue(Object value, SqlMetaDataPriv metadata, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
   at Altiris.NS.ResourceManagement.Database.BatchSqlClientInserter.CommitBatch() )
( Exception logged from:
   at Altiris.Diagnostics.Logging.EventLog.ReportException(Int32 severity, String strMessage, String category, Exception exception)
   at Altiris.NS.Logging.EventLog.ReportException(Int32 severity, String strMessage, String category, Exception exception)
   at Altiris.NS.ResourceManagement.Database.BatchSqlClientInserter.CommitBatch()
   at Altiris.NS.ResourceManagement.Database.SqlClientInserter.CommitBatch()
   at Altiris.NS.ResourceManagement.DataClassRowCollection.InternalTableInserterImpl(IDataLoaderConnection connection, Hashtable resourceDLInfoHT, TargetTable targetTable, String strInserterTable, DataClass dataClass, DataClassRows dataClassRows)
   at Altiris.NS.ResourceManagement.DataClassRowCollection.InternalTableInserter(IDataLoaderConnection connection, Hashtable resourceDLInfoHT, TargetTable targetTable, String strInserterTable, DataClass dataClass, DataClassRows dataClassRows)
   at Altiris.NS.ResourceManagement.DataClassRowCollection.UpdateDataClass_Impl(DataClass dataClass, DataClassRows rows, Hashtable resourceDLInfoHT, Boolean HistoryEnabled, Hashtable resourceGuidToResourceTypeGuid, IDataLoaderConnection connection)
   at Altiris.NS.ResourceManagement.DataClassRowCollection.<>c__DisplayClasse.<UpdateDataClass>b__a(IDatabaseContext context)
   at Altiris.Database.DatabaseContext`1.PerformWithDeadlockRetryHelper(Int32 retries, Boolean inTransaction, Getter`1 getContext, Action`1 action, Action`1 retry)
   at Altiris.Database.DatabaseContext`1.PerformWithDeadlockRetry(Int32 retries, Boolean startNewTransaction, IsolationLevel isolationLevel, Boolean independentContext, Action`1 action, Action`1 retry)
   at Altiris.Database.DatabaseContext`1.PerformWithDeadlockRetry(Int32 retries, Boolean startNewTransaction, Action`1 action, Action`1 retry)
   at Altiris.NS.ResourceManagement.DataClassRowCollection.UpdateDataClass(DataClass dataClass, DataClassRows rows, Hashtable resourceDLInfoHT, Boolean HistoryEnabled, Hashtable resourceGuidToResourceTypeGuid, IDataLoaderConnection connection)
   at Altiris.NS.ResourceManagement.DataClassRowCollection.CommitData_Impl(IDataLoaderConnection connection)
   at Altiris.NS.ResourceManagement.DataClassRowCollection.<>c__DisplayClass4.<CommitData>b__0(IDatabaseContext context)
   at Altiris.Database.DatabaseContext`1.PerformWithDeadlockRetryHelper(Int32 retries, Boolean inTransaction, Getter`1 getContext, Action`1 action, Action`1 retry)
   at Altiris.Database.DatabaseContext`1.PerformWithDeadlockRetry(Int32 retries, Boolean startNewTransaction, IsolationLevel isolationLevel, Boolean independentContext, Action`1 action, Action`1 retry)
   at Altiris.Database.DatabaseContext`1.PerformWithDeadlockRetry(Int32 retries, Boolean startNewTransaction, Action`1 action, Action`1 retry)
   at Altiris.NS.ResourceManagement.DataClassRowCollection.CommitData(IDataLoaderConnection connection)
   at Altiris.NS.ResourceManagement.DataClassImporter.CommitData(ImmutableDataRowCacheTransaction imu, DataClassRowCollection rows)
   at Altiris.NS.ResourceManagement.DataClassImporter.ProcessData(DataClass dataClass, XmlReader reader, Guid resourceGuidOverride, ProcessingDirective processingDirective)
   at Altiris.NS.ResourceManagement.DataClassImporter.LoadInventory_Impl(XmlReader reader, Boolean bProcessData, Boolean bForceSchemaValidation, Guid resourceGuidOverride)
   at Altiris.NS.ResourceManagement.DataClassImporter.LoadInventory(Guid resourceGuid, XmlReader reader)
   at Altiris.NS.StandardItems.Messaging.InventoryCaptureItem.OnMessage_Impl(String message, Boolean useFilename)
   at Altiris.NS.StandardItems.Messaging.InventoryCaptureItem.OnMessageInternal(String message, Boolean useFilename)
   at Altiris.Inventory.InvBasicInventoryItem.OnMessage(String message)
   at Altiris.NS.ClientMessaging.EventQueueDispatcher.Dispatch(String message, Boolean useFilename)
   at Altiris.NS.ClientMessaging.EventQueueDispatcher.PerformDispatch(DispatchSlot slot)
   at Altiris.NS.ClientMessaging.EventQueueDispatcher.DispatchSingleNSE(DispatchSlot slot)
   at Altiris.NS.ClientMessaging.EventQueueDispatcher.WorkerLoop(Object deliverySlot)
   at System.Threading.ThreadHelper.ThreadStart(Object obj)
 )
( Extra Details:  Type=System.Data.SqlTypes.SqlTypeException Src=System.Data )

Cause

Currently unknown why we prepend "00" to the year in the installed date if we find a two digit year in the registry.

This has been referred to our Developers.

Resolution

None at this time.

Work-around:

Since this issue stops the Inventory NSE from being processed you can work around this by disabling the collection of the "Patch Windows" data.

1.  On the Inventory policies click on "Advanced".
2.  On the "Data Classes" tab expand "Inventory data classes > Software > Windows".
3.  Clear the check mark from "Patch Windows".
4.  Click "OK" and save the changes.


Applies To

ITMS 7.1 SP2 MP1

Agent Name Product Version Build Number 64 Bit 
Altiris Agent 7.1.15350.8350 15350 0 
Altiris eXpress NS Client 7.1.15350.8350 15350 0 
Altiris Software Update Agent 7.1.7858 7858 0 
Altiris Base Task Handlers 7.1.15350 15350 0 
Altiris Client Task Agent 7.1.15350 15350 0 
Altiris Client Task Scheduling Agent 7.1.15350 15350 0 
Altiris Inventory Agent 7.1.7858 7858 0 
Inventory Rule Agent 7.1.8350 8350 0 
Altiris Server Inventory Agent 7.1.7858 7858 0 
Software Management Framework Agent 7.1.8350 8350 0 
Software Delivery Results Pickup Agent 7.1.7858 7858 0 
Software Management Solution Agent 7.1.7858 7858 0