INF: Initial Snapshot May Fail with Invalid Object Name When You Use User Defined Functions and Computed Columns (302115)

SUMMARY

The initial snapshot may fail when applied at the subscriber if the publication contains an article that uses a user defined function as a computed column. The error message that occurs is:

Error 208: 'Invalid object name %function_name%'

The error occurs when you use snapshot, merge, or transactional publications. The error occurs when the Distribution Agent or the Merge Agent attempts to apply the initial snapshot at the subscriber.

MORE INFORMATION

The Snapshot Agent scripts out the user defined function if the function is included as an article in the publication. However, when the snapshot is applied at the subscriber, the user defined function is not created until after the tables are created on the subscriber. Therefore, tables that use a user defined function as a computed column are not created and the error occurs:

Error 208 'Invalid object name %function_name%'

The order of the snapshot scripts applied at the subscriber is by design. User defined functions that reference tables are not created unless the table exists.

To apply the snapshot successfully at the subscriber:

Remove the user defined function as an article to the publication.
Use Enterprise Manager and script out the user defined function as a SQL script.NOTE: Before you subscribe to the publication, use Enterprise Manager and view the Publication properties.
On the Snapshot tab select Before applying the snapshot, execute this script, and then run the script that contains the user defined function definition. If you select the Before applying the snapshot, execute this script property, subscribers must be running SQL Server 2000.

When a user defined function is included in a publication, any changes to the function definition are only replicated upon applying the initial snapshot or when reinitialization occurs. Also, if the function is used as a computed column, you can not make any changes to the function unless you drop the table or the computed column that uses the function. Therefore, if a function is used as a computed column and the table that contains the computed column is used as an article, to make changes to the function definition you need to perform these steps:

Unsubscribe to the publication.
Remove the article that uses the computed column from the publication.
Drop the table or remove the computed column from the table.
Modify the function definition, and then script the function definition again.
If the table was dropped, re-create the table. If the computed column was dropped, add it back to the table.
Add the table as an article to the publication.
Subscribe to the publication again.