June 24, 2011 at 8:31 am
Hi,
I am scheduling automated reports on sql server 2008. The issue I have is that when the query fetches no data. blank report is sent to the mailing list.
My requirement is that instead of sending blank report I need a message like ' No report for today' to be sent. How can I do this ?
Is it possible to not send the report if no data is fetched.
Regards,
Nithin
June 24, 2011 at 9:25 am
Yes, pass the query result into a variable then use a case statement...
Something like:
case when @varCheck is null then 'No results for today...' else...
gsc_dba
June 24, 2011 at 9:30 am
Thanks to the solution :-):-):-).
How can I avoid sending the report if no data is fetched?
June 24, 2011 at 9:31 am
I only post this as it is related to your request and although it may be more complicated, it does allow for more functionality... 🙂
Go HERE ->http://www.sqlservercentral.com/articles/2824/
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 24, 2011 at 9:32 am
In our case report to be mailed was already written down as a text file on the server.
To solve the no-blank-report issue we just checked the size of the file, we knew the size of an empty report containing only the titles so mailer storedproc was able to decide what to do based on size of the file.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 24, 2011 at 9:35 am
You could adapt this to your needs:
DECLARE @varCheck BIT
IF EXISTS ( SELECT *
FROM YourResultTable
WHERE YourCriteria = 'True/False' )
PRINT 'It exists'
SET @varCheck = 1
ELSE
PRINT 'It doesnt exist'
SET @varCheck = 0
You can then check if varCheck is 1 or 0 and do an action for each...
HTH
gsc_dba
June 24, 2011 at 10:11 am
Thanks a loooooooooooooooooooooooooot:-):-):-):-):-) people for the help provided.
June 25, 2011 at 8:04 am
This is the script for the database mail i use. Where should i put the conditions in the script which will prevent the mail from being sent. All I need is If the report is blank i dont want the report to be sent.
Sorry for the trouble as I have very little programming knowledge.
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
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @recipients='nithin@mystifly.com',
@profile_name = 'Test2',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML' ;
June 27, 2011 at 3:04 am
Try this - uncomment the exec mail for each for testing...
USE [AdventureWorks]
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @varCheck BIT
IF EXISTS ( SELECT *
FROM AdventureWorks.Production.WorkOrder AS wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
--WHERE [p].[Class] = 'Nonsense'
)
SET @varCheck = 1
ELSE
SET @varCheck = 0
BEGIN
IF @varCheck = 1
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
FOR
XML PATH('tr')
, TYPE
) AS NVARCHAR(MAX)) + N'</table>' ;
PRINT @tableHTML
/*
EXEC msdb.dbo.sp_send_dbmail @recipients = 'nm', @profile_name = 'Test2', @subject = 'Work Order List',
@body = @tableHTML, @body_format = 'HTML' ;
*/
END
IF @varCheck = 0
PRINT 'Nothing to send...'
/*
EXEC msdb.dbo.sp_send_dbmail @recipients = 'nm', @profile_name = 'Test2', @subject = 'Work Order List',
@body = 'No data available', @body_format = 'HTML' ;
*/
gsc_dba
June 27, 2011 at 3:12 am
You could also circumvent the @varCheck by setting the @tableHTML = your result set when exists = true
And set @tableHTML to be your "No data available..." when exists = false...
Prob more efficient:
USE [AdventureWorks]
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @varCheck BIT
IF EXISTS ( SELECT *
FROM AdventureWorks.Production.WorkOrder AS wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
/*Comment and uncomment this line for testing*/
--WHERE [p].[Class] = 'Nonsense'
)
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
FOR
XML PATH('tr')
, TYPE
) AS NVARCHAR(MAX)) + N'</table>' ;
ELSE
SET @tableHTML = 'No data available message...'
BEGIN
PRINT @tableHTML
/*
EXEC msdb.dbo.sp_send_dbmail @recipients = 'nm', @profile_name = 'Test2', @subject = 'Work Order List',
@body = @tableHTML, @body_format = 'HTML' ;
*/
END
HTH
gsc_dba
June 27, 2011 at 3:30 am
gsc_dba (6/27/2011)
You could also circumvent the @varCheck by setting the @tableHTML = your result set when exists = trueAnd set @tableHTML to be your "No data available..." when exists = false...
Prob more efficient:
USE [AdventureWorks]
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @varCheck BIT
IF EXISTS ( SELECT *
FROM AdventureWorks.Production.WorkOrder AS wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
/*Comment and uncomment this line for testing*/
--WHERE [p].[Class] = 'Nonsense'
)
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
FOR
XML PATH('tr')
, TYPE
) AS NVARCHAR(MAX)) + N'</table>' ;
ELSE
SET @tableHTML = 'No data available message...'
BEGIN
PRINT @tableHTML
/*
EXEC msdb.dbo.sp_send_dbmail @recipients = 'nm', @profile_name = 'Test2', @subject = 'Work Order List',
@body = @tableHTML, @body_format = 'HTML' ;
*/
END
HTH
This is exactly what I was after - thanks!
😀
________________________________________________________________________________
Can I ask you a rhetorical question...?
________________________________________________________________________________
June 27, 2011 at 8:23 am
It works like a charm !!!!!!!!!!!!!!!!!:-):-):-):-):-)
Thaaaaaaaaaaaaaaaaaaank You!!!!!!!!!!!!!!!!!:-):-):-):-)
June 27, 2011 at 8:43 am
Your welcome
🙂
gsc_dba
June 30, 2011 at 1:24 am
One more query ..
My boss wants me now to stop sending blank reports rather than sending a message.
Is this possible?????
Regards,
Nithin
June 30, 2011 at 3:44 am
gsc_dba (6/27/2011)
Try this - uncomment the exec mail for each for testing...
USE [AdventureWorks]
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @varCheck BIT
IF EXISTS ( SELECT *
FROM AdventureWorks.Production.WorkOrder AS wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
--WHERE [p].[Class] = 'Nonsense'
)
SET @varCheck = 1
ELSE
SET @varCheck = 0
BEGIN
IF @varCheck = 1
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
FOR
XML PATH('tr')
, TYPE
) AS NVARCHAR(MAX)) + N'</table>' ;
PRINT @tableHTML
/*
EXEC msdb.dbo.sp_send_dbmail @recipients = 'nm', @profile_name = 'Test2', @subject = 'Work Order List',
@body = @tableHTML, @body_format = 'HTML' ;
*/
END
IF @varCheck = 0
PRINT 'Nothing to send...'
/*
EXEC msdb.dbo.sp_send_dbmail @recipients = 'nm', @profile_name = 'Test2', @subject = 'Work Order List',
@body = 'No data available', @body_format = 'HTML' ;
*/
Yes - try this snippet and instead of using sp_send_dbmail when there is nothing to send, exit the stored procedure.
gsc_dba
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply