Post reply

Problem with dependencies

  • February 12, 2003 at 7:10 am

    #159131

    Sqlserver 2000 lost dependencies when views is altered. After this all automatic scripts (using dependencies) work whith errors. I find a way for automatic refresh a good dependencies.

    See example :

    CREATE TABLE ta (A1 int, A2 int)

    GO

    CREATE VIEW va AS SELECT * FROM ta

    GO

    CREATE VIEW va2 AS SELECT * FROM va

    GO

    exec sp_depends 'va2'

    GO

    name type updated selected column

    dbo.va view no yes A1

    dbo.va view no yes A2

    This is correct dependenci.

    ALTER VIEW va AS SELECT * FROM ta

    GO

    exec sp_depends 'va2'

    go

    Object does not reference any object, and no objects reference it.

    The dependenci is lost!!!

  • February 12, 2003 at 7:14 am

    #448736

    Try sp_refreshview.

  • February 12, 2003 at 7:40 am

    #448738

    exec sp_refreshview 'va'

    lost dependencies of va2 !!!

    I need to execute sp_refreshview in order of dependencies !!!???

  • February 12, 2003 at 7:59 am

    #448739

    Your 'va2' is depend on 'va' that depends on 'ta'. If there are any changes in either 'ta' or 'va', you have to refresh 'va2' too.

  • February 12, 2003 at 8:19 am

    #448742

    My example is very simple. Imagine this with database having hundred of views !!

    I dont understand why sqlserver not have automatic procedure. After you make the change dependencies is lost and you not have way to see how views need refresh, is à good way to go around in circles !

  • February 12, 2003 at 8:32 am

    #448746

    You may put change control in place for any this kind of changes.

    Or write a script and schedule it run regularly to refresh all your views.

  • February 13, 2003 at 10:43 am

    #448906

    Hi,

    I Finaly write a general procedure to refresh all views of current database in good order

    This procedure work for any database in SQLSERVER 2000.

    See Resources/Scrips SQL Server 7.0/2000 T-SQL aids or Maintenance and Managment

    Thanks to Allen_Cui for his help.

  • February 13, 2003 at 10:44 am

    #448907

    Hi,

    I Finaly write a general procedure to refresh all views of current database in good order

    This procedure work for any database in SQLSERVER 2000.

    See Resources/Scrips SQL Server 7.0/2000 T-SQL aids or Maintenance and Managment

    Thanks to Allen_Cui for his help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply