Is it possible to retrieve the raw SQL which returned an error in SQL Server?

Question

I've got a SQL Server 2005 database and a classic ASP front end (don't ask). I'm trying to debug some errors in the IIS log file (error code 80040e14), and I'm not having much joy at the ASP end of things. Is it possible to retrieve the raw SQL which caused the error from SQL Server directly, or failing that set SQL Server to record such bad SQL in future? The errors occur intermittently a few times a week only, so I'm not sure if Profiler would be the tool for the job.

Answer 1

If you're using an ODBC data source, you can use the Tracing tab in the ODBC control panel to turn on tracing, which, if I remember right, records all the traffic to that data source. Although, unless you can reliably force the error to occur on demand, you're going to wind up with one humongous log file to wade through.

So probably not much different than recording all the traffic with Profiler.

Unless you don't have permissions to run profiler against the server.

Answer 2

In theory trace flag 3602 is designed for this - DBCCC TraceOn 3602 - it is not ideal and I'm not sure whether it still works on 2005, YMMV.

Answer 3

Trace flag 3602 + trace flag 3605 should get the error messages into the SQL Server log. See http://support.microsoft.com/kb/199037. Those work in SQL Server 2005, in my experience.

Another option would be to use SQL profiler to log the User Error Message event - this will give you the text of the error but not the statement that caused the error. Profiling the BatchCompleted event where the Error column is not equal to 0 should give you the statement text that errors out. That should put you pretty close to the error.