Firehose cursor

[manohars]

What is a firehose cursor? When 2 rows in a table have the same set of values and when we try to delete one of the rows, why it says "Cursor is in firehose mode".

Thanks,
Manohar

 

[hmckillop]

Quoted from the mighty Kalen Delaney

Firehouse mode means that SQL Server is returning all the rows to the client
in a fast forward-only cursor, so you cannot manipulate the individual rows.

How you change this behavior depends on what tool you are using, what API
you are using and what version of SQL Server you are running, so if you
supply that information someone may be able to advise you.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

And another quote this time from Hal Berenson

"Fast, Forward-Only" is one of
the cursor types you might use instead of "Firehose" (which isn't a true
cursor type). So Firehoses are indeed fast, and forward-only, but the
naming can be ambiguous. The proper name for Firehose is "Default Result
Set".

With a Default Result Set the server pumps the results from a
query/stored-procedure to the client in total using as little network
protocol overhead as possible. The client can take no action until it reads
the entire result set, as the efficiency is achieved via a highly optimized,
minimal overhead, half-duplex protocol (meaning the server won't even listen
for a client response until the result set is consumed). The effect is
somewhat like drinking from a firehose, hence the nickname.

Each client API, or object model, provides one or more means of dealing with
the Default Result Set (such as client-side cursors) or one of the
alternative Server Cursor models. So depending on which API or object
model, and exactly what you are trying to achieve, there are multiple
possible solutions. We need more information to help.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[manohars]

Thanks a lot.

In SQL Server 2000, it says "Key column information is insufficient or incorrect. Too many rows were affected by update." when a row is deleted, which has the same set of values as of some other row. I guess that the same is returned as "Cursor is in firehose mode" in SQL 7.0.

Any idea, when such a message comes?

Thanks,
Manohar

 

[TysonLPrice]

When that happens editing from Enterprise Manager scrolling up and down in the table usually ends it.

 

[donutman]

Manohar, I believe you answered your own question.

...when a row is deleted, which has the same set of values as of some other row.

That message doesn't come up (to the best of my knowledge) when you have a primary key. The system needs a way to distinguish between multiple rows that are duplicates. In EM you can delete the first one, but upon attempting to delete the next one you get red flagged. If you write a Delete query you will end up deleting all of them. Does this answer your question?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[manohars]

Thanks.

But when the row values are duplicated, SQL server will not permit even deletion of one row, as it doesn't know to identify the row id of that row.

Am I right?

Thanks,
Manohar

 

[donutman]

Yes you seem to be right (just tested it). That's interesting, because I distinct recall a situation where I got the message after the first update. Hmm, maybe it was an update...that would make more sense, maybe an update that creates a duplicate record.
Anyway it's annoying and only goes to demonstrate that it's best to aways have a PK field in a table.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]