Dependencies are lost when alter an object

Hello,
I have an old problem but now it became ugly...
I have an object (for example a function) that is used in some views. If I am looking on dependencies, it shows me the 8 views whcih depends on it. But if I edit the function in Query Analyser and run the script (no modification on the script), it deletes
all the dependencies for the 8 views on it... The problem is worst because when I generate the initial snapshot for a merge replication, it will affect the generation order of the scripts and I get error when trying to create the views (on subscriber), s
aying that the function doesn't exist in database...
Any ideea how can I preserve the dependencies?
Thanks in advance
Catalin,
I've also wondered what is the cause of this. There was mention a while back
that it is caused by using enterprise manager to do certain changes to
objects rather than using QA. Also, it has been mentioned that renaming
objects can corrupt dependencies.
I haven't found a definitive article explaining this, but as a workaround,
if you have this problem, you might want to run sp_refreshview on your views
prior to replication.
HTH,
Paul Ibison
|||I don't work with EM, only with QA.
For example: I run this script, to create 2 functions:
CREATE FUNCTION dbo.Child () RETURNS TABLE RETURN (SELECT 1 AS Col)
GO
CREATE FUNCTION dbo._Parent () RETURNS TABLE RETURN (SELECT * FROM dbo.Child ())
GO
As you can see _Parent depens on Child.
At this moment everything is fine on dependencies (each one shows the other)
But if I execute the next script:
ALTER FUNCTION dbo.Child () RETURNS TABLE RETURN (SELECT 1 AS Col)
I loose all the referencies. And I need to generate the initial snapshot for a replication. And because no dependencies found, it will create the function in the order of names (and "_Parent" is before "Child") and when it tries to create the database (on
subscriber) I get the error because it tries to create _Parent before Child...
|||Hi Catalin,
If you don't have very many complex dependencies among functions, you may be
able to restore the dependency information by altering _Parent after you
alter Child.
HTH
-Raymond
"Catalin" <anonymous@.discussions.microsoft.com> wrote in message
news:0D8E0108-CACC-4491-A1B2-DFCD4FE641C6@.microsoft.com...
> I don't work with EM, only with QA.
> For example: I run this script, to create 2 functions:
> CREATE FUNCTION dbo.Child () RETURNS TABLE RETURN (SELECT 1 AS Col)
> GO
> CREATE FUNCTION dbo._Parent () RETURNS TABLE RETURN (SELECT * FROM
dbo.Child ())
> GO
> As you can see _Parent depens on Child.
> At this moment everything is fine on dependencies (each one shows the
other)
> But if I execute the next script:
> ALTER FUNCTION dbo.Child () RETURNS TABLE RETURN (SELECT 1 AS Col)
> I loose all the referencies. And I need to generate the initial snapshot
for a replication. And because no dependencies found, it will create the
function in the order of names (and "_Parent" is before "Child") and when it
tries to create the database (on subscriber) I get the error because it
tries to create _Parent before Child...
|||Yes, but... unfortunately I have a database with more that 1000 objects and I need to replicate it... In this case I need to "reconstruct" the dependencies tree. The simplest way that I think is to develop a small VB application that will try to re-establ
ish the order (trying each script to execute in a blank database and to determine the missing object)...