Category: T-SQL Tuesday

SSRS, T-SQL Tuesday

T-SQL Tuesday #25–Did Your RS Report Fail?

Allen White (blog|twitter), in hosting this months T-SQL Tuesday, is looking for tricks to help make you more effective.

I could probably try and figure out lots of things that I mess around with to make myself more effective. For example adding aliases for my servers so that instead of having to go to “PRODSERVER17FML27” I can just go to “FML27”.

Instead of that I thought I’d throw something up real quick that turned up as an issue the other day at work.

 

We have a bunch of scheduled Reporting Services reports that get emailed to users and files put out on shares. These schedules are actually run as SQL Server Agent jobs. We ran into an issue whereby a report failed over a weekend. We knew nothing about the failure, the first we knew was when the customer started complaining that the report was not available.

Knowing that the schedules are running in SQL Agent I looked for failed jobs. There were none. All the job does is submit the required report into the job queue and then say that it did it’s job. It does not actually monitor successful execution of the report itself. There doesn’t actually appear to be any quick and simple way of doing this. As such I wrote a process that goes out to the Reporting Services database on a daily basis, looks for reports that have failed and then sends me a report to let me know. It’s a good way to get ahead of the customer and find problems before they are reported.

Code below (in this code the default database ReportServer is used for the Reporting Services database)

 

/* 

Query grabs a list of the failed Reporting Services reports for the last 24 hours.

This list is then sent as an HTML table via email.

Data in email includes:

    Report Name

    Path To Report

    Username Executing Report

    Date/Time When Report Executed

    Parameters Used In Report Execution

*/

 

SET NOCOUNT ON

DECLARE @HTML VARCHAR(MAX)

DECLARE @HTML_Header VARCHAR(MAX)

DECLARE @HTML_Body VARCHAR(MAX)

DECLARE @HTML_Footer VARCHAR(MAX)

DECLARE @COUNT INT

 

--create the table to hold the dataset

DECLARE @Results TABLE

    (

      ID INT IDENTITY(1, 1) ,

      RSName NVARCHAR(850) ,

      RSPath NVARCHAR(850) ,

      UserName NVARCHAR(520) ,

      TimeStart DATETIME ,

      Parameters NVARCHAR(MAX)

    )

 

--Grab the data from the ReportServer database for failed reports

INSERT  INTO @Results

        ( RSName ,

          RSPath ,

          UserName ,

          TimeStart ,

          Parameters

        )

        SELECT  c.Name ,

                c.Path ,

                [UserName] ,

                [TimeStart] ,

                [Parameters]

        FROM    [ReportServer].[dbo].[ExecutionLogStorage] els

                INNER JOIN ReportServer.dbo.Catalog c ON els.ReportID = c.ItemID

        WHERE   status != 'rsSuccess'

                AND TimeStart > DATEADD(hh, -24, GETDATE())

        ORDER BY LogEntryId 

 

 

--create the email

SELECT  @count = MAX(ID)

FROM    @Results

 

IF @count <> 0 

    BEGIN

        SET @HTML_Header = '<html><header></header><body>

        <TABLE>

        <table border="1" cellspacing="1" cellpadding="1"> 

        <TR colspan=2style="color: #A0522D; font-family: Verdana; font-size: 11; font-weight:bold" align=Left>

        <td>Report Name</TD>

        <TD>Report Path</TD>

        <TD>Executed On</TD>

        <TD>Executed By</TD>

        <TD>Parameters</TD>

        </TR>

        '

 

        SET @HTML_FOoter = '</TABLE>

                <BR><BR>

                <FONT FACE="color: #A0522D; font-family: Verdana; font-size: 12";></FONT></body></html>

        '

    END

ELSE 

    BEGIN

        SET @HTML_Header = 'No failed reports in the last 24 hours'

        SET @HTML_Footer = '</TABLE>'

    END

 

SELECT  @HTML_Body = ''

 

WHILE @count > 0 

    BEGIN

        SELECT  @HTML_Body = @html_body + '<TR style="font-size: 10">

                           <TD style="text-align: left;" >' + RSName + '</TD>

                           <TD style="text-align: LEFT;">&nbsp ' + RSPath

                + '</TD>

                           <TD>&nbsp ' + CONVERT(VARCHAR, timestart, 120)

                + '</TD>

                           <TD style="text-align: left;">&nbsp ' + Username

                + '</TD>

                           <TD style="text-align: left;">&nbsp '

                + ISNULL(Parameters, '') + '</TD>

                          

</TR>'

        FROM    @results

        WHERE   ID = @count   

 

        SET @Count = @count - 1

 

    END    

 

SET @HTML = @HTML_Header + @HTML_Body + @HTML_FOoter

 

 

EXEC MSDB.DBO.SP_SEND_DBMAIL @RECIPIENTS = '<Your Email Address>',

    @SUBJECT = 'Failed RS Reports - Last 24 hours', @BODY = @HTML,

    @BODY_FORMAT = 'HTML'

--, @IMPORTANCE = 'HIGH'

GO

Backups, T-SQL Tuesday

T-SQL Tuesday #19–What A Disaster

Allen Kinsel (blog|twitter) is running this months T-SQL Tuesday and wanted to know about preparing or recovering from a disaster. I thought this might be a good opportunity to tell a little story of how a disaster sucked up around three weeks of my life a couple of years ago.

 

It was a normal day, I was sitting and quietly going through a small staging release when my director asked me to come into his office. Instantly I started wondering “what did I do?”

Not being able to come up with anything egregious I settled down the stomach gurgles and went wandering in. That I walked in and was offered a seat I got nervous again, this was not a good sign with this director. Now I was really curious as to what could be up.

 

What was up?

“Are you working on anything big right now?” were the words that kicked off something that changed my views on a lot of things.

“Nothing that can’t be put on hold, what’s up?”

“Did you hear about the <redacted> outage?”

“Sure, everyone here has heard about that, it’s a seriously messed up situation.”

“How do you feel about being a part of the solution?”

My long nurtured DBA sense of responsibility kicked in at this point and I heard myself saying “sure”

“Great, I’ll shoot an email off to the admin, you’ll be on a flight to <redacted> first thing in the morning.”

 

I quickly rearranged all of my plans for the next few days and resisted giving myself a facepalm until I was well clear of the office.

 

Up, up and away

The next morning I hopped on a plane and by mid-afternoon was at my place of destination. When I walked in the door it was all hands to the pump where people were rushing around like crazy and the smell of desperation was in the air.

I was brought into a room and the situation explained to me…

A couple of days prior there had been an attempt at a microcode upgrade on a SAN. The upgrade failed, crashed the SAN and corrupted all of the data. No databases could be attached or started, no files were accessible, there was no filesystem, nothing. It was bad.

I asked at what point the decision was going to be made to scratch it and go to a backup (figuring if there had been a DR site for this it would have been in place already).

 

Backups? We don’t need no stinking backups

Yup, you guessed it, there were no backups. I asked when the last time a backup was taken, someone stated that they thought a backup had been taken 9 months before, but they couldn’t be sure and they didn’t know of anyone that could get into that datacenter and see.

The reason for no backup? Apparently it was taking too long to perform the backup and so the decision was made to just turn off the backup process.

I came to find out later no effort had been made to tune the backup process or to attempt alternative backup methods.

I find it unfathomable that there were no backups on a critical system that supported > 1 million users and had extremely high visibility. You would have thought that DR would have been priority #1, but that was not the case.

 

Sleepless nights

While there was little that could be done with the dead storage there was a lot of work around what could be done as a mitigation strategy. What could be done to restore service while other work was done in an attempt to recover some amount of data.

A new SAN was brought up, database installations performed, a change management process was put in place (one had not existed before) and there was a lot of discussion around getting backups working immediately.

I didn’t move from the building the first 36 hours. Thankfully the company brought in three square meals a day for everyone that was there to ensure that we at least got fed. People were sleeping on the floor in offices just to try and get a couple of hours sleep so they could remain functional.

Restoration of service was a slow arduous process as great care had to be taken with the order of enabling certain components.

Slowly things got back to normal, hourly calls with the VP dropped to every four hours. I was able to sleep in a bed and get some rest and a change of clothes (at one point I told the VP I was running out of clothes and asked how much longer I was going to be there, his response was that I should probably go and expense some underwear).

 

Getting back some data

A little over two weeks after everything went kaboom we started getting word of some data recovery. A third party company had been brought in and had been performing a block by block recovery of the storage from the bad SAN. They were not able to pull files or anything that simple, they were just able to pull access data on blocks. With a great deal of effort they managed to recover 90% of the data, which then had to somehow get validated and reconciled with the data now in the system.

Scripts abounded, levels of confidence in the data had to be decided upon and the risks ascertained for data restoration. That to me was a very scary concept. I’m glad that the decision on that one was made well above my pay grade.

After about three weeks I was able to go home. My work (and everyone else who was sent down to work on the recovery attempt) was acknowledged in a company meeting a couple of months later.

 

Takeaways

This is a real world example of a disaster. If there had been a backup a great many good people would not have been stuck away from home for three weeks.

It gave me a much greater appreciation for what can happen in a disaster. Don’t get caught out, make sure your backups are good and you have a strong business continuity strategy.

SQL, T-SQL Tuesday

T-SQL Tuesday #18–CTE A Simpler Form Of Recursion

It’s T-SQL Tuesday time folks. Bob Pusateri (blog|twitter) is the host and has chosen Common Table Expressions (CTEs) as the topic of the month.

Several ideas came to mind for writing about CTEs, one of the best uses I’ve seen for one recently was to grab the name of the most recent backup file for a database. You’ll have to ask Aaron Nelson (blog|twitter) to hook you up with that one though.

I thought I’d write about an interesting problem that was posed to me in an interview a couple of months ago.

 

Here’s a table

I was given a table with two columns; ManagerID, EmployeeID.

This table was populated with a few values thusly:

USE TempDB

GO

create table #ManagersEmployees (ManagerID int, EmployeeID int)

insert into #ManagersEmployees 

values(1,2), (2,3), (2,4), (2,5), (3,6), (3,7), (3,8)

    , (4,10),(5,11),(5,12), (12,13), (12,14)

GO

I was asked to write a recursive procedure to pull out the manager, employee tree for a given ManagerID.

 

CTEs to the rescue

Having done a little work with CTEs and understanding that I could easily write a recursive query using them I was able to quite quickly put together a script to pull the information needed. By throwing it into a procedure it could quickly and easily be executed.

CREATE PROCEDURE ManagerRecursion_CTE @ManagerID INT

AS

SET NOCOUNT ON

;WITH Managers_CTE (ManagerID, EmployeeID )

AS ( SELECT ManagerID, EmployeeID FROM #ManagersEmployees  

        WHERE ManagerID = @ManagerID

UNION ALL

    SELECT e.ManagerID, e.EmployeeID 

        FROM #ManagersEmployees e 

            INNER JOIN Managers_CTE c on e.ManagerID = c.EmployeeID)

SELECT * FROM Managers_CTE ORDER BY ManagerID, EmployeeID

GO

 

I tested and this worked nicely, it was a simple solution and provided the requested results.

 

That’s not recursion

The trouble is that while the results were not correct I was advised that this was not recursive and did not meet the criteria. Back to the drawing board then.

After a lot more work I came up with the following:

CREATE PROCEDURE ManagerRecursion_NonCTE @ManagerID INT

AS

SET NOCOUNT ON

DECLARE @rowcnt INT, @lastrow INT

DECLARE @Tbl_Results TABLE (rowid INT IDENTITY(1,1), ManagerID INT, EmployeeID INT)

 

INSERT INTO @Tbl_Results (ManagerID, EmployeeID)

SELECT ManagerID, EmployeeID

FROM #ManagersEmployees

WHERE ManagerID = @ManagerID

 

SET @rowcnt = @@ROWCOUNT

SET @lastrow = 0

WHILE @rowcnt > 0

BEGIN

INSERT INTO @Tbl_Results (ManagerID, EmployeeID)

SELECT m.ManagerID, m.EmployeeID

FROM #ManagersEmployees m

    INNER JOIN @Tbl_Results t

        ON m.ManagerID = t.EmployeeID

WHERE rowid > @lastrow

 

SELECT @rowcnt = @@ROWCOUNT

 

SELECT @lastrow = @@IDENTITY - @rowcnt

END

SELECT ManagerID, EmployeeID FROM @Tbl_Results ORDER BY ManagerID, EmployeeID

GO

 

I tested this and got back the same results as with the first procedure with all the values I passed in. Deep breath on this one as it was pushing the limits of what I could produce on the spot in an interview.

 

That’s still not recursion

Again, while the results we correct this was not recursive. It was back to the drawing board once more. This time I had to admit defeat, however did tell the interviewer that I would work on a solution at home and email it in. He gave me his contact information, we completed the rest of the interview and I went home determined to get the right data in the manner that the interviewer wanted.

After a whole bunch of reading and a lot of work I finally came up with correct results in a recursive procedure which I emailed in to get feedback.

CREATE PROC ManagerRecursion @EmpID INT, @InnerLoop INT = 0

AS

BEGIN

    IF @InnerLoop = 0

        BEGIN

        CREATE TABLE #Tbl_Results (ManagerID INT, EmployeeID INT)

        END

            INSERT INTO #Tbl_Results (ManagerID, EmployeeID)

            SELECT ManagerID, EmployeeID FROM #ManagersEmployees WHERE ManagerID = @EmpID

 

            SET @EmpID = (SELECT MIN(EmployeeID) FROM #ManagersEmployees WHERE ManagerID = @EmpID)

 

            WHILE @EmpID IS NOT NULL

            BEGIN

                EXEC ManagerRecursion @EmpID, 1

                SET @EmpID = (SELECT MIN(EmployeeID) FROM #ManagersEmployees WHERE EmployeeID > @EmpID 

                    AND EmployeeID NOT IN (SELECT ManagerID FROM #Tbl_Results)

                    AND EmployeeID IN (SELECT EmployeeID FROM #Tbl_Results))

            END

    IF @InnerLoop = 0   

        BEGIN

        SELECT * FROM #Tbl_Results order by ManagerID, EmployeeID

        DROP TABLE #Tbl_Results

        END      

END

 

GO

 

Unfortunately no feedback was forthcoming. I felt good about providing this solution despite that. I enjoy a challenge and this was certainly one of those.

 

So what was the right way?

That depends on who you ask. For me the first way was the right was. It performed well, the code was clean and easy and required a minimum amount of development. I feel that the solution here was exactly the reason that CTEs were created in the first place.

The second solution was a lot more work, the query got more complex and it does not perform as well as the first.

The final procedure was true recursion in that the procedure calls itself over and over again until all of the results are returned. It’s a loop that makes cursors look like they perform well. It was easily the worst performing of the three.

 

It all goes to show there’s more than one way to get the results you need. It’s also interesting how an example like this shows just how much work the SQL development team have done to help reduce complexity and improve performance.