Article ID: 173215
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q173215
BUG #: 17193 (WINDOWS: 6.5)
SYMPTOMS
Attempts to perform a COMMIT TRAN may fail with Msg 602 in a database. The transaction is rolled back and the connection broken. DBCC CHECKDB and DBCC NEWALLOC do not report any corruption that did not previously exist.
CAUSE
The following statements occur 13 times or more in the transaction:
- CREATE TABLE
- Two or more INSERTs into this table
- DROP TABLE
These statements may be within a stored procedure. An example of a system stored procedure that performs these operations is sp_statistics.
The following is an example of the error message in the errorlog:
Occasionally, if the above statements are executed a sufficiently large number of times in a transaction, the error log may also contain the following message:
WORKAROUND
To work around this problem, do any one of the following:
- Make the transactions shorter.
- Create an index on each temporary table involved in the transaction.
- Ensure that the statements or stored procedures that must be executed more than 13 times in a transaction do not meet the criteria outlined above.
- Revert to a service pack revision earlier than SQL Server 6.5 Service Pack 3.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:
197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a
For more information, contact your primary support provider.
MORE INFORMATION
The following script reproduces the problem scenario:
DROP PROCEDURE sp_test GO CREATE PROCEDURE sp_test AS SELECT au_id INTO #myauthors FROM authors INSERT INTO #myauthors SELECT au_id FROM authors DROP TABLE #myauthors GO OR CREATE PROCEDURE sp_test AS create table #myauthors (au_id varchar(11)) INSERT INTO #myauthors (au_id) SELECT au_id FROM authors INSERT INTO #myauthors (au_id) SELECT au_id FROM authors DROP TABLE #myauthors GO Declare @cnt int Select @cnt = 0 begin tran while (@cnt < 14) begin exec sp_test select @cnt=@cnt+1 end commit tran
Additional query words: prodsql sp sp3
Keywords: kbbug kbfix kbusage KB173215