SQL Server:: Corrupt sysdepends can create problem in DTS
SQL Server internally keeps tracks of database objects and their inter-dependencies. In routine operations; I guess this information is not being used; however if you Script your database (SQL-DMO API) this information is used to order the DML queries. The information of the database objects and their inter-decencies are stored in two special system tables, sysobjects and sysdepends. Here are simple queries to find out what information is available.
If you are using Data Transformation Services (DTS) to transfer SQL database from one system to another; this information becomes important, as database structure is created using this information. Today; while transferring database from one system to another; I found out that the information in sysdepends is not correct; as I was getting the following error.
I script-out the database; and execute the generated DML queries on the destination system and identified the exact problem. The two views were using another view which is not being created earlier before the two views and hence I was getting errors in the DTS job.
Lastly, I didn’t inserted the rows into sysdepends, I dropped and re-created the views in the source location; and doing it automatically fixed the sysdepends entries. I was lucky that those were just views. After this; I re-ran the job and everything went smooth.
One unanswered question; if we drop/re-create an object (view for instance) the security configurations remain intact?