|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
sp_recompile for user defined functionsHello,
I have to rerun a 'create function' script on the management console to renew stale references in the user defined function to external objects which had been changed. I am looking for a counterpart of sp_recompile or sp_refreshview for user defined functions (without schemabinding). My environment is SQL Server 2005 SP1. Thank you Y. Nakagawa Hi
Read this article http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx Show quote "Y. NAKAGAWA" <ynakag2005@community.nospam> wrote in message news:eI5sfcvtGHA.4752@TK2MSFTNGP02.phx.gbl... > Hello, > > I have to rerun a 'create function' script on the management console to > renew stale references in the user defined function to external objects > which had been changed. I am looking for a counterpart of sp_recompile or > sp_refreshview for user defined functions (without schemabinding). > My environment is SQL Server 2005 SP1. > > Thank you > > Y. Nakagawa Thanks Uri,
I read the article and it seemed that 'EXEC ... WITH RECOMPILE' or 'reconfigure' is suitable, but the former cannot be used for table-valued UDF and the latter seems to have no effect. I have table-valued UDFs which contain 'SELECT * FROM dbo.<synonym>', where each <synonym> is a synonym referencing to a table on another instance. When the schema of the tables on the latter instance is changed, table-valued UDFs return errors which read number of rows is different or something. Show quote "Uri Dimant" <u***@iscar.co.il> wrote in message news:uNXtYgvtGHA.1512@TK2MSFTNGP03.phx.gbl... > Hi > Read this article > http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx > > > > "Y. NAKAGAWA" <ynakag2005@community.nospam> wrote in message > news:eI5sfcvtGHA.4752@TK2MSFTNGP02.phx.gbl... >> Hello, >> >> I have to rerun a 'create function' script on the management console to >> renew stale references in the user defined function to external objects >> which had been changed. I am looking for a counterpart of sp_recompile or >> sp_refreshview for user defined functions (without schemabinding). >> My environment is SQL Server 2005 SP1. >> >> Thank you >> >> Y. Nakagawa > > Hi Y.Nakagawa,
From your description, I understand that: You use a synonym referencing another database table in SQL 2005 SP1. If the schema of the table is changed, it brings error. You want to know whether or not some functions are helpful like sp_recompile or sp_refreshview. If I have misunderstood, please feel free to let me know. In fact, this is a runtime issue, not appearing in compiling time. So even a stored procedure also can't find the error by sp_recompile. For such issue, I recommend that you write a program by try/catch statements to check and report such errors, otherwise you need to drop and recreate the synonym. If you have any other questions or concerns, please feel free to let me know. It's my pleasure to be of assistance. Charles Wang Microsoft Online Community Support ====================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== Thanks Charles,
I'm not sure that what 'runtime issue' means exactly, but when I run a 'ALTER FUNCTION' script of the UDF generated by the management studio, without any modification and recreating the synonym, the error disappears. Does this mean the issue is concerning compiling time? Show quote "Charles Wang[MSFT]" <chang***@online.microsoft.com> wrote in message news:Y8daJU6tGHA.3920@TK2MSFTNGXA01.phx.gbl... > Hi Y.Nakagawa, > > From your description, I understand that: > You use a synonym referencing another database table in SQL 2005 SP1. If > the schema of the table is changed, it brings error. You want to know > whether or not some functions are helpful like sp_recompile or > sp_refreshview. > If I have misunderstood, please feel free to let me know. > > In fact, this is a runtime issue, not appearing in compiling time. So even > a stored procedure also can't find the error by sp_recompile. > For such issue, I recommend that you write a program by try/catch > statements to check and report such errors, otherwise you need to drop and > recreate the synonym. > > If you have any other questions or concerns, please feel free to let me > know. It's my pleasure to be of assistance. > > Charles Wang > Microsoft Online Community Support > ====================================================== > When responding to posts, please "Reply to Group" via > your newsreader so that others may learn and benefit > from this issue. > ====================================================== > This posting is provided "AS IS" with no warranties, and confers no > rights. > ====================================================== > Hi,
Thanks for your response. Compiling time is just for compiling the script but not execute it. For example, you create a function and click the execute button on the toolbar. This process is just for checking syntax, creating the function and registering its information in database. Runtime issue means that this issue occurs when you execute it. For instance, "exec sp_configure". If you don't recreate the synonym, you can still compile the " alter function " script, but when you execute the function the error occurs. When you recreated the synonym, you corrected the schema reference, so you didn't need to modify your function scripts. Charles Wang Microsoft Online Community Support  |
|||||||||||||||||||||||
Other interesting topics