Hi,
Please help me,
I am writing a code that will be used to send email alerts for some events, the query run buts the mail generated is in blank, no query output, I did not see where I am making the mistake.
Thanks for your ideas and support
DECLARE @Body NVARCHAR(MAX),
@TableHead VARCHAR(1000),
@TableTail VARCHAR(1000),
@EmailSubject VARCHAR(300),
@TableTail2 VARCHAR(1000),
@statement VARCHAR(1000),
@porcentage real,
@comands varchar(32),
@starttime datetime
--'DECLARE @porcentual real'+','+'@comando varchar(32)'+','+'@inicio datetime'
SET @EmailSubject = 'Verificacion del estado del Backup';
SET @TableTail = '</table></body></html>';
SET @Body ='if exists (select percent_complete, command, start_time
from sys.dm_exec_requests
where command in ('+
'''BACKUP LOG ''' + ',' +
'''BACKUP DATABASE''' + ',' +
'''DBCC''' + ','+
'''SELECT''' +'))'+
'select @porcentual=percent_complete, @comando=command, @inicio=start_time from sys.dm_exec_requests where command in ('+
'''BACKUP LOG'''+','+
'''BACKUP DATABASE'''+','+
'''DBCC'''+','+
'''SELECT'''+')'+
'print '+'''Command: '''+'@comands'+''' started at: '''+'cast(@starttime as nvarchar(20))'+''' Porcentage:'''+ 'cast ( @porcentage as nvarchar(20))'
SELECT @Body = @TableHead + ISNULL(@Body, '') + @TableTail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MonitorB',
@recipients = 'lulu.ma123@xyz.com',
@subject=@EmailSubject,
@body=@Body ,
@body_format = 'HTML' ;