question

vivekchandra09 avatar image
vivekchandra09 asked

trouble in creating db mail.

SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online. how to fix it.
db
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You have several answers below. For each of those answers that are helpful you should click on the thumbs up next to the answers so that it turns green. If any one of the answers below lead to a solution to your problem, click on the check mark next to that one answer so that it turns green. This web site works best if you do the voting and marking of answers so that the next person who looks at your question knows what the solution was.
2 Likes 2 ·
thanks Grant
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
There are several stages to setting up DBMail in SQL Server - it's not just a matter of running exec sp_configure 'Database Mail XPs', 1 reconfigure with override You also need to create a mail profile: exec msdb.dbo.sysmail_add_profile_sp @profile_name = 'Some profile name', @description = 'something to explain what this profile is for' Once you've done that, you need to add a mail account. This is the biggie, as it includes details of the SMTP server and various email headers exec msdb.dbo.sysmail_add_account_sp ... Then you associate the profile and the account exec msdb.dbo.sysmail_add_profileaccount_sp ... Once you've done all that, then you might want to add an operator to your server - this can be a DBA mailing list, or an individual - someone who will receive error messages etc exec msdb.dbo.sp_add_operator ... You might want to tell the SQL Agent to use this account exec msdb.dbo.sp_set-sqlagent_properties @databasemail_profile = '...' exec master.dbo.sp_MSsetalertinfo @failsafeoperator = '...', @notificationmethod = 1 exec msdb.dbo.sp_set_sqlagent_properties ... Yes, I've left a lot of blanks here, and some research for you. On the other hand, it's not too difficult to find the information now you've got the SP names and an order in which to do things Good luck!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

neeehar avatar image
neeehar answered
why not use SSIS for sending db mail? Apologizes if my answer is foolish..
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

There was a short discussion about this on the main SSC boards a few years ago: http://www.sqlservercentral.com/Forums/Topic1319274-391-1.aspx
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.