Sql-server – Mysterious merge replication problems creating a subscription

merge-replicationreplicationsql-server-2005

We have a merge publication which had been mostly working OK for a long time (years). Recently an article was added, we were informed the snapshot was out of date, so we regenerated it. Then we got error messages during initialization and found in the verbose error logs this problem:

Msg 1934, Level 16, State 1, Procedure DDLTrigger_Index, Line 19
INSERT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

…fast forward to now. In experiments we found that yes it is possible to create a merge publication on the publisher and subscribe on our subscriber server, but only if using a different database. Went through this just to rule out permissions/etc and to determine it was truly the subscriber db that was the problem.

So, it seemed logical to remove replication from afflicted DB in hopes of cleaning out any buried metadata mess:

sp_removedbreplication @dbname = 'sickDB' @type = 'all'

So far so good. However now, I get the exact same error, but much earlier in the process. Specifically, when executing:

/****** Begin: Script to be run at Subscriber ******/
use [sickDB]
exec sp_addmergepullsubscription 
@publisher = N'publisher',
 @publication = N'publication', 
 @publisher_db = N'goodDB',
  @subscriber_type = N'Local',
   @subscription_priority = 0,
    @description = N'hope this works', @sync_type = N'Automatic'

(above edited slightly).

Is there some remaining prob lurking in the metadata? The original theory on the ANSI_PADDING stuff was there were in fact some tables in the subscriber DB w/ computed columns thus that seemed related. However some flipping of ANSI_PADDING settings (and Googling) didn't really lead to a conclusive answer.

Thanks so much for reading and let me know of any other info I can provide that may help.

Oh yeah, publisher and subscriber are both SQL Server 2005.

Best Answer

I think you should check that in your database you have some ddl trigger which can switch off 'ANSI_PADDING'. If in the trigger body you will find something like i.e. SETANSI_NULLS OFF it could be your error.

If it is i.e. trigger which is monitoring that users are removing or adding i.e. indices. That will be possible it is acting when you creating your pull subscription and replication internals are trying to add replication triggers on your subscriber database.

In this case you need to disable trigger, create pull subscription and enable trigger.

More you can find on this link. It is a bug in ms sql server 2005

http://connect.microsoft.com/SQLServer/feedback/details/334194/ansi-padding-options-in-replication-snapshot

J

Related Question