Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber
-
Wednesday, November 14, 2012 12:50 PM
Hi,
We are using merge pull replication with SQL Server 2008 r2.
We added some views long time after the publication was created. (Not nested views, only table dependencies)
ex:
create view VW_Empleados as select Empleado.ID_Cliente,Numero_Empleado,cliente.Nombre,Cliente_Retail.Apellido,Cliente.Email, Cliente.Notas,Telefono1,Telefono2,Cliente_Retail.Celular,Fax,ID_Empresa,Categoria_Cliente_Retail.Nombre Categoria_Cliente, Cliente_Retail.Documento_Identidad,Estado_Civil,Fecha_Nacimiento,Sexo, Empleado_Habilitado,Tipo_Empleado.Nombre TipoEmpleado from Empleado inner join Cliente on Cliente.ID_Cliente=Empleado.ID_Cliente inner join Cliente_Retail on Cliente_Retail.ID_Cliente=Empleado.ID_Cliente left outer join Tipo_Empleado on Tipo_Empleado.ID_Tipo_Empleado= Empleado.ID_Tipo_Empleado left outer join Categoria_Cliente_Retail on Categoria_Cliente_Retail.ID_Categoria_Cliente_Retail= Cliente.ID_Categoria_Cliente GO
exec sp_addmergearticle @publication = 'IPoint_Pub', @article = N'VW_Empleados', @source_owner = N'dbo', @source_object = N'VW_Empleados', @type = N'view schema only', @description = null, @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_owner = N'dbo', @destination_object = N'VW_Empleados', @force_invalidate_snapshot=1
Now, when initializing new subscribers we get:
"Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber"
The error mentions the missing table "Cliente".
Manually creating the missing tables solves the issue, but this is not an option for us because of the number of subsribers.
This is only happening with new views we added.
We have run exec sp_refreshview VW_Empleados
and then regenerated the snapshot but we get the same error.
I am almost sure the the views were not created using dynamic SQL. exec (' ALTER View ...');
Any help will be really appreciated.
Best Regards, Daniel.
All Replies
-
Wednesday, November 14, 2012 1:20 PMModerator The best way to do this is to create the view using a post snapshot script. What is happening is that the view is being created before the base tables are.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
- Marked As Answer by dKaco Wednesday, November 14, 2012 2:28 PM
-
Wednesday, November 14, 2012 1:32 PM
Hilary, thanks a lot for your quick answer.
If we create the views in the post snapshot script I assume that any alter view will not be replicated so will be necessary to add_script_exec in that publication.
Is there a way to recreate dependencies and doing so, also recreate the snapshot schema order?
Thanks,
Daniel.
Best Regards, Daniel.
-
Wednesday, November 14, 2012 1:55 PMModerator
Yes, that is correct alters will not travel. But you could modify the view using sp_addscriptexec
http://msdn.microsoft.com/en-us/library/ms174360.aspx
Which will execute a script on all subscribers.
In SQL 2000 dependencies were a problem. This was solved in SQL 2005 and above - however you are still bumping into dependency issues - I am not sure why. Refreshing the view is the best way to correct dependencies.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
.png)
