How can I capture queries that fail on the SQL Server due to connection failures?

Question

How can I capture queries that fail on the SQL Server due to connection failures?

Profiler doesnt seem to capture if there is a connectivity loss.

This is scenario:

1. DB server running in Domain A
2. SQL Client running on a server in Domain B - runs a transaction that would typically take 15 seconds to complete.
3. Network connectivity breaks at 10th second between Domain A and Domain B
4. SQL client displays error message - network failure
5. Transaction not completed on the DB server. No records updated.

Answer 1

I believe you have to do this on application level and not on SQL Server.

Here is .NET way of inserting in to the event viewer: http://forums.asp.net/t/1503109.aspx/1

Answer 2

You can query the SQL logs using EXEC SP_readerrorlog.

Create a table and load the log events using calling this stored proc. You can schedule a job to refresh the load in any interval you need.

CREATE TABLE Log_tbl
(
    LogDate DATETIME,
    ProcessInfo VARCHAR(500),
    Text NVARCHAR(MAX)
)

INSERT Log_tbl
EXEC SP_readerrorlog

SELECT * FROM Log_tbl 

Quering the table above, you can look for an error similar to what I got below:

Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: ]