0

Ive been having great trouble setting up replication (web sync via HTTPS).

If I only publish the tables, they replicate fine with the data.

If I add in views & sprocs then it failes with the message:

ComErrorException (0x80004005): The schema script 'xxx' could not be propagated to the subscriber

The publication is SQL 2008 and the subscriber is SQL Express 2005.

The views are all relativly simple, such as: (SQL added the DROP at the top).

drop View [dbo].[vAllUsers] go

SET QUOTED_IDENTIFIER ON go SET ANSI_NULLS ON go

CREATE VIEW [dbo].[vAllUsers] AS SELECT userid FROM staff WHERE userid IS NOT NULL UNION all SELECT userid FROM vesselstaff WHERE userid IS NOT NULL UNION all SELECT userid FROM fleetmanagers WHERE userid IS NOT NULL go

Im confused and basically dont know what Im doing. Does anyone have any ideas????

Update 3rd Dec 10

Ok, I've been fiddling with this for a bit longer. It does replicate the tables, data and user defined functions but no stored procedures or views will go. What does this indicate?

DomBat
  • 1,981
  • 5
  • 27
  • 42

1 Answers1

0

OK I hope this helps someone else as I found out what the problem is.

Basically, sprocs and views will not replicate without a table (there was an MSDN blog I found about this here link text). So in my example, I already had all the tables and data, so when I started adding views they were the only things coming across and it doesn't work.

I also had a function that was referencing a column that had been deleted (so when it was CREATED at the subscriber it failed).

We also had a lot of data to replicate (we were doing HTTP web sync replication) so had to adjust the settings in the agent on the server so it sent less data per hit. See this article for info link text

DomBat
  • 1,981
  • 5
  • 27
  • 42