Sid,

You are correct. There are several levels of complexity when discussing dependencies:
1. Object dependencies inside a single database.
2. Cross-DB object dependencies (on the same server).
3. Cross-DB and Server dependencies (e.g., over linked server, RPC calls, OPENROWSET commands, etc.).
4. Dependencies between a middle-tier or application tier and the database objects.

As mentioned in the article, the proposed solution was written to address #1. The other #2-#4 dependency types are beyond the scope of this article.

Again- you are absolutely correct. The article indeed does not address cross-DB dependencies.

Omri.

Omri Bahat 10/19/2007 9:10:49 AM

Mordechai,

In SQL Server 2005 Microsoft introduced the procs sp_refreshview (for views) and sp_refreshsqlmodule for other database object types. The sp_refreshview does prompt the side effect of updating dependencies of objects related to the view and not only the view; similarly with sp_refreshsqlmodule. To the best of my knowledge, both techniques indeed prompt the recompilation of the object since SQL Server updates the system meta-data related to these objects (thus recompilation is inevitable).

Hope this helps.

Omri.

Omri Bahat 10/19/2007 9:10:30 AM

Very nice but the SQl2K still does not work for dependencies in cross-databases. I have code like the following:
--openquery to Dtitletemp for CAE match
exec (" insert into " + @TDBtemptbl + "
(" + @TDBtempflds + ")
select " + @TDBtempflds + " from openquery(" + @acServer + ",
'select " + @TDBtempflds + "
from Dfrndis.dbo.vwArwEntitled
where " + @TDBwhereIN + "
')
")

and the table in the from clause was not found

Sid

jcasement 10/3/2007 1:25:42 PM

Hi I was recently told that there is a way to update information in sysdepends table. (but they did not tell me how). I am assuming that they did not mean recopiling all SPs. Is there a way to that?

mordechai 8/10/2007 5:48:44 AM