Question

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.


Command that is being run:

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


Sponsored



Replies

Alberto Morillo on Tue, 29 Apr 2014 13:25:49


Hello,

Please read the workaround Kendra applied when she had issues creating jobs for data collection.


http://www.littlekendra.com/2010/06/25/saddatacollector/

https://connect.microsoft.com/SQLServer/feedback/details/571046

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com




Rob DBA Firth on Tue, 29 Apr 2014 13:33:27


FYI - Answer:

I verified the schedule it was trying to use did not exist:

use msdb
exec sp_help_schedule @schedule_name ='RunAsSQLAgentServiceStartSchedule'

and then created it manually (note this schedule starts automatically when sql server agent starts).

This enabled me to turn on the cached collections.

Thanks Rob