sql agent issue
-
getting this error.
could not find the procedure msdb.dbo.sp_get_sqlagent_properties
and also this error
cannot find column msdb or user defined function or aggregate msdb.dbo.fn_syspolicy_is_automation_enabled or name is ambiguous.
how to fix this
- This topic was modified 2 weeks, 4 days ago by mtz676.
-
Enable the agent xp's via sp_configure
-
it is enabled
-
Post the exact error message(s) as they are shown on screen.
The general fix for that error is that your agent XP's are not enabled.
Also a copy of the output of
SELECT @@VERSION
Also probably about time you upgraded to a supported version of SQL too, 2014 is now a dead product.
-
ERROR:cannot find either column msdb or user-defined function or aggregate msdb.dbo.fn_syspolicy_is_automation_enabled
When we right click on server/database .... we get that error
-
error attached.
-
If you where to navigate in object explorer as someone with sysadmin rights, do you see that function??
-
yes. we see the function. but we do not have sysadmin.
SELECT roles.principal_id AS RolePrincipalID
, roles.name AS RolePrincipalName
, server_role_members.member_principal_id AS MemberPrincipalID
, members.name AS MemberPrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.server_principals AS members
ON server_role_members.member_principal_id = members.principal_id
it does not list sysadmin
-
Unsure then, if your doing whatever functionality it is as a sysadmin account and the function is there, something is for sure a miss.
If you're not using policy based management, safe to ignore, but if you are, time to get an upgrade to 2022 planned in sharpish.
-
ok..but we are unable to see sqlagent.how can that be fixed
-
Will probably have to repair the instance.
Might be worth getting a consultant in to do a deep dive into the server, it's not something which can easily be done on a forum.
- mtz676 wrote:
yes. we see the function. but we do not have sysadmin.
SELECT roles.principal_id AS RolePrincipalID , roles.name AS RolePrincipalName , server_role_members.member_principal_id AS MemberPrincipalID , members.name AS MemberPrincipalName FROM sys.server_role_members AS server_role_members INNER JOIN sys.server_principals AS roles ON server_role_members.role_principal_id = roles.principal_id INNER JOIN sys.server_principals AS members ON server_role_members.member_principal_id = members.principal_id
it does not list sysadmin
If you're not sysadmin (member), your sysadmin needs to grant your msdb authority to be able to use these objects.
(and these auth are not covered by SQLAgentOperatorRole! )
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
launch Sql Server Configuration Manager, Do you see SQL Agent running?
-
yes
-
run both.
SELECT *
FROM sys.objects
WHERE name = 'fn_syspolicy_is_automation_enabled' AND type = 'FN';
SELECT msdb.dbo.fn_syspolicy_is_automation_enabled() AS IsAutomationEnabled;
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply