June 9, 2005 at 6:06 am
Hello,
I'm having a problem with a inline table function :
CREATE FUNCTION dbo.udf_func (@id int)
RETURNS TABLE AS RETURN (
SELECT v.* FROM v_func v WHERE v.id=@id
)
This function is based on the view v_func(which also has a "Select *" clause), which itself is based on other tables. When I change a column name in the underlying tables, or when I add a column, I use
EXEC sp_refreshview v_func
in order to update the view with the changes that were made to the underlying tables. This works as it is supposed to. However, the function udf_func does not show the new columns or the modified column names. I tried with
EXEC sp_recompile udf_func
which outputs
Object 'udf_funcr' was successfully marked for recompilation.
but even after restarting sql server (MSDE2000a), the column names/numbers still are incorrect.
If I modify the function by adding a space somewhere and save it again, the function outputs the correct column names.
How do I have to proceed in order to update functions which use 'Select *' syntax by means of TSQL code?
June 9, 2005 at 7:23 am
Has the view been refreshed when you run this?
June 9, 2005 at 8:01 am
yes, I did update the view with "EXEC sp_refreshview v_func". And it works, because when I do a simple "SELECT * FROM v_func", all columns are displayed correctly.
June 9, 2005 at 8:08 am
If you did EXEC sp_refreshview 'v_func'
then select * from v_func
then recompile the function
then select * from function, I think it would work.
June 9, 2005 at 9:06 am
That's what I thought, too, but unfortunately it doesn't. Here is my test case, I tried to make it as simple as possible:
CREATE TABLE [dbo].[_tblFunc] (
[idfunc] [int] IDENTITY (1, 1) NOT NULL ,
[func] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE FUNCTION dbo.udf__func ()
RETURNS TABLE AS RETURN
(
SELECT * FROM _tblfunc
)
--> I insert some data into _tblFunc
--> the following statements result in the same output
SELECT * FROM _tblFunc
SELECT * FROM dbo.udf__func()
So far, so good. Now I change the name of the second column in _tblFunc to something else (enterprise manager).
SELECT * FROM dbo.udf__func() -- still shows the old column name
EXEC sp_recompile udf__func
--> restarting manually sql server
SELECT * FROM dbo.udf__func() -- still shows the old column name
In my opinion, after restarting sql server, the new column names should be shown. Just in case something's wrong with my installation of sql server (msde2000a), can you (or anybody else...) confirm this behavior?
Thanks!
June 9, 2005 at 9:16 am
I confirm that behavior on sql server 2000 dev edition.
however this doesn't fail :
CREATE TABLE [dbo].[_tblFunc] (
[idfunc] [int] IDENTITY (1, 1) NOT NULL ,
[func] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE FUNCTION dbo.udf__func ()
RETURNS TABLE AS RETURN
(
SELECT idfunc, Func FROM _tblfunc
)
GO
--> I insert some data into _tblFunc
Insert into _tblFunc (func) values ('Test')
--> the following statements result in the same output
SELECT * FROM _tblFunc
SELECT * FROM dbo.udf__func()
--So far, so good. Now I change the name of the second column in _tblFunc to something else (enterprise manager).
exec sp_rename '_tblFunc.func', 'func_renamed', 'COLUMN'
SELECT * FROM dbo.udf__func() -- still shows the old column name
--fails because or renamed column, no recompile needed
GO
DROP FUNCTION udf__func
DROP TABLE _tblFunc
This is one of the many reasons why you should always name the columns you need in the select statement, it's a little more work but you reap lots of dividends on the long run.
June 9, 2005 at 9:25 am
I guess then there is nothing to do about that.
It would have been nice to be able to use the "SELECT *" statement in the function, for the simple reason of not having to edit all the functions whenever I add a field to a table. But since this is not working, I guess it's better to have a function that fails than a function that misbehaves...
Thanks for your help!
June 9, 2005 at 11:09 am
Another thing is that once you got something in prod, you shouldn't be adding columns all over the place all the time so this stuff just shouldn't happen. You should rework the app on a test server, script the changes to production and make sure THEY WORK. Then apply the changes on production an you're sure (almost) that nothing's gonna fail.
June 21, 2005 at 2:42 am
Though not adding columns "all over the place", sometimes there has a column to be added, a view to be altered, a stored procedure to be fine-tuned. In a large database, this means recreating all of the dependent objects only to reflect the changes that were made in the underlying objects. Having sql server behave like this surprised me, and I was wondering how other people were handling those kind of problems.
On top of the above mentioned problem, I found that in every case one should after altering an object recreate or alter all of its depending objects, be it only to get sql server to correctly display all of the dependencies between the database objects (haven't found any other solution to the broken dependencies problem). This is really disturbing: If I want to recreate all the objects that depend on a certain object A, I have to keep a list of all the depending objects BEFORE altering A because when altering A, sql server loses the list of the objects that depend on it.
Basically, whenever I alter any object, I have to go down the hierarchy chain and alter all of the dependent objects if I want to be sure that the correct columns and dependencies are displayed.
June 21, 2005 at 6:35 am
If by object you mean table, when losing the dependecies chain, that's simply because the server creates a new table, copies the data to it, recreate the indexes and constraints, drops the old table and rename the new one. Therefore that "new" table is really a new object that has no dependecies as far as the server is concerned. However I agree that they could have tried to copy them somehow.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply