Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Unanswered: SELECT * view returning wrong column data

    SQL 2005 9.00.3402.00 (x64)

    As Above really when I select * OR select a single column from the view the wrong column data is returned.

    in SQL Management Studio when I expand the Columns of the view it reflects the old table structure not the new table structure.

    I can easily fix by compiling the view again but this would mean I would have to recompile all referencing views when I make a change to table structures.

    I've tried various DBCC Clean Buffers & drop cache with no effect.

    Is there a command to recompile all views & poss stored procs in a database.

    Any help or explanation would be appreciated

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This is why you should never use "select *" in a view.

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    I Absolutely agree Mc

    However it's one of those Jobs where we're developing on top of a vendor supplied product & select * in views is only a very small example of their horrible nasty DB practices.

    I can't change it.

    I suppose I could run a cursor using sys.views and sp_refreshview each time I apply the horrible computer generated TSQL DDL
    (The app uses a MMC Snapin Admin tool to make DDL changes because the front end is run by MetaData held in the main database)

    Any other ideas ?

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You can try sp_recompile on the base tables, but that would be about the same as looping through with sp_refreshview

  5. #5
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    For Info Implemented

    Code:
    CREATE PROCEDURE RefreshViews AS
     
    DECLARE @ViewName nVarChar(100)
    DECLARE csr_Views CURSOR FOR
     SELECT name FROM Sys.Views
     ORDER BY 1
     
    OPEN csr_Views
     
    FETCH NEXT FROM csr_Views INTO @ViewName
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
     --PRINT @ViewName + ' Refreshing'
     EXEC sp_refreshview @ViewName
     IF @@Error <> 0 GOTO err
     --PRINT @ViewName + ' Refreshed'
     FETCH NEXT FROM csr_Views INTO @ViewName
    END
     
    err:
    CLOSE csr_Views
    DEALLOCATE csr_Views
    twould have been nice if there was a MSoft Keyword for this

    Thanks

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Did you try DBCC FREEPROCCACHE?

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just curious

    What happens to the code that references the views?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Yup & DBCC DROPCLEANBUFFERS - neither sorted the prob
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  9. #9
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Brett it was Incredible really

    It actually returned the wrong data with no error

    ie where column a was swapped (in DDL order) with column b selecting column a would return column b data with column a header & no hint of an error - frightening
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You have to remember that when a plan like "Select *" is created by the query optimizer, it is not going by names of objects or columns. It is all translated to IDs in the background, so the query engine can go directly to the allocation maps, to pick up pages, and page headers to pick up column order and such. In these query plans there is no such thing as "all columns" any more than there would be "all tables". So, a snapshot of the column list is created by the query optimizer, and until that plan is fluished, that is the plan that will be used.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    Selet * From *.*.*.*
    :d
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Descarte would be proud of you for that. The rest of us would want to lynch you, though ;-).

  13. #13
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Quote Originally Posted by georgev
    Code:
    Selet * From *.*.*.*
    :d
    It doesn't work

    I was wondering what kind of result it could return... :P

    answer: none
    Less is more.
    How long is now?
    http://www.lesouterrain.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •