Presents your 
SQL SERVER E-NEWSLETTER for September 3, 2002

<------------------------------------------->

Sync sysdepends for data integrity

The system table, sysdepends, can be out of sync with the true
dependencies between database objects. It's important to create a schema in a
dependent order if using sysdepends or the system procedure, SP_DEPENDS. SQL
Server 6.5 would not allow a procedure to be created when it depended
upon a procedure that wasn't present.

On the other hand, SQL Server 7.0 and 2000 will allow the procedure to
be created, and the SP_DEPENDS system procedure will not report correct
results.

Below is a script illustrating the shortcoming of sysdepends data
integrity with the database's schema dependencies. It creates a procedure that
executes another procedure that is yet to be created. When that occurs,
SQL Server generates an information message stating that the sysdepends
table will not add a row for the current procedure. However, the script
will execute the SP_DEPENDS system procedure for both procedures created,
and it will report there are no dependencies.

CREATE PROCEDURE X_MyProc1
AS
BEGIN
     EXEC X_MyProc2
END
GO
/*  INFORMATIONAL MESSAGE RETURNED:
Cannot add rows to sysdepends for the current stored procedure because
it depends on the missing object 'X_MyProc2'. The stored procedure will still be created.
*/
CREATE PROCEDURE X_MyProc2
AS
BEGIN
     SELECT CURRENT_TIMESTAMP
END
GO
EXEC sp_depends X_MyProc1
EXEC sp_depends X_MyProc2
/*
Object does not reference any object, and no objects reference it.
*/
GO
DROP PROCEDURE X_MyProc2, X_MyProc1
GO

----------------------------------------