Post reply

How to set a view as Read only

  • November 28, 2008 at 8:23 am

    #196439

    Hi,

    I have got a view in Sql Server 2000 which is a union of 2 select statements.

    EX:

    CREATE VIEW VTEST AS

    SELECT A,B FROM TABLE1

    UNION

    SELECT A,B FROM TABLE2

    When I do a query on this view VTEST, it is setting it in Update Mode.

    Could you please let me know how to set this as READ ONLY.

    Thanks & Regards,

    Shailaja

  • November 28, 2008 at 8:58 am

    #904855

    SHAILAJA_SOMISETTY (11/28/2008)

    When I do a query on this view VTEST, it is setting it in Update Mode.

    Please explain in more detail what this means and how you can tell it.

    That View is certainly not updateable.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • November 28, 2008 at 10:15 am

    #904894

    CREATE VIEW VTEST AS

    SELECT A,B FROM TABLE1

    UNION

    SELECT A,B FROM TABLE2

    This view cant do update :D, it is read only 🙂

  • November 28, 2008 at 5:22 pm

    #905014

    CrazyMan (11/28/2008)

    CREATE VIEW VTEST AS

    SELECT A,B FROM TABLE1

    UNION

    SELECT A,B FROM TABLE2

    This view cant do update :D, it is read only 🙂

    Ummm... not if it's a partitioned view which IS fully updateable. Otherwise, I agree.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 28, 2008 at 6:43 pm

    #905022

    Jeff Moden (11/28/2008)

    CrazyMan (11/28/2008)

    CREATE VIEW VTEST AS

    SELECT A,B FROM TABLE1

    UNION

    SELECT A,B FROM TABLE2

    This view cant do update :D, it is read only 🙂

    Ummm... not if it's a partitioned view which IS fully updateable. Otherwise, I agree.

    Partitoned Views have to be UNION ALL, IIRC.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • November 28, 2008 at 7:31 pm

    #905026

    Dang... some of the things I forget... thanks Barry.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 28, 2008 at 7:34 pm

    #905027

    Actually - one way to make sure that the recordset is read only is to use something like DISTINCT in there (assuming that in itself doesn't alter your query in an undesirable way). Which is essentially the same reason why the original query is read only....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • December 1, 2008 at 5:32 am

    #905525

    Hi,

    I have created a DB Link 'testsqlserver' from Oracle to Sql Server.

    I am doing a query from a view vcustomers which is the Sql Server database:

    SELECT * FROM vcustomers@testsqlserver.

    I am doing this query from TOAD.

    When I close the TOAD session, it asks me:

    ********************************************************

    ' A session that has pending transactions is about to close'

    Commit or Rollback.

    ********************************************************

    I am unable to understand why this is happening as I am just doing a select query.

    Please advise.

    Thanks & Regrads,

    Shailaja

  • December 1, 2008 at 6:56 am

    #905585

    This is an entirely different question and should be re-posted as a new thread, probably under "Working with Oracle".

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 1, 2008 at 7:00 am

    #905589

    OK. Will do.

    Thanks & Regards,

    Shailaja

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

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