Post reply

Fire hose mode SQL 2K Enterprise manager

  • February 5, 2004 at 9:28 am

    #101868

    On some databases, when trying to update data via enterprise manager, I am recieving a message saying that the transaction is not possible while in fire hose mode. Is there a way that this can be turned off?

    Hi,

    On SQL Server 2000, when in Enterprise manager and I am trying to update some data, I am getting a message stating that it is not possible in 'fire hose' mode. How can I turn off this mode?

    Any help would be appreciated

    Thanks.

  • February 5, 2004 at 2:31 pm

    #492961

    The answer is YES!

    EM uses something called 'firehose' cursor, IIRC.

    The term "firehose" reflects the way that the server blasts rows to the client as long as the client keeps processing them so that there is room in the network buffer to send more results

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 6, 2004 at 3:20 am

    #493018

    Hey, yesterday evening there was an empty message!

    Well, after your editing, I must review my answer to NO. This can't be turned off.

    The alternative (IMHO also the better one) is NOT to use EM to edit data in your tables. Use QA or some front-end app. EM sometimes has nasty side-effects.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 6, 2004 at 3:38 am

    #493019

    Thanks Ill use Query Analyser

  • February 6, 2004 at 4:09 am

    #493020

    I agree with Frank, opening tables in EM and making edits creates an obscene amount of row locks. To prove this, run profiler, open the table in EM and start scrolling.

    HTH

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

  • February 6, 2004 at 5:57 am

    #493038

    I have had this issue before myself. The dangerous thing about editing a table via Ent. manager is if you make a change to ANY data in a column it has been updated no verifications at all... If you do use it and get a 'firehose' message I think that if you move your cursor to another row then click exit it will save the change. You have to get out of the row you made a change to .

  • February 6, 2004 at 6:21 am

    #493047

    Thanks for the info, im in the process of learning TSQL, so im still manually selecting and updating data using E.M, but when I improve ill be a bit more proficient with Q.A and will use this as my primary query tool.

  • February 6, 2004 at 8:18 am

    #493089

    I believe you can edit the table in EM if you scroll to the last row of the table and then go back and make your changes.  I agree this is not a good practice.

  • February 6, 2004 at 9:00 am

    #493097

    Excellent, just tried your advice. Works a treat (but obviously i'm going to refrain from using it)

  • February 9, 2004 at 10:20 am

    #493278

    That trick works because it causes all the rows in the table to be read. "Firehose" mode is in play in EM when you have more rows in the table than can be displayed in the screen or immediately buffered for your use. Obviously you can't use EM to edit fields in a database with a very large number of rows. It works OK for small, static tables (i.e. a table of Name Prefixes: Mr. Ms. Mrs. etc), but not for large dynamic data tables. Even then you have to make your changes and then close the table quickly or you could lock out users unless your code is written with the (NOLOCK) hint on all your accesses to the static tables.

    Yes, it gets complicated.

    You're much better off updating large tables in QA.

     

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply