Firehose mode

[Eduu]

Hi,
I have a problem trying to edit my records in an SQL Server 7 database from Enterprise Manager. I right click on the table select open table - return all rows. When I try to save my changes it gives me an error - Transaction cannot start while in firehose mode. How can I work round this?
Thanks.

 

[AndyMoss]

From MSDN:

Some providers, such as SQL Server, implement a forward-scrolling, read-only (or 'firehose') cursor mode, meaning that they can efficiently retrieve data by keeping a connection open. When working with such providers, the connection could be blocked by another user's transaction.

The following examples demonstrate scenarios that result in errors.

Example 1

dbConn.Open "DSN=SQLForum;UID=sa;PWD=;"
dbConn.BeginTrans
RS.Open "SELECT * FROM Message", dbConn
Set dbCmd.ActiveConnection = dbConn

The problem is that the command object's ActiveConnection is being set to a connection that is forward-scrolling and in 'firehose' mode. This is the same connection involved in the batch mode. The error from the provider will only appear in the Err object, and it will return as unspecified. For example, with the ODBC Provider, you will get "Unspecified error".

Example 2

dbConn.Open "DSN=SQLForum;UID=sa;PWD=;"
RS.Open "SELECT * FROM Message", dbConn
dbConn.BeginTrans

The problem here is that the connection is forward-scrolling and in firehose mode, so it cannot be put into transaction mode. The error returned in the Errors collection from the provider will indicate that it is operating in firehose mode, and can't work in transaction mode. For example, with the ODBC Provider against Microsoft SQL Server, you will get the error "Cannot start transaction while in firehose mode".

Example 3

dbConn.Open "DSN=SQLForum;UID=sa;PWD=;"
RS.Open "SELECT * FROM Message", dbConn
Set dbCmd.ActiveConnection = dbConn
dbConn.BeginTrans

The problem here is that the connection is in forward-scrolling firehose mode, so it cannot also be involved in a batch mode. The error returned in the Errors collection from the provider will indicate that the transaction could not be started. For example, with the ODBC Provider against Microsoft SQL Server, you will get the error "Cannot start transaction because more than one hdbc is in use".

Hope this helps....

 

[AndyMoss]

And the solution from SQL Magazine:

When using SEM to display the rows from a table, all rows are returned by a "firehose cursor"; however, only the rows that are displayed have been processed. A "firehose cursor" refers to how the server sends rows to the client as fast as the client can process them. Rows that are not displayed in the Enterprise Manager are not processed and, therefore, they remain in the network buffer.

The "Cannot start transaction while in firehose mode" error occurs when an OLE-DB provider attempts to perform a join transaction with results pending and while not in an updateable cursor mode.



WORKAROUND
Scroll all the way down to the last row of the table. This forces all the rows to be processed. You can then edit the row needed and execute the update.

 

[Eduu]

Hi thanks,
Ok this is great. One more thing. I thought we could change a database cursor setting somehow so we don't have this problem.