SQL Server Agent will not start
SQL Server (2000) Agent failed to start today and the message in the Event Viewer was
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.
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.
posted by Niall Flanagan at 14:50
0 Comments:
Post a Comment
<< Home