Firehoses in SQL Server

While modifying rows manually in a database with Enterprise Manager in SQL Server 2000, one may get this exciting error message: "Transaction cannot start while in firehose mode"

Even though this is better than “Error=47”, I think it would be nice with a bit more information since I assume that not all database workers are fluent with the firehose technique.

Clear as mud.

The quick and dirty workaround to the problem is to scroll to the very last row, and then back to make the edit. No kidding.

The reason for the error is that when the table rows are displayed, they use something called a firehose cursor. This is a quite misguiding name, since it’s not a cursor but rather a method to quickly move data to the requesting client. Firehose sends the requested data to an output buffer, and once that buffer is full, it waits for the client to get the data so the buffer can be filled again.

Since the records are only locked for the time it takes to transfer to the buffer, it will boost concurrency and performance. Without any more technical details, this gives that only the displayed rows are being processed. The rest of the rows are stuck in the network buffer, preventing the transaction from being completed. By scrolling to the end, the rest of the rows are processed and the transaction can be completed. Clear as mud.