Import of Security Groups is Failing with Arithmetic overflow error converting expression to data type int.

book

Article ID: 208181

calendar_today

Updated On:

Products

Client Management Suite Management Platform (Formerly known as Notification Server) IT Management Suite Server Management Suite

Issue/Introduction

After upgrading to 8.5 RU4 AD Import is failing when bringing in Security Groups.

 

Error in the SMP Logs:

Unspecified Dataloader SQL Exception encountered for inventory data class 'Security Groups' (e0918ef8-db42-4efe-9e18-565c858de54a). (SQL Exception Number: 8115)

Arithmetic overflow error converting expression to data type int.
Arithmetic overflow error converting expression to data type int.
Arithmetic overflow error converting expression to data type int.
   [System.Data.SqlClient.SqlException @ .Net SqlClient Data Provider]
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Altiris.NS.ResourceManagement.Database.SqlClientConnectionBase.ExecuteSQLNoResults(String query, Int32& rowsAffected)
   at Altiris.NS.ResourceManagement.Database.SqlClientConnectionBase.ExecuteSQLNoResults(String query)
   at Altiris.NS.ResourceManagement.Database.GluedSqlCalls.System.IDisposable.Dispose()
   at Altiris.NS.ResourceManagement.DataClassRowCollection.InventoryLoader.FixResourceUpdateSummaries(DataClass dataClass, NullableDictionary`2 mapResourceInsertInfo, IDataLoaderConnection connection, QueryCacheEntry e)
   at Altiris.NS.ResourceManagement.DataClassRowCollection.InventoryLoader.Update(DataClass dataClass, DataClassRows rows, NullableDictionary`2 mapResourceInsertInfo, Boolean historyEnabled, Dictionary`2 resourceGuidToResourceTypeGuid, IDataLoaderConnection connection, Guid& updateInstanceGuid, List`1& fixedResources)
   at Altiris.NS.ResourceManagement.DataClassRowCollection.Loader.Update(DataClass dataClass, DataClassRows rows, NullableDictionary`2 mapResourceInsertInfo, Boolean historyEnabled, Dictionary`2 resourceGuidToResourceTypeGuid, IDataLoaderConnection connection)
   at Altiris.NS.ResourceManagement.DataClassRowCollection.TryCommit(IDataLoaderConnection connection, Boolean isInvAeXAcClientAgentGuid, ICollection`1 resourcesToActivate, ICollection`1 skipResources)
   at Altiris.NS.ResourceManagement.DataClassRowCollection.<>c__DisplayClass25_0.<Commit>b__0(IDatabaseContext ctx)
   at Altiris.NS.ContextManagement.AdminDatabaseContext.<>c__DisplayClass26_0`1.<PerformTransactedDeadlockRetry>b__0(IDatabaseContext ctx)
   at Altiris.Database.DatabaseContext`1.RetryAction(Int32 retries, Boolean transacted, Getter`1 getContext, Action`1 action, Action`1 retry, DeadlockRetryArgs& re, Exception& exception, Boolean inTransaction)
   at Altiris.Database.DatabaseContext`1.PerformWithDeadlockRetryHelper(Int32 retries, Boolean inTransaction, Getter`1 getContext, Action`1 action, Action`1 retry, Boolean transacted)
   at Altiris.Database.DatabaseContext`1.PerformTransactedDeadlockRetry(Int32 retries, Getter`1 getContext, Action`1 action, Action`1 retry)
   at Altiris.NS.ResourceManagement.DataClassRowCollection.Commit(IDataLoaderConnection connection)
   at Altiris.Database.DatabaseContext`1.RetryAction(Int32 retries, Boolean transacted, Getter`1 getContext, Action`1 action, Action`1 retry, DeadlockRetryArgs& re, Exception& exception, Boolean inTransaction)
   at Altiris.Database.DatabaseContext`1.PerformWithDeadlockRetryHelper(Int32 retries, Boolean inTransaction, Getter`1 getContext, Action`1 action, Action`1 retry, Boolean transacted)
   at Altiris.Database.DatabaseContext`1.PerformWithDeadlockRetry(Int32 retries, Boolean startNewTransaction, Nullable`1 isolationLevel, Boolean independentContext, Action`1 action, Action`1 retry)
   at Altiris.NS.ResourceManagement.DataClassRowCollection.CommitData(IDataLoaderConnection connection)

SQL Exception details: code=8115, line=11, procedure=sp_Inv_Security_Groups_clean

Exception logged from: 
   at Altiris.NS.ContextManagement.AdminDatabaseContext.ReportException(String, String, Exception)
   at Altiris.NS.ResourceManagement.DataClassRowCollection.CommitData(Altiris.NS.ResourceManagement.IDataLoaderConnection)
   at Altiris.NS.ResourceManagement.DataClassImporter+InventoryDataLoader.CommitData(Altiris.NS.ResourceManagement.ImmutableData.ImmutableDataRowCacheTransaction, Altiris.NS.ResourceManagement.DataClassRowCollection)
   at Altiris.NS.ResourceManagement.DataClassImporter+InventoryDataLoader.ServeDataEnd()
   at Altiris.NS.ResourceManagement.DataClassImporter+InventoryDataLoader.Load(Altiris.Profiling.CodeProfiling.CodeProfiler)
   at Altiris.NS.ResourceManagement.DataClassImporter+InventoryDataLoader.Load(Altiris.NS.ResourceManagement.DataClass, System.Xml.XmlReader, System.Guid, Altiris.NS.ResourceManagement.DataClassImporter+ProcessingDirective, Altiris.NS.ResourceManagement.ResourceImporter)
   at Altiris.NS.ResourceManagement.DataClassImporter+InventoryLoader.ServeInventoryDataClassDataNode()
   at Altiris.NS.ResourceManagement.DataClassImporter+InventoryLoader.Load(Altiris.Profiling.CodeProfiling.CodeProfiler)
   at Altiris.NS.ResourceManagement.DataClassImporter.LoadInventory(System.Xml.XmlReader, Boolean, Boolean, System.Guid)
   at Altiris.NS.StandardItems.Messaging.InventoryCaptureItem.ImportDataClassNode(Altiris.NS.ResourceManagement.MessagingResource, System.Xml.XmlTextReader, Altiris.NS.ResourceManagement.ResourceImporter, Altiris.NS.ResourceManagement.DataClassImporter)
   at Altiris.NS.StandardItems.Messaging.InventoryCaptureItem.ImportDataClassData(Altiris.NS.ResourceManagement.MessagingResource, System.Xml.XmlTextReader, Altiris.NS.ResourceManagement.ResourceImporter)
   at Altiris.NS.StandardItems.Messaging.InventoryCaptureItem.OnMessageImpl(String, Boolean)
   at Altiris.NS.StandardItems.Messaging.InventoryCaptureItem.OnMessageInternal(String, Boolean)
   at Altiris.NS.ClientMessaging.EventQueueDispatcher.DispatchWithinContext(String, Boolean, Altiris.NS.ItemManagement.IItem, Altiris.NS.ItemManagement.IItemMessaging, Altiris.NS.ClientMessaging.EventQueueDispatcher+MessageHeader)
   at Altiris.NS.ClientMessaging.EventQueueDispatcher.DispatchWithinContext(Int32, String, Boolean, Int64, Int64, Altiris.NS.ClientMessaging.EventQueueDispatcher+MessageHeader)
   at Altiris.NS.ClientMessaging.EventQueueDispatcher.TryDispatch(Int32, String, Boolean, Int64, Int64)
   at Altiris.NS.ClientMessaging.EventQueueDispatcher.PerformDispatch(Altiris.NS.ClientMessaging.EventQueueDispatcher+DispatchSlot, Boolean&)
   at Altiris.NS.ClientMessaging.EventQueueDispatcher.WorkerDispatchStep(Altiris.NS.ClientMessaging.EventQueueDispatcher+DispatchSlot, Altiris.Common.Threading.IAutoCallContextSnapshot)
   at Altiris.NS.ClientMessaging.EventQueueDispatcher.PerformDispatchSlotLoop(Altiris.NS.ClientMessaging.EventQueueDispatcher+DispatchSlot, String&)
   at Altiris.NS.ClientMessaging.EventQueueDispatcher.PerformDispatchSlotLoop(Altiris.NS.Logging.EventLog+IMeasureOperation, Altiris.NS.ClientMessaging.EventQueueDispatcher+DispatchSlot)
   at Altiris.NS.ClientMessaging.EventQueueDispatcher.DispatchSlotThreadProc(Object)
   at System.Threading.ThreadHelper.ThreadStart(Object)

Cause

The Table Inv_Security_Groups stores _ID column as a BIGINT.  The Stored Procedure (SP) sp_Inv_Security_Groups_clean uses an Int for _ID.  When _ID is > 2.1 billion causes an Arithmetic Overflow in the Stored Procedure.

 

bigint -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) 8 Bytes
int -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) 4 Bytes

Environment

ONLY Valid on 8.5 RU4.

Resolution

First of all check to see if you have this issue by running this SQL Query.

select _id = max( _id ) from dbo.[Inv_Security_Groups]

If the number returned is greater than 2,147,483,647 you will want to update the Stored Procedure

View the existing Stored Procedure:

sp_helptext sp_Inv_Security_Groups_clean 

Validate that it looks like the query below before proceeding.  If the definition of sp_Inv_Security_Groups_clean does not look like this, do not run this query.  The only changes are in BOLD.

Otherwise execute the command below to Update the _ID to a BIGINT.  This will resolve the Arithmetic Overflow error.

 

ALTER proc [dbo].[sp_Inv_Security_Groups_clean]  
@ResourceGuid uniqueidentifier  
as  
 set nocount on  
  
 declare @id bigint,  
   @gn nvarchar(255),  
   @un nvarchar(64)  
  
  select  @id = MAX(_id ), @gn = [Group Name], @un = [User Name]  
   from dbo.[Inv_Security_Groups]  
   where [_ResourceGuid] = @ResourceGuid  
   group by [_ResourceGuid], [Group Name], [User Name]  
   having count(_id) > 1  
  
  if @@rowcount > 0   
   DELETE dbo.[Inv_Security_Groups]  
    WHERE [_ResourceGuid] = @ResourceGuid AND [Group Name] = @gn AND [User Name] = @un  
    AND   [_id] < @id