June 28, 2011 at 8:30 am
This is probably a SILLY question and a quick Google search didn't yield any satisfying results so here I am...
I've got a procedure that sends out an email as our corporate reports process notifying that there are x number of reports left to run. When this email gets sent out it has a subject but the entire email is blank (making me think something's returning a null somewhere)
What it should be doing is this:
Application Reports left to run: 5/120
--------------------------------------------------------------------------------------------
Note: This counter notification will quit after 12hrs if reports have not completed.
When it completes, it sends a final email with some statistics on the run (this works as expected).
Report(s) left to run: 0/120
Total Report Execution Time: 02:10:19
(Count does not include reports catching up)
--------------------------------------------------------------------------------------------
Report(s) failed: 1
ScheduleID: 7054
ATID/AID/Freq: 51 - 15443 - 8
Report Name: \\UNCPath\Folder\InvoiceTransactionsReport_15443_2011-06-11_0500.pdf
Result: 1:An error has occurred during report processing.Unhandled exception:
You can review reports at the dashboard: http://Server/Reports/Pages/Report.aspx?ItemPath=%2f_ReportMaster%2fReporting_Dashboard
The code generating these notifications isn't overly complicated but I can't figure out why this doesn't return what it's supposed to! When I run the procedure manually to test, it works. Is it possible that while the WAITFOR DELAY is issued that the variable @RunCnt loses its value (I fear is a stupid question to ask), and if not, can someone please point out the flaw(s) in the TSQL that are contributing to the issue?
CREATE PROCEDURE [dbo].[utl_CheckApplicationReportsStatus] AS
SET NOCOUNT ON
--DECLARE VARIABLES
BEGIN
DECLARE @Recipientsvarchar(200)
DECLARE @Subjectvarchar(175)
DECLARE @Bodyvarchar(MAX)
DECLARE @AppReportsToRunint
DECLARE @MAXSENDint
DECLARE @RunTimevarchar(10)
DECLARE @Counterint
DECLARE @CurrentRundatetime
DECLARE @ScheduleIDvarchar(6)
DECLARE @ReportInfovarchar(125)
DECLARE @ReportNamevarchar(750)
DECLARE @OutComevarchar(750)
DECLARE @Cntvarchar(6)
DECLARE @idxint
DECLARE @RunCntint
DECLARE @Jobvarchar(250)
DECLARE @ErrNovarchar(15)
DECLARE @ErrMsgvarchar(2000)
DECLARE @Elapsedvarchar(8)
END
--DECLARE TABLES
BEGIN
DECLARE @Results TABLE (
idxint IDENTITY(1,1),
schedIDint,
RptInfovarchar(25),
RptNamevarchar(500),
Outcomevarchar(500)
)
END
--SET VARIABLES
BEGIN
SET @Recipients = 'mydoggiejessie@mycompany.com'
SET @RunTime = CONVERT(varchar(10),GETDATE(),121)
SET @CurrentRun = CAST(DATEDIFF(dd,0 ,GETDATE()) AS DATETIME)
SET @MAXSEND = 72
SET @Counter = 0
SET @Job = 'Run Reports - CC - Running Notifications'
END
/* ######################################### START MAIN PROCEDURE HERE ########################################## */
BEGIN
/* Since Job is called from another procedure, wait 30 minutes before counting the remaining reports */
WAITFOR DELAY '00:30:00'
/* How many reports are left */
SELECT @AppReportsToRun = ISNULL(COUNT(1), 0)
FROM SSRS_Run_Reports.dbo.AppReportQueue WITH(READUNCOMMITTED)
WHERE
CONVERT(varchar(10),NextDateTime,121) <= CONVERT(varchar(10), @RunTime, 121)
AND Frequency NOT IN (2,99)
/* Fetch total reports to run */
SET @RunCnt = (SELECT AppRptCount from dbo.ReportCounts with(nolock))
/* Send current report counts */
WHILE((@AppReportsToRun > 0 and @Counter < @MAXSEND))
BEGIN
SET @Subject = 'Application Reporting - Reports Continue to Run'
SET @Body =
+ '
Application Reports left to run: ' + CAST((@AppReportsToRun) AS varchar(5)) + '/' + CAST((@RunCnt) AS varchar(5)) + '
'
+ '--------------------------------------------------------------------------------------------
'
SET @Body = RTRIM(@Body)
+ 'Note: This counter notification will quit after 12hrs if reports have not completed.'
EXECUTE msdb.dbo.sp_send_dbmail @recipients = @Recipients
,@subject = @Subject
,@body = @Body
,@body_format = 'HTML'
WAITFOR DELAY '00:30:00'
/* Count reports to run */
SELECT @AppReportsToRun = ISNULL(COUNT(1), 0)
FROM SSRS_Run_Reports.dbo.AppReportQueue WITH(READUNCOMMITTED)
WHERE
CONVERT(varchar(10),NextDateTime,121) <= CONVERT(varchar(10), @RunTime, 121)
AND Frequency NOT IN (2,99)
SET @Counter = @Counter + 1
END
IF (@AppReportsToRun = 0)
BEGIN
SET @Elapsed = (SELECT F1Settings.dbo.fx_GetElapsedTime(MIN(Executed), Max(Executed)) FROM AppReportResults )
SET @Subject = 'Application Support Reports - Have Completed'
SET @Body = '
Report(s) left to run: ' + cast((@AppReportsToRun) as varchar(4)) + '/' + CAST((SELECT AppRptCount from dbo.ReportCounts with(nolock)) AS varchar(4)) + '
'
+ 'Total Report Execution Time: ' + @Elapsed + '
'
+ '(Count does not include reports catching up)
'
+ '--------------------------------------------------------------------------------------------'
/* How many have failed */
INSERT INTO @Results
SELECT DISTINCT
ScheduleID,
RTRIM(ATID) + ' - ' + RTRIM(AID) + ' - ' + RTRIM(DeliveryFrequency),
ReportName,
OutCome
FROM SSRS_Run_Reports.dbo.CC_Report_Log WITH(READUNCOMMITTED)
WHERE LEFT(Outcome, 1) = 1
AND Finish >= @CurrentRun
SET @Cnt = @@ROWCOUNT
IF CAST(@Cnt as int) > 0
BEGIN
SET @Body = RTRIM(@Body)
+ '<font color="red">Report(s) <u>failed</u></font>: ' + RTRIM(@Cnt) + '
'
WHILE EXISTS (SELECT TOP 1 idx FROM @Results )
BEGIN
SELECT TOP 1 @idx = idx, @ScheduleID = schedID, @ReportInfo = RptInfo,
@ReportName = RptName, @Outcome = Outcome FROM @Results
SET @Body = RTRIM(@Body)
+ '<b>ScheduleID</b>:' + RTRIM(@ScheduleID) + '
'
+ '<b>ATID/AID/Freq</b>:' + RTRIM(@ReportInfo) + '
'
+ '<b>Report Name</b>:' + RTRIM(@ReportName) + '
'
+ '<b>Result</b>:' + RTRIM(@OutCome) + '
'
+ '
'
DELETE FROM @Results WHERE idx = @idx
END
END
SET @Body = RTRIM(@Body) + '
You can review reports at the dashboard:</br>
http://nas2-dbr/Reports/Pages/Report.aspx?ItemPath=%2f_ReportMaster%2fReporting_Dashboard '
EXECUTE msdb.dbo.sp_send_dbmail @recipients = @Recipients
,@subject = @Subject
,@body = @Body
,@body_format = 'HTML'
/* Stop the Job from Running */
BEGIN TRY --'Run Reports - CC - Running Notifications'
IF ((SELECT F1Settings.dbo.fx_FetchJobStatus(F1Settings.dbo.fx_FetchSQLAgentJobID(@Job))) = 0)
BEGIN
EXECUTE msdb.dbo.sp_stop_job @Job
END
END TRY
BEGIN CATCH
SELECT @ErrNo = ERROR_NUMBER(), @ErrMsg = ERROR_MESSAGE()
SET @ErrMsg = 'ERROR :: ' + @ErrNo + CHAR(13)
+ '----------------------------------------------------------' + CHAR(13)
+ @ErrMsg
SET @Subject = 'Application Support Reporting :: There was an error stopping ' + RTRIM(@Job) + '!'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'dba_alerts@mycompany.com',
@subject = @Subject,
@body = @ErrMsg,
@importance = 'High'
END CATCH
END
EndProcess:
END
/* ########################################## END MAIN PROCEDURE HERE ########################################### */
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 28, 2011 at 8:44 am
This may help
http://msdn.microsoft.com/en-us/library/ms187953.aspx
The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared. For example, the following script generates a syntax error because the variable is declared in one batch and referenced in another:
USE AdventureWorks2008R2;
GO
DECLARE @MyVariable int;
SET @MyVariable = 1;
-- Terminate the batch by using the GO keyword.
GO
-- @MyVariable has gone out of scope and no longer exists.
-- This SELECT statement generates a syntax error because it is
-- no longer legal to reference @MyVariable.
SELECT BusinessEntityID, NationalIDNumber, JobTitle
FROM HumanResources.Employee
WHERE BusinessEntityID = @MyVariable;
gsc_dba
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply