SQL Server Performance

What is the difference between Exception event and User Error Message event in Profiler?

Discussion in 'SQL Server 2005 General Developer Questions' started by pcsql, Jan 17, 2008.

  1. pcsql New Member

    I'm trying to catch error in a .net application but the exception caught in .Net is actually the User Error Message. Should I just use the Error # in the User Error Message?
  2. satya Moderator

  3. pcsql New Member

    Hi Satya,
    I'm using Try...Catch in VB.NET to catch connection problem (non-existing database xyz for this sample). I used the profiler to trace the connection problem and here was the exceptions and errors events in the trace:
    - <Event id="33" name="Exception">
    <Column id="11" name="LoginName">sa</Column>
    <Column id="31" name="Error">911</Column>
    <Column id="51" name="EventSequence">23813</Column>
    <Column id="10" name="ApplicationName">.Net SqlClient Data Provider</Column>
    <Column id="12" name="SPID">61</Column>
    <Column id="14" name="StartTime">2008-01-18T10:17:04.837-08:00</Column>
    <Column id="20" name="Severity">16</Column>
    <Column id="30" name="State">1</Column>
    <Column id="1" name="TextData">Error: 911, Severity: 16, State: 1</Column>
    <Column id="9" name="ClientProcessID">1668</Column>
    </Event>
    - <Event id="162" name="User Error Message">
    <Column id="11" name="LoginName">sa</Column>
    <Column id="31" name="Error">4060</Column>
    <Column id="51" name="EventSequence">23814</Column>
    <Column id="10" name="ApplicationName">.Net SqlClient Data Provider</Column>
    <Column id="12" name="SPID">61</Column>
    <Column id="14" name="StartTime">2008-01-18T10:17:04.837-08:00</Column>
    <Column id="20" name="Severity">11</Column>
    <Column id="30" name="State">1</Column>
    <Column id="1" name="TextData">Cannot open database "xyz" requested by the login. The login failed.</Column>
    <Column id="9" name="ClientProcessID">1668</Column>
    </Event>
    - <Event id="22" name="ErrorLog">
    <Column id="11" name="LoginName">sa</Column>
    <Column id="31" name="Error">18456</Column>
    <Column id="51" name="EventSequence">23815</Column>
    <Column id="10" name="ApplicationName">.Net SqlClient Data Provider</Column>
    <Column id="12" name="SPID">61</Column>
    <Column id="14" name="StartTime">2008-01-18T10:17:04.837-08:00</Column>
    <Column id="20" name="Severity">14</Column>
    <Column id="1" name="TextData">2008-01-18 10:17:04.83 Logon Error: 18456, Severity: 14, State: 16. 2008-01-18 10:17:04.83 Logon Login failed for user 'sa'. [CLIENT: 192.168.1.134]</Column>
    <Column id="9" name="ClientProcessID">1668</Column>
    </Event>
    - <Event id="21" name="EventLog">
    <Column id="11" name="LoginName">sa</Column>
    <Column id="31" name="Error">18456</Column>
    <Column id="51" name="EventSequence">23816</Column>
    <Column id="2" name="BinaryData">184800000E000000130000004400450056004E00450054005C0041004D004E00450054005600530032003000300038000000070000006D00610073007400650072000000</Column>
    <Column id="10" name="ApplicationName">.Net SqlClient Data Provider</Column>
    <Column id="12" name="SPID">61</Column>
    <Column id="14" name="StartTime">2008-01-18T10:17:04.837-08:00</Column>
    <Column id="20" name="Severity">14</Column>
    <Column id="1" name="TextData">Login failed for user 'sa'. [CLIENT: 192.168.1.134]</Column>
    <Column id="9" name="ClientProcessID">1668</Column>
    </Event>
    - <Event id="20" name="Audit Login Failed">
    <Column id="3" name="DatabaseID">1</Column>
    <Column id="11" name="LoginName">sa</Column>
    <Column id="23" name="Success">0</Column>
    <Column id="31" name="Error">18456</Column>
    <Column id="35" name="DatabaseName">master</Column>
    <Column id="51" name="EventSequence">23817</Column>
    <Column id="8" name="HostName">WKSPETERC521</Column>
    <Column id="12" name="SPID">61</Column>
    <Column id="64" name="SessionLoginName">sa</Column>
    <Column id="1" name="TextData">Login failed for user 'sa'. [CLIENT: 192.168.1.134]</Column>
    <Column id="9" name="ClientProcessID">1668</Column>
    <Column id="49" name="RequestID">0</Column>
    <Column id="10" name="ApplicationName">.Net SqlClient Data Provider</Column>
    <Column id="14" name="StartTime">2008-01-18T10:17:04.837-08:00</Column>
    <Column id="26" name="ServerName">TESTINGNETAPP</Column>
    </Event>
    - <Event id="162" name="User Error Message">
    <Column id="11" name="LoginName">sa</Column>
    <Column id="31" name="Error">18456</Column>
    <Column id="51" name="EventSequence">23818</Column>
    <Column id="10" name="ApplicationName">.Net SqlClient Data Provider</Column>
    <Column id="12" name="SPID">61</Column>
    <Column id="14" name="StartTime">2008-01-18T10:17:04.837-08:00</Column>
    <Column id="20" name="Severity">14</Column>
    <Column id="30" name="State">1</Column>
    <Column id="1" name="TextData">Login failed for user 'sa'.</Column>
    <Column id="9" name="ClientProcessID">1668</Column>
    </Event>
    -
    From the trace, I can see the Exception (Error 911) but I can only see the first 2 User Error Message in .NET.
    Here is my understanding about the events based on this behavior:
    Exception event only show in trace.
    User Error Message is the information returned to the caller.
    ErrorLog event is the information logged to SQL Server logs.
    EventLog event is the information logged to Application logs of Event Viewer.
    The Audit Login Failed event is kind of confusing since it is showing Master as the database name.
    Another weird thing is that if I do the same thing thru SSMS, I will get more events and get the connection. Somehow, SQL Server will use Master automatically. I think this may be related to the behavior of Error 911.
  4. satya Moderator

    That error relates to :
    This error occurs when attempting to change database context (with a USE statement) to a database that does not exist, or when the default database established for a login does not exist. In the latter case, the user login then attempts to access the master database.
    So you should also ensure that user has relevant privileges to access the database.

Share This Page