Fatal exception c0000005 with INSTEAD OF UPDATE TRIGGER

Hi,
I am receiving the following error when trying to use an 'instead of' update
trigger.
SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
It looks like something to do with calling stored procedures with text
fields. The following test case illustrates the problem:
CREATE TABLE A
(Field1 int,
Field2 text)
GO
CREATE TRIGGER A_UpdateTrig
ON A
INSTEAD OF UPDATE
AS
UPDATE A
SET Field1 = i.Field1,
Field2 = i.Field2
FROM
A JOIN inserted i ON (A.Field1 = i.Field1)
GO
INSERT INTO A VALUES (1, 'aaa')
INSERT INTO A VALUES (2, 'bbb')
GO
CREATE PROCEDURE UpdateA
@.Field1 int,
@.Field2 text
AS
UPDATE A
SET Field2 = @.Field2
WHERE
Field1 = @.Field1
return 1
GO
-- Error occurs here:
exec UpdateA 2, N'cccc'
-- No error occurs here:
DROP TRIGGER A_UpdateTrig
exec UpdateA 2, N'ddd'
DROP TABLE A
DROP PROCEDURE UpdateA
Running this produces the following result:
(1 row(s) affected)
(1 row(s) affected)
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionChec
kForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas would be appreciated. BTW select @.@.version returns:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)Hi,
From your descriptions, I understood that access violation exception occurs
when a table has an INSTEAD OF trigger defined on it, and you try to update
a text column in the table by using a stored procedure. Have I understood
you? Correct me if I was wrong.
Based on my konwledge, it was a known issue of us and a hotfix is available
now! You could check out the knowledge base articles below to learn more
about this
FIX: An access violation exception may occur when you update a text column
by using a stored procedure in SQL Server 2000
http://support.microsoft.com/kb/839523
Note that a supported hotfix is now available from Microsoft for this known
issue, but it is only intended to correct the problem that is described in
this article. Only apply it to systems that are experiencing this specific
problem. This hotfix may receive additional testing. Therefore, if you are
not severely affected by this problem.
To resolve this problem immediately, contact Microsoft Product Support
Services to obtain the hotfix. It wil be a FREE INCIDENT as we have
confirmed that this is a problem in the Microsoft products. For a complete
list of Microsoft Product Support Services phone numbers and information
about support costs, visit the following Microsoft Web site:
http://support.microsoft.com/defaul...scid=fh;[LN];CNTACTMS
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Moreover, you should consider looking into the WRITETEXT and UPDATETEXT
statements as an alternative to strict UPDATES to TEXT data type attributes.
Sincerely,
Anthony Thomas
"Anthony Meehan" <anthonymeehan@.nospam.nospam> wrote in message
news:32DB2A24-2AEE-4B81-9AC8-B0AD216D7114@.microsoft.com...
Hi,
I am receiving the following error when trying to use an 'instead of' update
trigger.
SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
It looks like something to do with calling stored procedures with text
fields. The following test case illustrates the problem:
CREATE TABLE A
(Field1 int,
Field2 text)
GO
CREATE TRIGGER A_UpdateTrig
ON A
INSTEAD OF UPDATE
AS
UPDATE A
SET Field1 = i.Field1,
Field2 = i.Field2
FROM
A JOIN inserted i ON (A.Field1 = i.Field1)
GO
INSERT INTO A VALUES (1, 'aaa')
INSERT INTO A VALUES (2, 'bbb')
GO
CREATE PROCEDURE UpdateA
@.Field1 int,
@.Field2 text
AS
UPDATE A
SET Field2 = @.Field2
WHERE
Field1 = @.Field1
return 1
GO
-- Error occurs here:
exec UpdateA 2, N'cccc'
-- No error occurs here:
DROP TRIGGER A_UpdateTrig
exec UpdateA 2, N'ddd'
DROP TABLE A
DROP PROCEDURE UpdateA
Running this produces the following result:
(1 row(s) affected)
(1 row(s) affected)
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionChec
kForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas would be appreciated. BTW select @.@.version returns:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)|||this could be to do with the following
1) the collation on your database is/was different to the server collation
2) is the column in question text(this data type could cause the issue
mentioned) and not ntext
can you confirm that the above is not true?
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:uyDxQIX7EHA.2572@.tk2msftngp13.phx.gbl...
> Moreover, you should consider looking into the WRITETEXT and UPDATETEXT
> statements as an alternative to strict UPDATES to TEXT data type
attributes.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Anthony Meehan" <anthonymeehan@.nospam.nospam> wrote in message
> news:32DB2A24-2AEE-4B81-9AC8-B0AD216D7114@.microsoft.com...
> Hi,
> I am receiving the following error when trying to use an 'instead of'
update
> trigger.
> SqlDumpExceptionHandler: Process 61 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
>
> It looks like something to do with calling stored procedures with text
> fields. The following test case illustrates the problem:
> CREATE TABLE A
> (Field1 int,
> Field2 text)
> GO
>
> CREATE TRIGGER A_UpdateTrig
> ON A
> INSTEAD OF UPDATE
> AS
> UPDATE A
> SET Field1 = i.Field1,
> Field2 = i.Field2
> FROM
> A JOIN inserted i ON (A.Field1 = i.Field1)
>
> GO
>
> INSERT INTO A VALUES (1, 'aaa')
> INSERT INTO A VALUES (2, 'bbb')
> GO
> CREATE PROCEDURE UpdateA
> @.Field1 int,
> @.Field2 text
> AS
>
> UPDATE A
> SET Field2 = @.Field2
> WHERE
> Field1 = @.Field1
> return 1
> GO
>
> -- Error occurs here:
> exec UpdateA 2, N'cccc'
>
> -- No error occurs here:
> DROP TRIGGER A_UpdateTrig
> exec UpdateA 2, N'ddd'
> DROP TABLE A
> DROP PROCEDURE UpdateA
>
> --
> Running this produces the following result:
>
> (1 row(s) affected)
>
> (1 row(s) affected)
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCh
eckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
>
>
> Any ideas would be appreciated. BTW select @.@.version returns:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>