search cancel

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

book

Article ID: 162905

calendar_today

Updated On:

Products

IT Management Suite

Issue/Introduction

When the customer 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

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

Date: 2/5/2016 10:15:00 AM, Tick Count: 159151062 (1.20:12:31.0620000), Size: 4.37 KB

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

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

 

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

After we added the proper return column, the issue was resolved.
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'