Filter creation Error: Cannot drop the table '#tmpResGuids', because it does not exist or you do not have permission.

Products

• IT Management Suite

Issue/Introduction

When you tried to run a raw SQL query into a filter and got the following error:

Failed to perform a collection membership update on the collection 'New Filter' (e392e89c-723a-4a60-b698-5ae8312d852c). An unhandled exception has occurred. Full membership update failed.

Failed to perform a collection membership update on the collection 'New Filter' (e392e89c-723a-4a60-b698-5ae8312d852c). An unhandled exception has occurred.

Full membership update failed.

   [Altiris.NS.Exceptions.AeXException @ Altiris.NS.StandardItems]

   at Altiris.NS.StandardItems.Collection.NSDataSrcBasedResourceCollection.UpdateMembershipCore(ICollectionUpdateMessage updateMessage)

   at Altiris.NS.StandardItems.Collection.NSResourceCollectionBase.<>c__DisplayClass5.<UpdateMembershipImpl>b__1(IDatabaseContext ac)

   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, IsolationLevel isolationLevel, Boolean independentContext, Action`1 action, Action`1 retry)

   at Altiris.NS.StandardItems.Collection.NSResourceCollectionBase.UpdateMembershipImpl(ICollectionUpdateMessage updateMessage)

 

Cannot drop the table '#tmpResGuids', because it does not exist or you do not have permission.

   [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.DataAccessLayer.DatabaseAbilities.ExecuteNonQuery(String sql)

   at Altiris.NS.StandardItems.Collection.NSDataSrcBasedResourceCollection.FullUpdatePostDataSrcProcessing(DateTime collMembershipUpdateTime)

   at Altiris.NS.StandardItems.Collection.NSDataSrcBasedResourceCollection.FullUpdateMembership()

   at Altiris.NS.StandardItems.Collection.NSDataSrcBasedResourceCollection.UpdateMembershipCore(ICollectionUpdateMessage updateMessage)

 

SQL Exception details: code=3701, line=1

-----------------------------------------------------------------------------------------------------

Process: AeXSvc (2816), Thread ID: 161, Module: AeXSVC.exe

Priority: 1, Source: Altiris.NS.StandardItems.Collection.NSResourceCollectionBase.UpdateMembershipImpl

 

Environment

ITMS 8.x, 8.7.x

Cause

We noticed that the customer was trying to return the following on his raw sql query:

select distinct
vc.[name],
vc.[user],
fd.[Name] as 'File Name',
wf.[productversion],
fd.[path]

Filters only allow to return GUIDs since our code will then grab what the filters need and displayed them in the grid from the returned GUIDs.
After we added the proper return column, the issue was resolved.

Resolution

Add the proper return column.

This was the final query:

 

select distinct vc.GUID
from vcomputer vc
join dbo.inv_installed_file_details fd on fd._resourceguid=vc.Guid
JOIN dbo.Inv_Windows_File wf ON wf.[_ResourceGuid] = FD.[FileResourceGuid]
where fd.[Name] like 'iexplore.exe'
and wf.[ProductVersion] like '10%'
and [Path] not like 'C:\Windows%'
and [Path] like 'C:\Program Files\Internet Explorer'