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

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 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)

Environment

ONLY Valid on 8.5 RU4.

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

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  

 

Powered by Wolken Software