Hi,
I have a problem with replication now. I changed the articles property on
publications to replicate not only tables but also views and functions. Then
on the failover server which is the subscriber, the replication job failed
with below msg:
' invalide object name 'xxx', unable to replicate a view or function since
the referenced objects or columns are not present in subscriber'.

I checked some miscrosoft documents, some of which are saying it is a bug
for schema only objects and can be fixed via applying latest service pack.
But our servers are packed with SP3 currently, so anyone have such similar
issues before? Any clues of trying to solve this problem is highly
appreciated.

thanks in advance.

I know this sounds stupid but have you confirmed that all the dependant
objects (tables) exist on the fail over server? OR is your problem
that the view depends on the function or vice verca.

I have experenced such interdependancy problems. If you look in the
SQL Server query analyser for object dependencies are all of them
shown? I have found that dependencies get lost!

The replication process inspects data in the sysdepends table to work
out the order to create the objects. If data is missing then the
objects get created in the wrong order and the process fails.

you must run an alter function / view / procedure to get SQL server to
rebuild the dependencies list for an object. BUT remember that when
you do this the dependency between the object you are modifying and the
things that depend on it will be lost! You can end up chasing yourself
in a circle!

eg

Create View vA
as select * from table A

Create View vB
as select * from vA

Create View vC
as select * from vB

so view vB depends on vA and view vC depends on vB

if you find that the dependency between vB and vA is missing you will
run Alter View vB ....

now you will find that the dependency between vA and vB shows but the
dependeny between vB and vC has been lost - you therefore need to run
alter view on vC to reinstate the dependency. Very annoying!

That said i have applied microsoft security rollup MS03-031 which
apprered to address a number of replication issues! see KB815495

http://www.microsoft.com/downloads/details.aspx?FamilyID=9814AE9D-BD44-40C5-ADD3-B8C99618E68D&displaylang=en

Regards

Alex

Yanping,
you might want to separate the publication and put views
in one new publication and functions in another. This then
means that the issues nicely explained by Alex won't
affect the flow of data in the main publication. To fix
this issue is not simple. In SQL 2005 we can specify the
article order, but the issue could still arise. You could
use sp_addscriptexec instead, or manuallt script out the
views and apply them on the subscriber.
Rgds,
Paul Ibison, SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Oh i did not mention that having applied MS03-031i have not experienced
any dependency problems, however this may simply be coincidence.

Thanks for the feedback. I did some digging and on
http://support.microsoft.com/default.aspx?scid=kb;en-us;810185
and
http://support.microsoft.com/common/canned.aspx?r=d&h=microsoft%20sql%20server%202000%20post-sp3%20hotfixes&ll=&sz=kbsqlserv2000presp4fix&fr=&du=&sd=gn&ln=en-us&cnd=1&vr=&cat=&vrl=&sg=&maxresults=200
there's no mention of dependency algorithms being updated, so I suspect this
is a coincidence, but who knows - it may be a side-effect of some other
unrelated hotfix.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)