How to log the syntax error

Question

How to log the syntax error in Sql Server for example 'Invalid object name 'Table1'.I am trying to capture syntax error into try/catch block but its directly breaking the line from code and eventually send a alert mail.Please find below code which i tried.

Create Procedure dbo.Errorlog
as
BEGIN
BEGIN TRY
Select * from Table1;
END TRY
BEGIN CATCH
exec dbo.spGetErrorInfo
END CATCH
END

Answer 1

Error handling in SQL Server can be complex.

In the case you have, the Query Optimizer attempts to create a query plan when you execute the procedure. If it suceeds in creating the plan, it then executes it. But if it cannot create the plan, then it never executes your procedure. So it never gets to then BEGIN TRY/CATCH code.

Erland Summarskog has an excellent series of blogs on error handling you can access at http://www.sommarskog.se/error_handling/Part1.html. It is well worth reading and understanding the entire blog. But if you want to see how to handle this type of error (a misspelled object name in a stored procedure) go to
http://www.sommarskog.se/error_handling/Part2.html#compilationerrors and scroll down to the heading "Binding Errors".

Tom

Answer 2

Tom says it's "complex" - I guess that is intended to be a finer word for "mess".

Had SQL Server still worked like in 6.5, the explanation would be simple: you could create stored procedures if tables are missing. And if you dropped a table after creating your procedure, you were not table to start it.

But since SQL 7 there is deferred name resolution, and this is an error that occurs at run time. But yet, you cannot cannot catch these errors in the scope they occur, only in outer scope. I and other people have reported this as bugs, but they were closed as "won't fix" or "by design".

I guess the design here is that SQL Server is meant to be difficult.

Tom has already been kind to give links to my series of articles on the topic.

Answer 3

Hi @Rakesh kumar ,

If an error occurs in Transact-SQL, there are generally two ways to capture the error, one is to use a try...catch statement in the client code (such as c#, delphi, etc.) to capture; the other is In Transact-SQL, use the error capture mechanism provided by Transact-SQL itself to capture. If the error is caused by the execution of the Transact-SQL statement, such as a key-value conflict, the first and second methods can be used to capture, but if it is a logical error, it is not convenient to use client code to capture.

How to use Transact-SQL for error capture? Please refer to：TRY...CATCH (Transact-SQL)

Exception Handling in SQL Server by TRY…CATCH

Best Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.