|
|
EXCEPTION_ACCESS_VIOLATION wher inserting to a Table with INSTEAD OF INSERT Trigger |
|
Author |
Message |
CTA102
|
Posted: 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
|
Posted: 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
|
Posted: 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
>
>
|
|
|
|
|
|
|