SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role).
We looked at permissions and policies on the Win2K server and also tried using other administrator ids without success.
Finally, when all else failed, I tried profiler and discovered...
This is caused by setting the msdb compatibility to 7 (sp_dbcmptlevel 'database_name', 70). Specifically, the use of COLLATE fails in a temporary table creation within the sp_sqlagent_has_server_access stored procedure which is called as part of service startup
Just run sp_dbcmptlevel 'database_name', 80 to correct.
Unfortunately, SQL Server 2000 allows you to change the compatibility level on the msdb and, therefore, cause this problem.
No comments:
Post a Comment