MDW Data Collection Set - Unable to setup a cached collection

Rob DBA Firth on Tue, 29 Apr 2014 11:25:10

When I try to create a cached collection in MDW I get an error relating to 'RunAsSQLAgentServiceStartSchedule'.

This is on 3 servers, and yet I have set it up correctly on two elsewhere previously.  I have tried many security settings.  This is to both a local and a remote server hosting the MDW database.  The cached collections work just fine. I have set the sql engine service account up a sysadmin and added it to the dc_admin and mdw_admin roles.

Msg 14684, Level 16, State 1, Line 21

Caught error#: 14684, Level: 16, State: 1, in Procedure: sp_syscollector_update_collection_set_internal,

Line: 222, with Message: Caught error#: 14684, Level: 16, State: 1, in Procedure:

sp_syscollector_create_jobs, Line: 251, with Message: Caught error#: 14262, Level: 16,

State: 1, in Procedure: sp_verify_schedule_identifiers, Line: 138, with Message:

The specified @schedule_name ('RunAsSQLAgentServiceStartSchedule') does not exist.

Begin Transaction
Begin Try
EXEC [msdb].[dbo].[sp_syscollector_update_collection_set] @collection_set_id=3, @proxy_name=N'', @collection_mode=0

Commit Transaction;
End Try
Begin Catch
Rollback Transaction;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorNumber INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorProcedure NVARCHAR(200);
SELECT @ErrorLine = ERROR_LINE(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE(),
       @ErrorNumber = ERROR_NUMBER(),
       @ErrorMessage = ERROR_MESSAGE(),
       @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR (14684, @ErrorSeverity, 1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);

End Catch;

GO

 Any ideas anyone?!

Thanks

Rob

Rob