Delta Collection update fails to update a collection that uses ADSI OLE link as a Source Component

Products

• IT Management Suite

Issue/Introduction

Delta collection updates fails to update a collection that is using ADSI as a source component.  Essentially, using the SQL below, a collection is created from an OLE linked server connection to the Active Directory domain controller.  To reproduce, perform the following steps:

1. Creating the OLE Link Server in SQL:
   
   EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
   GO
   
   
2. Create the Collection of computers using the following query in a Notification Server collection builder screen:
   
   select Guid from vResource where ResourceTypeGuid in (select ResourceTypeGuid from ResourceTypeHierarchy where BaseResourceTypeGuid='493435f7-3b17-4c4c-b07f-c23e7ab7781f')    
   and Guid in (    
   Select r.guid from vResource r, [Inv_AeX_AC_Identification] i                    
   WHERE i.[_ResourceGuid] = r.[Guid]                          
   and i.[Name] IN (SELECT [Name] FROM OPENQUERY ( ADSI, 'SELECT Name FROM ''LDAP://(enter domain server name here)/DC=(Enter Domain name here),DC=(enter domain type here, ie com,org,local, etc)'' WHERE objectCategory = ''Computer'''))    
   )
   
   
3. Assign the collection to a test task or policy, such as the Hello World sample software delivery task, and run it, so that the collection has to update.
4. Run the Delta Collection Update Scheduled task.
5. You will see the following error in the a.log file:
   
   Module: AltirisNativeHelper.dll
   Source: Altiris.NS.StandardItems.Collection.DeltaCollectionUpdateSchedule
   Description: Error delta updating collection 'ADSI Test Colln': SqlException.
   The '.Net SqlClient Data Provider' has received a severity 16, state 1 error number 7390 on line 11 of procedure '' on server 'HOOK'.
   Error Message:
     'The requested operation could not be performed because the OLE DB provider 'ADSDSOObject' does not support the required transaction interface.'
   Original SqlCommand text:
   >>>
   set nocount on
   declare @now datetime
   set @now = getdate()
   create table [#f37e1bca-3708-4975-a9ac-878a45f430c6](ResourceGuid uniqueidentifier primary key)
   select distinct ResourceGuid as Guid into [#5604d1cb-c17f-49fa-abd7-ee6c9e23a9d6] from  ResourceUpdateSummary where ModifiedDate > @LastRunDate
   create unique clustered index [d3c01f21-a413-468c-923d-58374cc2c4f6] on [#5604d1cb-c17f-49fa-abd7-ee6c9e23a9d6] (Guid)
   select vr.* into #vResource from vResource vr where Guid in (select Guid from [#5604d1cb-c17f-49fa-abd7-ee6c9e23a9d6])
   create clustered index [1b3e28ff-3fb2-4825-965b-11381cc30cbf] on #vResource (Guid)
   
   /* Put in the static resources */
   INSERT INTO [#f37e1bca-3708-4975-a9ac-878a45f430c6](ResourceGuid)
   (    SELECT ResourceGuid FROM CollectionIncludeResource WHERE CollectionGuid = @collectionGuid  
       UNION SELECT ResourceGuid FROM CollectionMembership cm JOIN CollectionIncludeCollection cic ON cm.CollectionGuid = cic.SubCollectionGuid WHERE cic.CollectionGuid = @collectionGuid
   )
   
   UNION
   SELECT w.Guid AS ResourceGuid
   FROM #vResource  w  WHERE (([w].[Guid] in (select Guid from (
   select Guid from #vResource  where ResourceTypeGuid in (select ResourceTypeGuid from ResourceTypeHierarchy where BaseResourceTypeGuid='Computer')  
   and Guid in (  
   Select r.guid from #vResource  r, [Inv_AeX_AC_Identification] i                
   WHERE i.[_ResourceGuid] = r.[Guid]                      
   and i.[Name] IN (
       SELECT [Name]  
       FROM OPENQUERY  
       ( ADSI, 'SELECT Name FROM ''LDAP://stripes-dc/DC=stripes,DC=puck'' WHERE objectCategory = ''Computer''')
   )  
   )
   ) tab)))
   /* remove excluded resources */
   DELETE FROM [#f37e1bca-3708-4975-a9ac-878a45f430c6] WHERE ResourceGuid IN
       (
           SELECT ResourceGuid FROM CollectionExcludeResource
               WHERE CollectionGuid = @collectionGuid
       )
   
   /* remove excluded collections */
   DELETE FROM [#f37e1bca-3708-4975-a9ac-878a45f430c6] WHERE ResourceGuid IN
       ( SELECT cm.ResourceGuid FROM CollectionMembership cm JOIN CollectionExcludeCollection ec
           ON cm.CollectionGuid = ec.SubCollectionGuid
   WHERE ec.CollectionGuid=@collectionGuid )
   drop table #vResource
   
   DELETE m FROM CollectionMembership m
   JOIN [#5604d1cb-c17f-49fa-abd7-ee6c9e23a9d6] d on d.Guid = m.ResourceGuid
   LEFT JOIN [#f37e1bca-3708-4975-a9ac-878a45f430c6] t on t.ResourceGuid = m.ResourceGuid
   WHERE m.CollectionGuid = @collectionGuid and t.ResourceGuid is null
   
   INSERT CollectionMembership (CollectionGuid, ResourceGuid, CreatedDate)
   SELECT @collectionGuid, t.ResourceGuid, GetDate() FROM [#f37e1bca-3708-4975-a9ac-878a45f430c6] t
   LEFT JOIN CollectionMembership m on t.ResourceGuid = m.ResourceGuid and m.CollectionGuid =  @collectionGuid
   WHERE m.ResourceGuid is null
   
   UPDATE Collection SET LastUpdated = GETDATE() where Guid=@collectionGuid
   
   set nocount off
   select @now
   
   <<<
   Parameters:
     Name: '@collectionGuid'     Value: 'ADSI Test Colln'
     Name: '@LastRunDate'     Value: '3/7/2006 11:42:40 AM'
   Stack Trace:
   
   Server stack trace:
      at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
      at System.Data.SqlClient.SqlCommand.ExecuteScalar()
      at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(MethodBase mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
      at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
   
   Exception rethrown at [0]:
      at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
      at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
      at System.Data.SqlClient.SqlCommand.ExecuteScalar()
      at Altiris.NS.StandardItems.Collection.BaseCollection.DeltaUpdateMembership()

Cause

The OLE DB provider does not support serializable transactions in SQL.  In this case, the linked OLE DB provider 'ADSDSOObject' does not support the required transaction interface.  To determine this, the following SQL was run in Query Analyzer.  The SQL is essentially the same as the original SQL used to define the collection, with the Serialization of the transaction in bold:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION

select Guid from vResource where ResourceTypeGuid in (select ResourceTypeGuid from ResourceTypeHierarchy where BaseResourceTypeGuid='493435f7-3b17-4c4c-b07f-c23e7ab7781f')
and Guid in (
Select r.guid from vResource r, [Inv_AeX_AC_Identification] i
WHERE i.[_ResourceGuid] = r.[Guid]
and i.[Name] IN (SELECT [Name] FROM OPENQUERY ( ADSI, 'SELECT Name FROM ''LDAP://stripes-dc/DC=stripes,DC=puck'' WHERE objectCategory = ''Computer''')))

COMMIT TRANSACTION

This gave the following error in Query Analyzer:

Server: Msg 7390, Level 16, State 1, Line 3
The requested operation could not be performed because the OLE DB provider 'ADSDSOObject' does not support the required transaction interface.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' IUnknown::QueryInterface returned 0x80004002]

Resolution

This method of collection generation is unsupported, as are other external database connections for collection generation.  Symantec strongly discourages customers from using external database data as a linked basis for collection generation.

The other 2 collection update procedures work on this type of collection.

Applies To
Notification Server 6.0.5287, and 6.0.6074