SQL Server Performance

Simple AFTER INSERT trigger fails on SELECT * FROM INSERTED in SQL2000sp4

Discussion in 'General Developer Questions' started by jjamjatra, Sep 30, 2008.

  1. jjamjatra New Member

    First trigger I have ever written seems innocent to me, but results inthis error (seems to occur on the SELECT statement inside the trigger):

    ODBC: Msg 0, Level 19, State 1
    [Microsoft][ODBCSQL Server Driver][SQL Server]SqlDumpExceptionHandler: Process 63generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
    SQL Server is terminating this process.
    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionWrite (send()).
    Server: Msg 11, Level 16, State 1, Line 0
    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation.

    Here is the source for the trigger itself:


    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO


    ALTER TRIGGER AuditLogIsMonitored on dbo.Audit_Log
    AFTER INSERT
    AS
    declare @status int
    declare @userid varchar(254)
    declare @eventidint
    declare @eventP1varchar(254)
    select
    @status = I.AL_STATUS
    , @userid = I.AL_USER
    , @eventid= I.AL_EVENT_ID
    , @eventP1= I.AL_EVENT_PARAM1
    FROM INSERTED I
    -- Case 1: Login Failure
    If @eventid = 8 and @status = 16386 -- and @userid = '(Login failed)'; this test not really needed
    begin
    Execute dbo.CBMI_Audit_LogonFailure @UserName = @eventP1
    RETURN
    end
    -- Case 2: Login Success
    If @eventid = 8 and @status = 0 -- @userid = id of person logging on successfully
    begin
    Execute dbo.CBMI_Audit_LogonSuccess @UserName = @userid
    RETURN
    end



    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO
    The above error happened on my production system which runs SQL 2000 SP3. So I copied the database to my test system which runs SQL 2000 SP4
    and tried the same thing. I got a similar failure but there were slight differences:
    ODBC: Msg 0, Level 19, State 1
    [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler: Process 60 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
    [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionWrite (WrapperWrite()).
    Server: Msg 11, Level 16, State 1, Line 0
    [Microsoft][ODBC SQL Server Driver][Shared Memory]General network error. Check your network documentation.
    I even reduced the already simple trigger to just a SELECT * FROM INSERTED and it fails with the same thing. Can a trigger have any other "dependencies" other than table for which it is FOR ? For example, I am puzzled why this is using the ODBC SQL Server Driver? Please help - I am very stuck on what to do next. Thanks.


  2. Adriaan New Member

    I would look for the cause in the CBMI_Audit procedures - this trigger should be completely harmless in itself.
    As you probably already figured out, you need to loop through the inserted snapshot row-by-row. If more than one row was inserted, you're only reading whatever happens to be the last row read from that snapshot.
    EDIT
    The ODBC messages are returned to your client application, which is obviously connecting over ODBC.

Share This Page