CODEWITHSUNDEEP
sqlsql-server-2008t-sqlsql-server-2008-r2
peter
peter

Reputation: 2516

Sending the Sproc results to an Email in SQL Server

I am trying to send an email of the Sproc results. I have tried doing this:

EXEC msdb.dbo.sp_send_dbmail  
    @recipients = '[email protected]',
    @query = 'EXEC test_email' ,
    @subject = 'Sample Data',
    @attach_query_result_as_file = 1 ;

It gives me the following error:

Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
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.

Is there any other way to do this?

Upvotes: 1

Views: 8502

Answers (1)

Pete Carter
Pete Carter

Reputation: 2731

No other way of using DBMail Im afraid. Database Mail needs to be enabled with: EXEC sp_configure 'Database Mail XPs', 1 But additionally, Database Mail needs to be configured with an appropriate Profile and account.

One work around I have used for servers where the policy is not to enable mail is to have the stored procedure call a SSIS package that runs the query and sends the email. This bypasses Database Mail entirely and the SSIS package makes its own connection to the SMTP server.

The way I normally set this up is to run sp_startjob to call a Server Agent job, which then runs the SSIS package.

Upvotes: 4

Related Questions