NuRoN Consulting, INC.

Blog > SQL Tools > Databases > Util > Procedures > dbo.usp_DiabloFailedRowDetail_GenerateEmail

Dependencies for Stored procedure: Util.dbo.usp_DiabloFailedRowDetail_GenerateEmail

ReferencingFQN	ReferencingType	ReferencedFQN	ReferencedType	Depth	RedHierarchy
Util.dbo.usp_DiabloFailedRowDetail_GenerateEmail	Stored procedure	msdb.dbo.sp_send_dbmail	Stored procedure	1	[msdb].[dbo].[sp_send_dbmail]
Util.dbo.usp_DiabloFailedRowDetail_GenerateEmail	Stored procedure	Util.dbo.DiabloFailedRowDetail	Table	1	[Util].[dbo].[DiabloFailedRowDetail]
msdb.dbo.sp_send_dbmail	Stored procedure	msdb.dbo.get_principal_id	SQL scalar function	2	[msdb].[dbo].[sp_send_dbmail], [msdb].[dbo].[get_principal_id]
msdb.dbo.sp_send_dbmail	Stored procedure	msdb.dbo.sp_validate_user	Stored procedure	2	[msdb].[dbo].[sp_send_dbmail], [msdb].[dbo].[sp_validate_user]
msdb.dbo.sp_send_dbmail	Stored procedure	msdb.dbo.sysmail_principalprofile	Table	2	[msdb].[dbo].[sp_send_dbmail], [msdb].[dbo].[sysmail_principalprofile]
msdb.dbo.sp_send_dbmail	Stored procedure	msdb.dbo.sysmail_verify_profile_sp	Stored procedure	2	[msdb].[dbo].[sp_send_dbmail], [msdb].[dbo].[sysmail_verify_profile_sp]
msdb.dbo.sysmail_verify_profile_sp	Stored procedure	msdb.dbo.sysmail_profile	Table	3	[msdb].[dbo].[sp_send_dbmail], [msdb].[dbo].[sysmail_verify_profile_sp], [msdb].[dbo].[sysmail_profile]

Stored procedure: Util.dbo.usp_DiabloFailedRowDetail_GenerateEmail

CREATE PROCEDURE usp_DiabloFailedRowDetail_GenerateEmail
AS
SET NOCOUNT ON
DECLARE @Id INT,
    @recipients NVARCHAR(4000),
    @subject NVARCHAR(4000),
    @body NVARCHAR(MAX),
    @SQL NVARCHAR(MAX),
    @Info VARCHAR(MAX),
    @ProcessStartTime DATETIME2(2)

WHILE 1 = 1
    BEGIN
        SELECT  @Id = a.Id,
                @recipients = a.recipients,
                @subject = subject,
                @body = body,
                @SQL = SQL,
                @ProcessStartTime = GETDATE(),
                @Info = ''
        FROM    dbo.DiabloFailedRowDetail a
        INNER JOIN (SELECT TOP 1
                            Id
                    FROM    dbo.DiabloFailedRowDetail
                    WHERE   Processed = 0
                    ORDER BY Id) b ON a.Id = b.Id

        IF @@ROWCOUNT = 1
            BEGIN
                EXEC SP_EXECUTESQL
                    @SQL,
                    N'@Output VARCHAR(MAX) OUTPUT',
                    @Info OUTPUT

                SET @body = @body + '

' + @Info
                EXEC msdb.dbo.sp_send_dbmail
                    @recipients = @recipients,
                    @subject = @subject,
                    @body = @body

                UPDATE  dbo.DiabloFailedRowDetail
                SET     Processed = 1,
                        ProcessStartTime = @ProcessStartTime,
                        ProcessEndTime = GETDATE(),
                        Info = @Info
                WHERE   Id = @id
            END
        ELSE
            BEGIN
                RETURN
            END
    END

Description for Stored procedure: Util.dbo.usp_DiabloFailedRowDetail_GenerateEmail

Todo