Trigger results in fatal exception in SQL2000 SP3

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger results in fatal exception in SQL2000 SP3

Author

Topic

jjamjatra
Starting Member

13 Posts

Posted - 2008-09-25 : 12:31:46

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

ODBC: Msg 0, Level 19, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler: Process 63 generated 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 @eventid	int
declare @eventP1	varchar(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

First I tested this via my web app and got a strange error (different from the ODBC SQL Server Driver error posted above). Then
wrote a little test driver so I could debug it via Query Analyzer directly on the database server:

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

I scraped this code above out of the Profiler trace I was running earlier so I think it is good. Can you suggest any ideas on either what might be wrong with my trigger code or how to best troubleshoot this problem? Thanks in advance for your time and interest.

BTW - I have not applied SP4 yet to SQL Server 2000; I will move toward that too but I thought I should ask about the trigger itself first.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-25 : 13:27:46

Access violations are SQL Server bugs. You need to not only install SP4 for 2000 but also the latest patch to it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jjamjatra
Starting Member

13 Posts

Posted - 2008-09-25 : 17:14:20

quote:
Originally posted by tkizer

Access violations are SQL Server bugs. You need to not only install SP4 for 2000 but also the latest patch to it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Thanks. I took a backup of my database and restored it to another machine running an instance of SQL Server 2000 at SP4. The product version string is 8.00.2050.

Then I used my TestHarness stored procedure to debug the trigger and stepped into the trigger. This time the failure was a little different (see below):

ODBC: Msg 0, Level 19, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRead (WrapperRead()).

Server: Msg 11, Level 16, State 1, Line 0
[Microsoft][ODBC SQL Server Driver][Shared Memory]General network error. Check your network documentation.

I think hotfixes applied after SP4 have brought up the build level to 8.00.2050 from 8.00.2039.

Do you see anything wrong with the SELECT statement in the TRIGGER (seems to go beserk when that statement is executed..)?

Again, thanks for your time.

jjamjatra
Starting Member

13 Posts

Posted - 2008-09-29 : 14:13:20

quote:
Originally posted by tkizer

Access violations are SQL Server bugs. You need to not only install SP4 for 2000 but also the latest patch to it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Do you have any other ideas? As I posted earlier, I am getting a similar EXCEPTION_ACCESS_VIOLATION against SQL 2000 SP4. I have reduced the trigger down to a simple SELECT * FROM INSERTED statement but even that fails. There is only my single trigger against this table. When I disable this trigger the INSERT script I am using for triggering the TRIGGER works correctly. Is there something at a more global level that prohibits triggers on a table? Please suggest something else I can do to troubleshoot this situation. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-29 : 14:20:49

The only thing that can be suggested is to open a case with Microsoft on this since access violations are SQL Server bugs. Unfortunately, you are using SQL Server 2000, so if it's a new bug you may not be able to get support for it due to 2000 hitting end-of-life for support. If it's a known bug though, they'll direct you to the KB article for it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jjamjatra
Starting Member

13 Posts

Posted - 2008-09-30 : 16:24:19

Thank you; I will consider the MSFT support option.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources