Is there a way I can add the time in the following query as the body of my email :
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TEST_DEV',
@recipients = '[email protected]',
@query = ' select
Percentage = CONVERT(DECIMAL(10,1),100 - (CAST(COUNT(DISTINCT case when PD.Exception != ' ' then PD.Id END) as float)/CAST(COUNT(PD.Id) as float)*100))
from
DataBaseName.dbo.Product P INNER JOIN DataBaseName.dbo.LogProduct PD
ON P.LogId = PD.LogId
WHERE
ResponseTime < GETDATE() and RequestTime > DATEADD(MINUTE, -150, GETDATE())
' ,
@subject = 'Test',
@body = 'Please check the attached file for Providers with Many unsuccessful calls between the time xx an yy',
@attach_query_result_as_file = 1 ;
In the current line
@body = 'Please check the attached file for info on calls between the time xx an yy',
I would like to add GetDate() in place of xx and DATEADD(MINUTE, -150, GETDATE()) in place of yy ?
is it possible ?
declare @body nvarchar(max)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DEV',
@recipients = '[email protected]',
@query = 'exec Database.dbo.spTest' ,
@subject = 'Test',
select @body = 'Please check the attached file for info on calls between the time ........................',
@attach_query_result_as_file = 1 ;
Would you want me to do something like this ?
You can declare your @body variable before the EXEC statement and make it any string that you'd like.
Edit:
I updated this to be more verbose. I dont have sp_send_dbmail configed anywhere to test, but I think it should work fine. I created a string variable called @bodyMsg, set it to the string you want before the stored procedure call, then gave the value over to the @body variable in sp_send_dbmail.
declare @bodyMsg nvarchar(max)
select @bodyMsg = 'Please check the attached file for info on calls between the time ' + convert(varchar,GETDATE()) + ' and ' + convert(varchar,DATEADD(mm, -150, getdate())) + '.'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TEST_DEV',
@recipients = '[email protected]',
@query = ' select
Percentage = CONVERT(DECIMAL(10,1),100 - (CAST(COUNT(DISTINCT case when PD.Exception != ' ' then PD.Id END) as float)/CAST(COUNT(PD.Id) as float)*100))
from
DataBaseName.dbo.Product P INNER JOIN DataBaseName.dbo.LogProduct PD
ON P.LogId = PD.LogId
WHERE
ResponseTime < GETDATE() and RequestTime > DATEADD(MINUTE, -150, GETDATE())
' ,
@subject = 'Test',
@body = @bodyMsg,
@attach_query_result_as_file = 1 ;
Then just pass the @body variable to the sp_send_dbmail stored procedure. Different datetime formats can be found here: http://technet.microsoft.com/en-us/library/ms187928.aspx