ID Number: Q82945
1.10 1.11
OS/2
buglist1.10 buglist1.11 fixlist4.20
Summary:
Problem ID: PRSQL9202021
SYMPTOMS
Running a COMMIT TRANsaction statement results in the following
error:
Could not find row in Sysindexes for dbid '2', object xxx, index yy.
Run DBCC CHECKTABLE on Sysindexes.
(Msg 602, Level 21, State 3)
The SQL Server is terminating this process.
Attempting to log back into SQL Server again and issue any query
yields the following error:
Logical page xxx in database 2 is already hashed.
(Msg 813, Level 20, State 1)
The SQL Server is terminating this process.
After this, subsequent attempts to log into SQL Server and issue
queries are successful.
CAUSE
SQL Server incorrectly handles the processing of stored procedures
that create and use temporary tables inside user-defined
transactions that do not have corresponding COMMIT TRAN or ROLLBACK
TRAN statements for the BEGIN TRAN statement.
The following sequence of steps will yield the above errors:
create procedure myproc
as
create table #temp (col1 int)
begin tran
insert #temp values (1)
go
exec myproc
go
This will produce error 266, indicating that a transaction was
begun in the procedure, but did not have a corresponding COMMIT or
ROLLBACK.
Next, carry out a "COMMIT TRAN" to commit the open transaction. SQL
Server will return error 602, and the client session will be
terminated. Attempting to log back into SQL Server and issue any
query will result in error 813, and again the client session will
be terminated. After this, subsequent login attempts are
successful.
WORKAROUND
To work around this problem, ensure that all BEGIN TRANsaction
statements always have corresponding COMMIT TRANs or ROLLBACK
TRANs.
STATUS
Microsoft has confirmed this to be a problem in SQL Server versions
1.1 and 1.11. This problem does not occur in SQL Server version
4.2.
Additional reference words: 1.10 4.20