Issue: on DB Mail

Executed as user: domain\user. Could not find stored procedure ‘msdb.sp_send_dbmail’. [SQLSTATE 42000] (Error 2812). The step failed.

Reason for failure:

Need to use msdb..sp_send_dbmail instead of msdb.dbo.sp_send_dbmail 

Solution:

EXEC msdb..sp_send_dbmail
@profile_name = ‘EMailProfile’,
@recipients = ‘user@domain.com’,
@body = ‘Success – Full Database Backup’,
@subject = ‘Database Free SPACE Report.’
@file_attachments=’E:\TEST\testdbspace.txt’

**************************************

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = 'yourfriend@Adventure-Works.com',
    @body = 'The stored procedure finished successfully.',
    @subject = 'Automated Success Message' ;

B. Sending an e-mail message with the results of a query

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = 'yourfriend@Adventure-Works.com',
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
                  WHERE DueDate > ''2004-04-30''
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

C. Sending an HTML e-mail message

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Work Order Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
    N'<th>Expected Revenue</th></tr>' +
    CAST ( ( SELECT td = wo.WorkOrderID,       '',
                    td = p.ProductID, '',
                    td = p.Name, '',
                    td = wo.OrderQty, '',
                    td = wo.DueDate, '',
                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty
              FROM AdventureWorks.Production.WorkOrder as wo
              JOIN AdventureWorks.Production.Product AS p
              ON wo.ProductID = p.ProductID
              WHERE DueDate > '2004-04-30'
                AND DATEDIFF(dd, '2004-04-30', DueDate) < 2 
              ORDER BY DueDate ASC,
                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='yourfriend@Adventure-Works.com',
    @subject = 'Work Order List',
    @body = @tableHTML,
    @body_format = 'HTML' ;

 

Reference:
https://msdn.microsoft.com/en-IN/library/ms190307.aspx

 

Advertisements