Table added columns-sync successfully-but col. still missing
toebens
Posts: 10
hi,
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
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
Comments
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
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!