can't modify stored proc which is in a publication

Hi

SQL 2005 sp1 - merge replication - HTTPS.

We have 2 publications for the database - one which has subscription.SyncType = SubscriptionSyncType.Automatic
and another with SyncType = SubscriptionSyncType.None.

The first publication is there so we can add new stored procs etc, the second contains the initial schema and the data.

When we try to modify a proc which is in the publication with SyncType = Automatic, the query never returns.

This is most urgent - thanks for your help.

Bruce

Do we have one or two subscriptions to publication one that contains the stored proc as a merge article in the publication?

|||

There are 2 subscriptions - one for each publication - but the proc is only in one publication

regards
Bruce

|||

1. Back to your original question - could the query (modifying the SP) be locked by another process so that it won't return?

2. Let us try to isolate this problem. If you can - colon the publication database, this publication (no second one), and subscribing database; does the same problem still occur?

Thanks.

|||

1. I doubt it..

2. 'colon the publication database' - what do you mean ?

thanks

|||

Create another publication database and create the same set of user tables/views/SPs. Just create one publication which includes the SP and other merge articles. See if you still can repro this issue with a single publication.

Thanks.

|||

We will try this tomorrow - but it's worth pointing out the following

a) the publication with SyncType = none -- has a few hundred stored procs - I can change any of these no worries

b) the publication with SyncType = automatic - initially just has 1 stored proc - so we can create the publication! - it's the procs we've added to this which we can't subsequently change.

Regards
Bruce

|||

Ok

I stripped it right back.

a. created a new database

b. added one proc

c. created the merge publication , snapshot etc

d. initalized ok

e. The query never returns....

Thanks
Bruce

|||

Can you share the proc prototype/definition (also I assume you use "ALTER PROCEDURE" to modify it)? I want to repro this case in house.

Thanks.

|||

Absolutely - it's a mindnumbing place-holder - it's just there so we can create the publication..

Don't laugh.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

--

-- This is the proc for the 'extras' subscription

--

--

ALTER PROCEDURE [dbo].[aaaaPlaceHolder]

AS

BEGIN

SET NOCOUNT ON;

SELECT 'fish', 'cow', 'dog', 'elephant', 'pig', 'moo', 's', 't', 'dog', 'sheep'

END

|||

Bruce,

Thanks for your help. I have repro-ed this scenario on my machine. I need to work with my peers to diagnoise the real cause of it. Should get back to you once I have the answer.

Regards,

Leo

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

As a temp solution - a dummy table can be created to join the SP in the same publication so that SP can be modified and replicated to the subscriber.

Leo

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi

I'll try it - has this been logged as a bug ? If so, how do I keep track of it ?

Thanks

|||

The bug was definitely filed and once I heard the decision/status I post it immediately.

Thanks

|||

This issue/bug should be addressed in Yukon Service Pack 2.

Leo

This posting is provided AS IS with no warranties, and confers no rights