Table added columns-sync successfully-but col. still missing

toebenstoebens Posts: 10
edited December 22, 2008 10:46AM in SQL Compare Previous Versions
i had to add two additional columns to a table on my developer database. a user defined function that queried the table with * for all columns of that table made some problems. i "modify"ed the UDF without actually altering anything - just pressing "execute" in management studio (mssql 2005!)
please see http://forums.asp.net/t/1359702.aspx for additional info about it.

everything was working on my developer machine + database after that. i wanted to sync it with the live DB but now i ran into similar problems:

i synced the table with the two added columns successfully but when i query the table in management studio on the live DB the two new columns won't appear - even sql compare isn't showing any differences on this table anymore. how to "refresh" the schema?

as a result of this i can't sync some other UDF functions that are querying this two new columns.

please help me.
greetings, toebens


  • Options
    the two new columns were added after the last (old) column. i had to use "force column order".

    however i had to split the sync into three steps:
    1) had to sync only the table with the two added columns with "force column order"
    2) i had to "modify" my existing UDF that queries every column of that table and press "execute" in management studio (without changing any character in the alter function script) - as long as i would not have done this: my UDF would not have returned the two new columns - even if it querys "*"
    3) had to sync other UDFs that are calling the UDF from step two

    the same problem:
    please see http://forums.asp.net/t/1359702.aspx for additional info about it.

    why do i have to manually call "alter function" first - without changing anything actually! - to get SQL Server to return the two new columns?

    do you have any hints/answers to my questions on the asp.net forum?

    thanks, toebens
  • Options
    Hi Toebens,

    If I understand the problem correctly, it may be related to a UDF that is out-of date and needs to be recompiled. I think that when you have SELECT * in a view or UDF, the SQL Server internally stores the explicit list of columns in place of *, so if columns were added, the function would need recompilation. Running the ALTER FUNCTION query is more than likely triggering a recompilation, which would explain the behaviour.

    However, you may try using SQL Server's sp_recompile stored procedure in place of ALTER FUNCTION as this would guarantee recompilation.

    I hope this helps!
Sign In or Register to comment.