sp_get_sqlagent_properties doesnt return all the correct information - by Marshall GSEIT

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 778988 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 2/11/2013 11:39:04 AM
Access Restriction Public


When running EXEC msdb.dbo.sp_get_sqlagent_properties
not all of the information is being returned.  E.g. email_profile.

Sign in to post a comment.
Posted by Maxime Daniou on 7/21/2017 at 6:13 AM
I encounter the same problem as. I need to get the configuration using a script.

I am almost 4 years late but here is how I do that :

DECLARE @email_profile NVARCHAR(64)
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                @email_profile OUTPUT,
SELECT @email_profile

It could help someone else.
Posted by Marshall GSEIT on 11/6/2013 at 12:38 PM
sp_get_sqlagent_properties is _not_ working "By Design".

It is clear when looking at what this sp returns, that some fields are returned, e.g. email_profile, but a value is never provided.

In addition, when you look at the contents of sp_get_sqlagent_properties by selecting "Modify" from the Object Explorer; code is obviously there and is intended to return this information. sp_get_sqlagent_properties just fails to do so.

I can see no evidence for marking this as "By Design".
Posted by Microsoft on 10/18/2013 at 9:44 AM
Hello Marshall.

You should be able to view the properties through the UI. There is no SP available for DBMail.
Posted by Marshall GSEIT on 3/12/2013 at 1:05 PM
Yes, there is an associated Mail profile. The profile has an SMTP account assigned to it. I have verified this is working correctly with a test email.

Instead of using sp_get_sqlagent_properties; what should I use to retrieve the properties of the SQL Agent?

Posted by Microsoft on 3/12/2013 at 8:25 AM
Hello Marshall. This topic has remained inactive for an extended period and will be cleaned up. If you find this is being done in error, simply start a new thread and share relevant information and it will be addressed directly. -Walter A Jokiel, Program Manager, SQL Server
Posted by Sethu Srinivasan on 2/13/2013 at 11:41 AM
Hello Marshall,
Can you check if SQLAgent's notification setting has an associated Database Mail profile?. Could you add more detaisl you the scenario you are working on?

sp_get_sqlagent_properties is not a documented procedure. It is recommended not to rely on undocumented stored procedures in your application.

Sethu Srinivasan [MSFT]
SQL Server