Incorrect processing order for views

I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2

Essentially the problem is as follows:

1) Initial state is that merge replication of table and views is working fine

2) I then alter one view which references a new view in the same publication

3) Synchronization processes the view scripts in the wrong order regardless of the processing order

4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted

The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order

Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs

I have tried to recreate this problem on a small database with a minimum of articles, but attempts at repro have failed to date with a simple configuration - ie the processing order applied is correct

Is there an known problem in this area?

Any suggestions would be much appreciated

aero1

Note

1) Dependency information is up to date and accurate on the publisher

2) Creating new view manually on the subscriber allows sync to complete successfully - but this isn't an option for ongoing system updates with large numbers of subscribers

3) I am not clear whether the SQL for creating the new view is ever reaching the subscriber. I will test for this with profiler

|||

While this is not a fix for your problem it might help you out for a while.

You can schedule a script to run before the snapshot is extracted onto the subscriber. In the script you could create your troublesome view. That way it will automatically create the view everytime someone is added. Not the solution but maybe a workaround.

Martin

|||

Hi Martin

Thanks for your suggestion. Similarly to what you suggest - I am looking at ways of introducing a generic mechanism in our system to provide a means of running scripts that replication should have run. As some subscribers are SQL Express this can't be an agent job

I did look at using the pre-snapshot script for this purpose - but this script is only run when initializing

The case I have cited is just one instance of the type of ordering problems that may be encountered - so I can't just code for this specific case

Note that I created the new view manually on a test system and profiled the subsequent merge (which was successful). As anticipated all the 'alters' were run first and then the 'creates' came along afterwards. The processing order applied was correct within the 'alters' and correct within the 'creates'. However, the processing order needs to be applied across the whole set of 'alters' and 'creates' together

aero1

|||I don't konw that merge replication has great dependency checks like tran replication does, but for this scenario, it's not uncommon to separate out the user procs/views/functions/etc into a separate snapshot or tran publication and refresh it periodically (or on a daily schedule) when you know you've made changes.|||

Hi Greg

I have tested as per your suggestion - i.e. I created a separate merge publication for all the non schema articles (stored procs, view and functions) - and recreated the other publications without those articles.

All appeared to be going well until I attempted to alter a replicated stored proc (on the publisher). I attempted altering a number of stored procs, but each time the alter hung - and I could see that the alter SPID had clocked up up to 20 minutes of CPU each time. I checked this behaviour on my original configuration and the alters took about 4 seconds.

I like the simplicity of a separate publication, but need to get over this issue to make it workable.

I don't know if it is relevant, but the database had 3 merge publications previously and now has four with the new publication for the non schema articles.

The 3 publications were setup for the following reasons

- To get over the 256 article limit problem

- To use separate publications for subscribers needing filtered and unfiltered data

Any thoughts would be much appreciated

Thanks

aero1

|||

I have found that the problem altering procs is a known issue covered in this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417921&SiteID=1

I added a dummy table to the publication and am now able to alter published procs successfully

aero1