Cluster computing

Saturday, August 17, 2013

In the previous post we talked about database transforms of test results and mail. This was easy to implement when you apply the xslt transform. The resulting procedure with cursors looks something like this:

DECLARE @results table(
TestRunId uniqueidentifier NOT NULL,
SubmittedBy varchar(30) NOT NULL,
TestOwner varchar(30) NOT NULL,
TestName varchar(255) NOT NULL,
Environment varchar(10) NOT NULL,
TestCategory varchar(255) NOT NULL,
Submitted varchar(255) NOT NULL,
Status varchar(10) NOT NULL,
Elapsed varchar(255) NOT NULL,
Total int NOT NULL,
Passed int NOT NULL,
Fail int NOT NULL,
Inconclusive int NOT NULL,
Error int NOT NULL,
PercentPass varchar(10) NOT NULL,
ResultLabelWidth varchar(10) NOT NULL);

DECLARE @TestRunId uniqueidentifier ;
DECLARE @SubmittedBy varchar(30) ;
DECLARE @TestOwner varchar(30) ;
DECLARE @TestName varchar(255) ;
DECLARE @Environment varchar(10) ;
DECLARE @TestCategory varchar(255) ;
DECLARE @Submitted varchar(255) ;
DECLARE @Status varchar(10) ;
DECLARE @Elapsed varchar(255) ;
DECLARE @Total int ;
DECLARE @Passed int ;
DECLARE @Fail int ;
DECLARE @Inconclusive int ;
DECLARE @Error int ;
DECLARE @PercentPass varchar(10) ;
DECLARE @ResultLabelWidth varchar(10);

INSERT into @results SELECT * FROM dbo.Results; -- or exec stored_proc
DECLARE @msg nvarchar(MAX);
SET @msg = '<head></head><body>
<div>Test Run Report</div><table id="ResultContainer" border="1">
<tr>
<th>Service Name</th>
<th>Environment</th>
<th>Test Category</th>
<th>Submitted By</th>
<th>Date</th>
<th>Status</th>
<th>Elapsed</th>
<th>Total</th>
<th>Pass</th>
<th>Fail</th>
<th>Incon</th>
<th>Error</th>
<th>Result</th>
<th>Test Owner</th>
</tr>'
DECLARE Results_Cursor CURSOR FOR (SELECT * From @results); -- exec [ms01806.sbweb.prod].TestRunner.dbo.usp_GetSubmittedTestStatus @SubmittedBy='Ravi Rajamani (CW)',@TodayOnly=1,@TestType=N'Coded';
OPEN Results_Cursor;
FETCH NEXT FROM Results_Cursor INTO @TestRunId, @SubmittedBy , @TestOwner , @TestName, @Environment , @TestCategory , @Submitted , @Status , @Elapsed , @Total , @Passed , @Fail , @Inconclusive , @Error , @PercentPass , @ResultLabelWidth ;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msg = @msg + '<tr>
<td>' + @TestName + '
</td>
<td> ' + @Environment + '
</td>
<td>' + @TestCategory + '
</td>
<td>' + @SubmittedBy + '
</td>
<td>' + @Submitted + '
</td>
<td> ' + @Status + '
</td>
<td>' + @Elapsed + '</td>
<td>' + Convert(nvarchar, @Total) + '
</td>
<td>' + Convert(nvarchar, @Passed) + '
</td>
<td>' + Convert(nvarchar, @Fail) + '
</td>
<td> ' + Convert(nvarchar, @Inconclusive) + '
</td>
<td>' + Convert(nvarchar, @Error) + '
</td>
<td>' + @PercentPass + '
</td>
<td> ' + @TestOwner + '
</td>
</tr>';
FETCH NEXT FROM Results_Cursor INTO @TestRunId, @SubmittedBy , @TestOwner , @TestName, @Environment , @TestCategory , @Submitted , @Status , @Elapsed , @Total , @Passed , @Fail , @Inconclusive , @Error , @PercentPass , @ResultLabelWidth ;
END
SET @msg = @msg + '</table></body>';
CLOSE Results_Cursor;
DEALLOCATE Results_Cursor;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail Profile',
@recipients = 'recipients@where.com',
@body_format = 'HTML',
@body = @msg,
@subject = 'Test Results Reports' ;

Cluster computing

Saturday, August 17, 2013

No comments:

Post a Comment