Last week I did something I promised myself I would never do again. It was horrible, dirty and made me feel like the worst DBA in the world. I didn’t drop a production database or truncate the most important table, this was way worse. I created a cursor. I am hoping that can I appeal to your kindness and understanding. Please help me forgive myself so the healing may begin.

It all started when I needed to build an Error Log report in SSRS. We are using a PowerShell script to go out to all our servers and bring a copy of the latest SQL error log to a centralized database and table. I was making a call to this table and only pulling back the last 4 days worth of data. I have learned over the years that with SSRS if you try to feed it too much data, you don’t get a report, you get a big red “X”. I was getting that “X”. Since I wasn’t allowed to modify the base table incase we needed to review it for other troubleshooting matters, I created a new table that just had the last 4 days worth of data. I was still getting the red “x”. I knew I had to filter my data more. We have a table of specific errors that we want to show up on this report. But they are not the complete error, just parts of it so the error remains generic enough to pull it from any server. In order for me to compare these fields, I need to use a “LIKE” clause. I tried joining with the “LIKE” clause, but I didn’t have any luck. I even tried a “Cross Join”  and a “Hard Join” but it still didn’t work.

In my despair, I was talking to a friend of the kingdom and he suggested a cursor. I backed away in shock. “No, no! Cursors are bad!” I cried. He calmly looked at me and said, “You are trying to use a sledge-hammer to hang a picture right now, try the cursor.”

I sat back down, arguing with myself. Could I do this? Betray what I have always believed and actually use a cursor? I figured I would at least write one and hope that I came up with a better solution in the process.

I know you are all excited to see the monster that I have created, truly a Royal Pain:

SET ANSI_PADDING ON
GO

Declare @objid int
Declare @ErrorMsg Varchar (255)

DECLARE SQLErrorLog_Cursor CURSOR FOR

/*Here is where I load my table of filters. This will allow me to add more errors to the report later without rebuilding anything.*/

SELECT [objid],[ErrorMsg]
FROM [LightsOn].[dbapp].[SQLErrorLogParseList]
OPEN SQLErrorLog_Cursor

FETCH NEXT FROM SQLErrorLog_Cursor
INTO @objid, @ErrorMsg
WHILE @@FETCH_STATUS = 0
BEGIN

/*This is my main query that I need to apply the filter to. InstanceSQLLog is the really large table I am filtering down.*/
INSERT INTO dbapp.SQLErrorLogFilter
SELECT InstanceName, LogDate, Text, P.FullName AS PrimaryOps, P.ID AS PrimaryOpsID,P3.FullName AS SecondaryOps, P3.ID AS SecondaryOpsID
FROM dbapp.InstanceSQLLog ISL
LEFT JOIN dbapp.Instance I ON ISL.InstanceId = I.InstanceID
LEFT JOIN People P ON I.PrimaryOpsDBAId = P.Id
LEFT JOIN dbo.People P3 ON I.SecondaryOpsDBAId = P3.ID
WHERE LogDate > GETDATE()-4 AND TEXT LIKE '%' + @ErrorMsg + '%'

FETCH NEXT FROM SQLErrorLog_Cursor
INTO @objid, @ErrorMsg
END

CLOSE SQLErrorLog_Cursor
DEALLOCATE SQLErrorLog_Cursor
GO

This is one of the steps of a 4 part job that I auto run early in the morning so the reports are in the specified DBA’s Email when they arrive at work (each report is customized to the DBA and the servers that are their Primary responsiblity.).

The whole process goes like this: I truncate the dbapp.SQLErrorLogFilter Table, run this cursor, Truncate the final grouped and filtered table for the report and repopulate that so SSRS gets a very clean and easy to read table. The whole process takes about 8 minutes. I am still trying to find a better way, but this works for now and the team is happy.

I have been toying with the idea of adding FastForward to the cursor. Does anyone have any opinions of why I should or should not do that?

Thank you for being so kind to read. I love how included the community makes me feel and how much everyone is willing to teach each other.

Have a magical week!

Andrea