BUG: Error Message "Transaction Cannot Start in Firehose Mode" Occurs When a Table is Open in SQL Server Enterprise Manager During a Failover

When you connect to a Virtual Server and open a table or a view in the SQL Server Enterprise Manager, if a failover occurs the following error message may display when you try to make changes to the data from Enterprise Manager:

When you right-click a table, and then click Return all rows, a firehose cursor is opened and only the rows that have been processed are displayed. After the failover occurs and after you reconnect to the virtual server, only a batch of the rows in the table are processed. The error message shown in the "Symptoms" section occurs when an OLE-DB provider attempts to perform a join transaction, with results pending, while not in an updateable cursor mode.

To work around this problem, use any one of these methods:

• Scroll down to the end of the table before you make any changes to the data in the grid.
• Before you initiate a failover, make sure that you are not displaying the results of any table or view.
• Use Transact-SQL, ISQL_W or Query Analyzer to write a query to update the row.
• Use an alternative interface to update the row (for example, Microsoft Access or Microsoft Query).

Microsoft has confirmed this to be a problem in SQL Server 2000.

If a failover occurs when the result set of a table or view is open, the SQL Server Enterprise Manager displays a message that states that the connection to the SQL Server has been lost. To reconnect to the Virtual Server, click OK. Open a SQL Server pane and try to make updates to the data by using an Update query. After the query runs, the data grid either becomes blank or dimmed. If you close and then reopen the SQL Server Enterprise Manager on the same node, it reflects the update that was made. However, you are not able to make any data changes through the Enterprise Manager by directly entering data into the grid.