September 25, 2008 at 3:28 pm
Here is my small trigger code:
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
PRINT 'Login Failure: AL_EVENT_PARAM1 = ' + @eventP1
Execute dbo.CBMI_Audit_LogonFailure @UserName = @eventP1
end
-- Case 2: Login Success
If @eventid = 8 and @status = 0 -- @userid = id of person logging on successfully
begin
PRINT 'Login Success: AL_USER = ' + @userid
Execute dbo.CBMI_Audit_LogonSuccess @UserName = @userid
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
When the trigger gets entered, I get a fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION on my production instance (running on SQL 2000 SP3) and I get the same thing when running on my test instance (SQL 2000 SP4 ..Product version is 8.00.2050 so I think that includes one or more hotfixes on top of SP4).
The code in the trigger does not even get a chance to enter the stored procedures I want to EXECUTE via the trigger. Do you see something wrong with the SELECT statement inside the trigger ? I have studied triggers and written many sprocs but never actually tried a trigger before this application. Thanks in advance for having a look at this.
September 26, 2008 at 12:57 am
Not sure why you're getting an access violation. What do the procs do? Any linked servers involved?
There is another issue with the trigger
select
@status = I.AL_STATUS
, @userid = I.AL_USER
, @eventid = I.AL_EVENT_ID
, @eventP1 = I.AL_EVENT_PARAM1
FROM INSERTED I
Triggers fire once per event, not once per row. What's going to happen if there's more than one row in the inserted table (which will happen if the insert affected more than one row)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2008 at 6:38 am
I agree with Gail, I don't see anything in the trigger itself that would cause an error and with her comment about the trigger not handling a batch/set-based insert.
Ownership chaining should allow for the procs to execute, so I'd think the issue would have to be in the procs somewhere.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 26, 2008 at 8:33 am
Thank you Gail and Jack. I see your point about the possibility of a set of rows in the INSERTED table and I'll think more about that but in this case I don't think that aspect is at the root of this. The table for which the TRIGGER is intended is called AUDIT_LOG which is a collection area for discrete events being logged as they occur (i.e. they should not occur in sets). In fact, using Profiler I captured the text on an actual command string emitted from the application and then wrapped that into a stored procedure in an attempt to debug the TRIGGER (below):
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.CBMI_Trigger_TestHarness
AS
declare @rows int
set @rows = 0 -- nonsense to get our bearings in debugger....
---- want to STEP INTO the trigger on the call stack...
--
exec sp_executesql
N'INSERT INTO AUDIT_LOG (AL_EVENT_ID,AL_SEQUENCE,AL_OBJECT_KEY,AL_EVENT_PARAM1,AL_EVENT_PARAM3,AL_DATETIME,AL_USER,AL_STATUS,AL_WORKSTATN_ADDR,AL_ELAPSED_TIME,AL_SET_NUMBER,AL_PROCESSID)
VALUES (@P1,@P2,@P3,@P4,@P5,CURRENT_TIMESTAMP,@P6,@P7,@P8,@P9,@P10,@P11)',
N'@P1 char(1),@P2 char(1),@P3 varchar(4),@P4 varchar(5),@P5 varchar(13),@P6 varchar(5),@P7 char(1),
@P8 varchar(7),@P9 char(3),@P10 char(1),@P11 char(4)',
'8', '5', '2382', 'admin', 'No Client Log', 'Admin', '0', 'CATFISH', '188', '2', '1980'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
There is no Linked Server involved and I am attempting the testing while logged on directly to the SQL server machine using Query Analyzer. I was wondering if any special permissions are required for a trigger. Again, thanks for your time and thoughts on this.
September 26, 2008 at 8:41 am
I doubt it is a permissions issue as long as the ownership chain on all the objects stays (all objects owned by dbo).
The first thing I would do is simplify the trigger. Just do a select * from inserted and run my test sp.
Then I would add the variables and do a select variables.
Then in the IF I'd just have the Print Statements in my IF blocks to see if they are working.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 26, 2008 at 9:31 am
Jack Corbett (9/26/2008)
I doubt it is a permissions issue as long as the ownership chain on all the objects stays (all objects owned by dbo).The first thing I would do is simplify the trigger. Just do a select * from inserted and run my test sp.
Then I would add the variables and do a select variables.
Then in the IF I'd just have the Print Statements in my IF blocks to see if they are working.
I simplified the trigger, reducing it to just a select * and print. Same thing. When stepping into the trigger via the testharness sproc there is a big pause when I hit F11 on the SELECT * statement inside the trigger then it returns this (never getting to the next statement):
@RETURN_VALUE = N/A
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.
Here is the simplified trigger:
ALTER TRIGGER AuditLogIsMonitored on dbo.Audit_Log
AFTER INSERT
AS
declare @rows int
SELECT * FROM INSERTED I
SELECT @rows = @@rowcount
PRINT 'INSERTED ROW COUNT = ' + CONVERT(VARCHAR(3), @rows)
Pretty straightforward.
Now I am wondering about the syntax of the INSERT INTO AUDIT_LOG inside my testing stored procecure (i.e. I've never seen syntax quite like this on an INSERT before nor am I that familiar with any side effects of invoking it via EXEC sp_executesql).
If there were something wrong with its syntax or some constraint killed it then I doubt an AFTER INSERT trigger would be invoked which it appears to be as I step-into it via the Debugger.
September 29, 2008 at 12:06 pm
I double-checked the syntax of my INSERT script that I use to test and trigger my TRIGGER. It works fine (i.e. the values in the script are indeed INSERTed into the AUDIT_LOG table.
I have simplified my trigger as has been suggested. It fails on a simple SELECT * FROM INSERTED with the error messages I have posted (all of this is on SQL 2000 Service Pack 4 plus some hot fixes).
I don't know what else to try. Please let me know if there is something else I can do to help some of you experts help me. Thank you very much.
September 30, 2008 at 2:20 pm
I could really, REALLY use some more help from you experts here...I am totally stuck. The trigger has been reduced to the most trivial with SELECT * FROM INSERTED.
When triggers are disabled for this table, the INSERT syntax I posted above works just fine as a new row is inserted into the table.
Can you please suggest something else ?
September 30, 2008 at 4:00 pm
To the best of my knowledge an "c0000005 EXCEPTION_ACCESS_VIOLATION" is only caused by (1)a SQL Server bug, a binary corruption in the SQLServer executables or a corruption of one of the databases. Call Microsoft.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 30, 2008 at 6:11 pm
rbarryyoung (9/30/2008)
To the best of my knowledge an "c0000005 EXCEPTION_ACCESS_VIOLATION" is only caused by (1)a SQL Server bug, a binary corruption in the SQLServer executables or a corruption of one of the databases. Call Microsoft.
I'm glad I don't have the experience necessary to have known that. Then it would mean I had had to deal with major problems and who wants that?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 30, 2008 at 10:50 pm
Its not as hopeless as it might sound Jack. For (3) you can restore from backups, for (2) you can reinstall SQL Server and for (1) you can nag MS support until they write you a custom patch, or come up with a work-around.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 1, 2008 at 1:55 am
Jack Corbett (9/30/2008)
I'm glad I don't have the experience necessary to have known that. Then it would mean I had had to deal with major problems and who wants that?
I have plenty of experience with the bug option. (SQL 2000 on Itanium). It got to a point that I knew all of the local PSS guys on a first name basis.
Generally the first thing to do with an access violation is call MS support. They have the tools to read the stack dumps and track where the violation came from. They also have a lot more experience reading stack dumps than most other people do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2008 at 7:42 am
Google search for 'c0000005 EXCEPTION_ACCESS_VIOLATION' found the following KB at Microsoft.
http://support.microsoft.com/kb/892451
Apply SQL Server SP4. Good Luck.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply