TRY/CATCH Help Needed

[SgtJarrow]

So I have had this process running for some time and thought I had sufficient error handling to catch most of my errors. Over the weekend, I encountered a new error that did not trigger my CATCH block. I am not 100% sure why my CATCH did not report the error and am looking for some help to ensure I catch these types of errors in the future.

My query reaches into a transactionally replicated database on my reporting server. Friday night, the publishing server was upgraded from SQL 2008 R2 to SQL 2014. The subscriber is still on SQL 2008 R2. I don't think that is the issue but wanted to provide that bit of detail. During the upgrade, several tables were excluded from the replication set. So when my query tried to use one of these (now) missing tables, the following error was returned:

Msg 4413, Level 16, State 1, Procedure LoadLSODSTables, Line 533
Could not use view or function 'CV_Prod.dbo.adTeacher' because of binding errors. "

The replication issues has since been resolved and I can now execute the query as needed. What I am needing some help with is shoring up my TRY/CATCH to catch another instance of this in the future. In other words, FORCING my TRY/CATCH to return ANY AND ALL ERRORS, regardless of severity or outcome. My current TRY/CATCH is below (lots removed for clarity but the blocks are in place). CV_Prod is the database that lives on my reporting server but is transactionally replicated from the production database for reporting purposes.

        BEGIN TRY
	--OMITTED CODE

	WITH cteInstructors(AdClassSchedId, PrimaryInstructor, SecondaryInstructor) AS
	(
	    SELECT
                cs.AdClassSchedID 'AdClassSchedId',
                spi.LastName + ', ' + spi.FirstName 'PrimaryInstructor',
                t.descrip 'SecondaryInstructor'
            FROM CV_Prod.dbo.AdClassSched cs WITH(NOLOCK)
            LEFT JOIN CV_Prod.dbo.SyStaff spi
                    ON cs.AdTeacherID = spi.SyStaffID
            LEFT JOIN CV_Prod.dbo.AdClassSchedInstructor csi
                    ON cs.AdClassSchedID = csi.AdClassSchedID
            LEFT JOIN CV_Prod.dbo.adTeacher t
                    ON csi.AdTeacherID = t.adTeacherID
        )
	SELECT
	    i.AdClassSchedId,
            i.PrimaryInstructor,
            i.SecondaryInstructor
	FROM cteInstructors i;

	EXEC LS_ODS.AddODSLoadLog 'Loaded Courses Working Table', 0;

	--OMITTED CODE
	END TRY
	BEGIN CATCH
		--************
		--NEED THIS CATCH BLOCK TO CATCH ANY AND ALL ERRORS, NO MATTER WHAT SEVERITY OR ERROR NUMBER.
		--************
		EXEC LS_ODS.AddODSLoadLog 'An Error Occurred While Processing The Current GradeExtract File Data', 1;
		--OMITTED CODE
		EXEC msdb.dbo.sp_send_dbmail @profile_name = @EmailProfile, @recipients = @EmailRecipients, @importance = 'High', @subject = @EmailSubject, @body = @EmailBody, @body_format = @EmailFormat;
		RETURN;
	END CATCH

If anyone has any resources to helping me ensure I catch all errors for my process, I would appreciate it. Just need some pointers to find the right answer to my problem and I am not finding the right search terms... Thank you.

Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer

 

[philhege]

Unfortunately your error looks like one that TRY..CATCH can't catch. Read the "Errors Unaffected..." section in this doc:

https://msdn.microsoft.com/en-us/library/ms175976.aspx

The example code may hint at a solution.



-----------
With business clients like mine, you'd be better off herding cats.