EXCEPTION_ACCESS_VIOLATION wher inserting to a Table with INSTEAD OF INSERT Trigger  
Author Message
CTA102





PostPosted: Thu Jul 22 08:03:10 CDT 2004 Top

SQL Server Developer >> EXCEPTION_ACCESS_VIOLATION wher inserting to a Table with INSTEAD OF INSERT Trigger

Hello all,

we are currently evaluating the use of INSTEAD OF Trigger for our
Application and found out that it could simplify some of our work.
BUT:

I have a table with an INSTEAD-OF trigger for insert.
Every now and again I get strange Errors from SQL Server and the
Connection closes down.

Error Message in german:
SqlDumpExceptionHandler: Prozess 52 erzeugte eine schwere
Ausnahmebedingung c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
beendet diesen Prozess.

should be something like this in english:
SqlDumpExceptionHandler: Process 52 generated fatal c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

After some time of investigation I was able to find out the problem.
Below you can find some code to produce the error in the "pubs"
database.
The error will occur when INSERT with a SELECT-statement is in the sql
batch AND the SELECT contains ORDER BY with a column that won't be
inserted.
And the Error even occurs when this INSERT will not be executed (see
Procedure P_exceptTest).

ok, I found a little workaround, but I have big concern about the use
of INSTEAD-OF trigger in general. Although it is quite interesting
technique. The point is that we have quite a large SQL-Application
with 200 Tables and 400 procs. If we start switching to I-OF this step
cannot be rolled back.

Is this technique safe enough? Are there other known problems or
experiances?


any suggestions will be appreciated

best regards,

Martin Niedergesäß



****************************************

****************************************

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)


****************************************
SAMPLE CODE
****************************************


CREATE TABLE exceptTest ( id int identity(1,1) primary key,
s varchar(100))

GO

CREATE TRIGGER TR_exceptTest_INSTEAD on exceptTest
INSTEAD OF INSERT
AS

SET NOCOUNT ON
insert exceptTest (s)
SELECT s FROM inserted

GO

/* ERROR will occur */
insert exceptTest (s)
Select au_lname from authors
order by phone

GO

CREATE PROC P_exceptTest
AS

RETURN 0

/* ERROR will occur */
insert exceptTest (s)
Select au_lname from authors
order by phone

GO

/* still crashed */
EXEC P_exceptTest

GO

/* NO ERROR occurs - */
insert exceptTest (s)
Select au_lname from authors
order by au_lname

GO

DROP TABLE exceptTest

SQL Server281  
 
 
Ilya





PostPosted: Thu Jul 22 08:03:10 CDT 2004 Top

SQL Server Developer >> EXCEPTION_ACCESS_VIOLATION wher inserting to a Table with INSTEAD OF INSERT Trigger Martin,

Why do you need order by in the select for the insert anyway?

Ilya


> Hello all,
>
> we are currently evaluating the use of INSTEAD OF Trigger for our
> Application and found out that it could simplify some of our work.
> BUT:
>
> I have a table with an INSTEAD-OF trigger for insert.
> Every now and again I get strange Errors from SQL Server and the
> Connection closes down.
>
> Error Message in german:
> SqlDumpExceptionHandler: Prozess 52 erzeugte eine schwere
> Ausnahmebedingung c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> beendet diesen Prozess.
>
> should be something like this in english:
> SqlDumpExceptionHandler: Process 52 generated fatal c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
>
> After some time of investigation I was able to find out the problem.
> Below you can find some code to produce the error in the "pubs"
> database.
> The error will occur when INSERT with a SELECT-statement is in the sql
> batch AND the SELECT contains ORDER BY with a column that won't be
> inserted.
> And the Error even occurs when this INSERT will not be executed (see
> Procedure P_exceptTest).
>
> ok, I found a little workaround, but I have big concern about the use
> of INSTEAD-OF trigger in general. Although it is quite interesting
> technique. The point is that we have quite a large SQL-Application
> with 200 Tables and 400 procs. If we start switching to I-OF this step
> cannot be rolled back.
>
> Is this technique safe enough? Are there other known problems or
> experiances?
>
>
> any suggestions will be appreciated
>
> best regards,
>
> Martin Niedergesäß
>
>
>
> ****************************************

> ****************************************
>
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>
>
> ****************************************
> SAMPLE CODE
> ****************************************
>
>
> CREATE TABLE exceptTest ( id int identity(1,1) primary key,
> s varchar(100))
>
> GO
>
> CREATE TRIGGER TR_exceptTest_INSTEAD on exceptTest
> INSTEAD OF INSERT
> AS
>
> SET NOCOUNT ON
> insert exceptTest (s)
> SELECT s FROM inserted
>
> GO
>
> /* ERROR will occur */
> insert exceptTest (s)
> Select au_lname from authors
> order by phone
>
> GO
>
> CREATE PROC P_exceptTest
> AS
>
> RETURN 0
>
> /* ERROR will occur */
> insert exceptTest (s)
> Select au_lname from authors
> order by phone
>
> GO
>
> /* still crashed */
> EXEC P_exceptTest
>
> GO
>
> /* NO ERROR occurs - */
> insert exceptTest (s)
> Select au_lname from authors
> order by au_lname
>
> GO
>
> DROP TABLE exceptTest


 
 
martin





PostPosted: Fri Jul 23 02:02:58 CDT 2004 Top

SQL Server Developer >> EXCEPTION_ACCESS_VIOLATION wher inserting to a Table with INSTEAD OF INSERT Trigger well, not in my example. this was just for producing the error.
but if you add an autoincrement key it makes sense. At least for "normal"
tables.
With an instead-of-insert trigger I guess I cannont get hold of the order
the data was inserted.
Therefore it has not much sense maybe.

But even if the statement might not make sense it should not raise an
exception.
Did you try it? does the error only occur on my server?

Are there other disadvantages using instead-of? At least it looks like you
cannot perform a UPDATE xx FROM statement. which makes life a little mor
complicated. Maybe there are other restrictions/problems -I don't know. that
is was I want to find out.

regards
martin






> Martin,
>
> Why do you need order by in the select for the insert anyway?
>
> Ilya


> > Hello all,
> >
> > we are currently evaluating the use of INSTEAD OF Trigger for our
> > Application and found out that it could simplify some of our work.
> > BUT:
> >
> > I have a table with an INSTEAD-OF trigger for insert.
> > Every now and again I get strange Errors from SQL Server and the
> > Connection closes down.
> >
> > Error Message in german:
> > SqlDumpExceptionHandler: Prozess 52 erzeugte eine schwere
> > Ausnahmebedingung c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> > beendet diesen Prozess.
> >
> > should be something like this in english:
> > SqlDumpExceptionHandler: Process 52 generated fatal c0000005
> > EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> >
> > After some time of investigation I was able to find out the problem.
> > Below you can find some code to produce the error in the "pubs"
> > database.
> > The error will occur when INSERT with a SELECT-statement is in the sql
> > batch AND the SELECT contains ORDER BY with a column that won't be
> > inserted.
> > And the Error even occurs when this INSERT will not be executed (see
> > Procedure P_exceptTest).
> >
> > ok, I found a little workaround, but I have big concern about the use
> > of INSTEAD-OF trigger in general. Although it is quite interesting
> > technique. The point is that we have quite a large SQL-Application
> > with 200 Tables and 400 procs. If we start switching to I-OF this step
> > cannot be rolled back.
> >
> > Is this technique safe enough? Are there other known problems or
> > experiances?
> >
> >
> > any suggestions will be appreciated
> >
> > best regards,
> >
> > Martin Niedergesäß
> >
> >
> >
> > ****************************************

> > ****************************************
> >
> > Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> > Dec 17 2002 14:22:05
> > Copyright (c) 1988-2003 Microsoft Corporation
> > Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> >
> >
> > ****************************************
> > SAMPLE CODE
> > ****************************************
> >
> >
> > CREATE TABLE exceptTest ( id int identity(1,1) primary key,
> > s varchar(100))
> >
> > GO
> >
> > CREATE TRIGGER TR_exceptTest_INSTEAD on exceptTest
> > INSTEAD OF INSERT
> > AS
> >
> > SET NOCOUNT ON
> > insert exceptTest (s)
> > SELECT s FROM inserted
> >
> > GO
> >
> > /* ERROR will occur */
> > insert exceptTest (s)
> > Select au_lname from authors
> > order by phone
> >
> > GO
> >
> > CREATE PROC P_exceptTest
> > AS
> >
> > RETURN 0
> >
> > /* ERROR will occur */
> > insert exceptTest (s)
> > Select au_lname from authors
> > order by phone
> >
> > GO
> >
> > /* still crashed */
> > EXEC P_exceptTest
> >
> > GO
> >
> > /* NO ERROR occurs - */
> > insert exceptTest (s)
> > Select au_lname from authors
> > order by au_lname
> >
> > GO
> >
> > DROP TABLE exceptTest
>
>