Tips and Tricks for:  SQL Server

The Problem:   Replication fails with Error 208 "Unable to replicate a view or function..."

This error occurs when a script for a view or function fails because a database object that the view or function depends on hasn't been defined in the database when the script is run.  The most common cause of this problem is that the missing view or function doesn't have an article in the publication.  Check this first.  If you swear up and down that every required object has a proper article than move on to the solution.

Here's what the problem looks like including the distribution agent error details:

Solution: Use sp_depends to check for missing dependency information and recreate it if necessary.

As confirmed by MS KB article 11533, SQL Server can loose dependency information when a table, view, or function is recreated.  The article only covers tables but this applies to views and user-defined functions (UDFs) as well.  Information in sysdepends points to other database objects using the id column from sysobjects.  When the object is dropped the ID becomes meaningless.  When the object is recreated, it gets a new object ID and the old dependency information remains invalid.

The sort term fix

Figure out which object couldn't be created.  This is a problem because the replication error detail only tells you which object is missing.  To find out which objects depend on the missing object use a UDF from Volume 1 Issue 13 of the T-SQL UDF of the Week Newsletter, coded as such:

    select * from udf_SQL_SearchDBObjectsTAB ('missingobjectname', default)

This will show all the objects that reference the missing object.  You may have to research all of them until you find the one with the missing dependency information.  To do that.....

For the object that didn't get created correctly, run sp_depends as such:

             exec sp_depends 'myview'

Compare the dependency information to the script and the disparity should be pretty obvious.  Drop and recreate the object that could not be created in the snapshot and run sp_depends again to be sure the dependency information is correct.

A Long Term Solution

To prevent these types of problems you might consider creating views and UFDs WITH SCHEMABINDING.  Schemabinding will prevent changes in objects that are depended on from changing.  It can be annoying to work with schema bound objects because to make a change to an object that is schema bound, the schemabinding must be removed.   However, it works and would have prevented the problem in the first place.  Besides.  when replication is used you can't change the object definitions anyway, except for adding and removing columns with sp_repladdcolumn.