Data Sync error - Error while InsertUserComputerToIntermTable Invalid column name 'Active'

[TeriStevenson]

I'm seeing an error in the data sync logs after the user/computer relationship starts. I do see that the relationships sync but I'm not able to determine why this error occurs. Error while InsertUserComputerToIntermTable Invalid column name 'Active'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.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.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at AppPortal.Business.SqlRepository.ExecuteSQL(SqlConnection connection, String sql, List`1 parameters) at AppPortal.Business.SqlRepository.ExecuteSQL(String sql, List`1 parameters) at AppPortal.Infrastructure.Repositories.BaseCmDeployRepository.InsertUserComputerToIntermTable(DataTable dtDepTechUserComputers)

[CharlesW · Accepted solution]

I seem to remember seeing this before, though I don't recall what originally caused the behavior.. App Portal defines a number of "Types" in the App portal DB, and if the Type is incorrect, then a similar exception would be logged.. Try deleting the type  named TYPE_SccmUserComputerMap, and then sync again, to see if this eliminates the error.. Note that the sync will automatically rebuild the type if you delete it.. The following screen captures shows you were the types are located under the App Portal DB:

I'm assuming that the type in your DB may currently be missing the Active column.. Let me know if deleting the type,  and re-syncing does not resolve the issue.

[CharlesW · Accepted solution]

I seem to remember seeing this before, though I don't recall what originally caused the behavior.. App Portal defines a number of "Types" in the App portal DB, and if the Type is incorrect, then a similar exception would be logged.. Try deleting the type  named TYPE_SccmUserComputerMap, and then sync again, to see if this eliminates the error.. Note that the sync will automatically rebuild the type if you delete it.. The following screen captures shows you were the types are located under the App Portal DB:

I'm assuming that the type in your DB may currently be missing the Active column.. Let me know if deleting the type,  and re-syncing does not resolve the issue.

[TeriStevenson]

I just looked and that column is missing. We're in an IT stand down until further notice but I'll delete that Type and run a sync when we are able to.

[TeriStevenson]

Charles,

The column didn't get rebuilt after deleting the Type and running a data sync.  Should I create it manually if you have the information on what it should look like or what's the best way to get it created.

[CharlesW · Accepted solution]

Can you tell me if you are using a custom user computer map sync query? If so, be sure that you have an active column in the query. For example:

select vrs.ResourceID, vrs.Netbios_Name0 AS MachineName, sc.TopConsoleUser0 as UniqueName,Active0 as Active FROM v_R_User AS vru INNER JOIN v_GS_SYSTEM_CONSOLE_USAGE AS sc ON vru.Unique_User_Name0 = sc.TopConsoleUser0 RIGHT OUTER JOIN v_R_System AS vrs ON sc.ResourceID = vrs.ResourceID

[TeriStevenson]

That was it Charles. Our custom query didn't have the Active portion of the query. I added it, deleted the Type and ran a sync and it rebuilt the Type with the Active column. Thanks again for the assistance on this one.