Article ID: 114288
Article Last Modified on 2/7/2002
APPLIES TO
- Microsoft SQL Server 4.21a Standard Edition
This article was previously published under Q114288
BUG# NT: 748 (4.2)
SYMPTOMS
When a Remote Procedure Call (RPC) is made to execute a stored procedure which updates a table with a trigger containing a call to another stored procedure which in turn contains an unmatched COMMIT TRAN or ROLLBACK TRAN statement, SQL Server may generate an Access Violation and terminate.
This does not happen if the ROLLBACK TRAN or COMMIT TRAN statement itself is contained in the trigger, instead of being in the stored procedure that is called by the trigger.
For example, consider the following script:
CREATE TABLE testtrig ( i INT, j INT )
go
CREATE PROC myproc @P0 INT, @P1 INT, @P2 INT AS
UPDATE testtrig SET i=@P0,j=2 WHERE i=@P1 AND j=@P2
go
CREATE PROC trigproc AS ROLLBACK TRAN /* or a COMMIT TRAN */
go
CREATE TRIGGER trg ON testtrig FOR UPDATE AS
EXECUTE trigproc
go
Then executing myproc stored procedure remotely will cause SQL Server to terminate, generating an access violation.
WORKAROUND
TO workaround this problem, avoid calling the stored procedure within the trigger if it contains an unmatched rollback (or commit). Instead, place the rollback (or commit) directly within the trigger.
STATUS
Microsoft has confirmed this to be a problem in SQL Server version 4.2 for Windows NT. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.
Additional query words: Access VB Visual Basic ODBC gp-fault trap
Keywords: kbprogramming KB114288
