Professional Web Applications Themes

Adding a column to a table breaks my SP - Microsoft SQL / MS SQL Server

A very perplexing and unfortunate thing has happened to our database. All of a sudden, a stored proc that we've been using for months is showing an incorrect value in one column. This DB is still under development, so new elements are continually being added. Knowing this, I started working backwards, eliminating newer columns until I got the results as before. Now I find that the proper result hinges on whether I've added a column to a particular table. It doesn't matter what I name the column or what the data type is. The SP in question involves this table, ...

  1. #1

    Default Adding a column to a table breaks my SP

    A very perplexing and unfortunate thing has happened to our database.
    All of a sudden, a stored proc that we've been using for months is
    showing an incorrect value in one column. This DB is still under
    development, so new elements are continually being added. Knowing
    this, I started working backwards, eliminating newer columns until I
    got the results as before.

    Now I find that the proper result hinges on whether I've added a
    column to a particular table. It doesn't matter what I name the
    column or what the data type is. The SP in question involves this
    table, but not the new column. The name of the new column does not
    coincide with any other column in the DB. Within the SP, the table is
    accessed in a join within a view, which is in turn joined to another
    view (containing a join of tables and/or views).

    I've tried forcing recompilation of the table, the SP, and all the
    views used by the SP. We have SQL running on another box, where the
    same DB exists (same schema, somewhat different data). The same issue
    occurs there. That box actually contains a version of the DB that,
    sometime back when a particular schema version was deemed stable, we
    backed up and then restored into SQL on my development box.

    Am I hosed? Do I have to re-create the DB from scratch? Or is there
    some way I can cause a re-compile of everything in the DB? Any help
    would be greatly appreciated.

    pt
    Paul Guest

  2. #2

    Default Re: Adding a column to a table breaks my SP

    "Andrew John" <com> wrote in message news:<u#phx.gbl>... 

    Ok, I ran checkdb with REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS (not
    at the same time, of course), and I came up with no errors. I tried
    this both with and without the offending column.

    I ran checktable on the table containing the offending column, and
    came up with no errors. Same deal with syscolumns.

    The BOL says you can run checktable on a view, but this didn't work
    for the view I wanted to check. I got the following error:
    "Could not find a table or object named 'vXYZ'. Check sysobjects."
    The view appears in sysobjects. Don't know what that means.
     

    I'm not sure what this means. Are you suggesting that I eliminate all
    the column aliases in the views (that worked just fine up until now)?
    The SP that fails explicitly names individual columns in the views
    that it joins, and gives the columns some aliases which are used by my
    ASP code. Am I not correct in the assertion that adding a column
    should have no effect on extant views and SPs?

    Any further answers and suggestions are most eagerly solicited.

    pt
    Paul Guest

  3. #3

    Default Re: Adding a column to a table breaks my SP

    Paul,

    Sorry I wasn't clear. Code that uses select * will often break if another column
    is added, so no that doesn't apply if you have used named columns. Aliases
    also are unlikely to break anything.

    Dropping and recreating the views would be my next step.

    AJ


    "Paul Tamalunas" <com> wrote in message
    news:google.com... 
    >
    > Ok, I ran checkdb with REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS (not
    > at the same time, of course), and I came up with no errors. I tried
    > this both with and without the offending column.
    >
    > I ran checktable on the table containing the offending column, and
    > came up with no errors. Same deal with syscolumns.
    >
    > The BOL says you can run checktable on a view, but this didn't work
    > for the view I wanted to check. I got the following error:
    > "Could not find a table or object named 'vXYZ'. Check sysobjects."
    > The view appears in sysobjects. Don't know what that means.

    >
    > I'm not sure what this means. Are you suggesting that I eliminate all
    > the column aliases in the views (that worked just fine up until now)?
    > The SP that fails explicitly names individual columns in the views
    > that it joins, and gives the columns some aliases which are used by my
    > ASP code. Am I not correct in the assertion that adding a column
    > should have no effect on extant views and SPs?
    >
    > Any further answers and suggestions are most eagerly solicited.
    >
    > pt[/ref]


    Andrew Guest

  4. #4

    Default Re: Adding a column to a table breaks my SP

    schedule something like the following script every 15min:

    set nocount on

    declare sql varchar(8000)
    , view sysname

    select table_name
    into #tmpviews
    from information_schema.tables
    where table_type = 'view'

    while exists (select 0 from #tmpviews)
    begin
    select top 1 view = table_name
    , sql = 'exec sp_refreshview ' +
    quotename(table_name)
    from #tmpviews

    exec (sql)

    delete #tmpviews
    where table_name = view
    end

    HTH,
    </wqw>

    "Paul Tamalunas" <com> wrote in message
    news:google.com... 
    news:<u#phx.gbl>... 
    >
    > Ok, I ran checkdb with REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS (not
    > at the same time, of course), and I came up with no errors. I tried
    > this both with and without the offending column.
    >
    > I ran checktable on the table containing the offending column, and
    > came up with no errors. Same deal with syscolumns.
    >
    > The BOL says you can run checktable on a view, but this didn't work
    > for the view I wanted to check. I got the following error:
    > "Could not find a table or object named 'vXYZ'. Check sysobjects."
    > The view appears in sysobjects. Don't know what that means.

    >
    > I'm not sure what this means. Are you suggesting that I eliminate all
    > the column aliases in the views (that worked just fine up until now)?
    > The SP that fails explicitly names individual columns in the views
    > that it joins, and gives the columns some aliases which are used by my
    > ASP code. Am I not correct in the assertion that adding a column
    > should have no effect on extant views and SPs?
    >
    > Any further answers and suggestions are most eagerly solicited.
    >
    > pt[/ref]


    Vlad Guest

  5. #5

    Default Re: Adding a column to a table breaks my SP

    Ha! That's the ticket! That's exactly what I was looking for. I'm
    back up and running.

    I want to address the other responses in this posting:

    As it turns out, one of the views in my multi-view, multi-join stored
    proc had a "select *" which, based on your insight into the nuts and
    bolts of the View entity, explains where the first respondant was
    headed with the second of his two suggestions. Against his advice, I
    will freely admit to having a "select *" in that view, and I point the
    finger of blame squarely at the author of the New Riders "SQL 7 DB
    Design" book, which gives a "select *"-type as one example of creating
    views, and no warning that such a result could ensue. All it says, in
    fact, is "even if a view is created with a SELECT * statement, new
    columns added to the table aren't automatically added to the view,"
    when in fact it should say "For god's sake, don't ever use a SELECT *
    in a view unless you are prepared for the consequences."

    I want to acknowledge all respondants by saying thank you. I don't
    know who you people are, but you saved me from pulling out what hair I
    had left after struggling for an entire day on this issue, testing
    different scenarios & whatnot.

    Best regards,
    Paul T.

    "Amy" <com> wrote in message news:<phx.gbl>... 
    THVVM
    Paul Guest

Similar Threads

  1. Question Adding column in middle of a table
    By sriarun in forum Brainstorming Area
    Replies: 0
    Last Post: January 12th, 02:17 PM
  2. Table Breaks
    By Dave_Saunders@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 3
    Last Post: November 28th, 12:31 AM
  3. adding on a column...
    By nobuko_nagaoka@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 4
    Last Post: August 21st, 09:26 PM
  4. Insert into same table pulling one column from another table
    By Fred in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 12th, 09:54 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139