Invalid SQL when @centralized_conflicts='false'

The further I delive into SQL Server replication, the more apparent it
becomes what a complete and utter lash-up the whole "technology" is.
Has anyone ever encountered this particular problem? I have transactional
push replication in use, with a publication with
@.centralized_conflicts='false' (i.e. requiring conflicts to be logged at
both publisher and subscriber).
However, when a conflict occurs, SQL Agent falls over with an error
"Incorrect syntax near the keyword 'null'". On further investigation, the
SQL generated by Agent for inserting the conflict into the subscriber's
conflict table is missing a comma in the VALUES list!!
It seems incredible to me how something as important as data replication can
have been implemented using nothing more than a whole load of (pretty poorly
written) triggers and stored procedures that may or may not be restored when
you backup and restore a database, that cause replication to fail when you
restore a published database on the subscriber unless you first run the
pathetic sp_scriptpublicationcustomprocs to generate the world's longest
nvarchars containing dynamically generated SQL, and which cause utterly
meaningless errors about invalid SQL to be logged to the event log.
The rant is over now, but the serious question remains - is anyone out there
actually using SQL Server Replication within a serious Enterprise class
application or should I just accept that it really is the half-hearted,
conceived-at-the-last-minute, hack that it appears to be?It works well for my clients. I have worked on replication projects with
several hundred subscribers.On one implementation we had a publisher
replicating 2500 transactions per sec! Barnes and Noble runs an
implementation with over 600 subscribers. Ticket Master uses transactional
replication and is the largest implementation of transactional replication
or was last time I spoke with some of the MS folks. In one of the channel 9
videos Bren Newman talks about repro'ing a topology with over 200. Its done,
its stable, it offers high performance.
I am curious though as to what method of transactional replication you are
using? There is a problem with the GUI in transactional replication methods.
IIRC it seems to indicate that you can log conflicts centrally or locally;
whereas you can only log them centrally. Perhaps this is what you are
encountering. What sp and version of SQL (7 or 2000) are you running?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
news:%23f7FFZ%23zFHA.904@.tk2msftngp13.phx.gbl...
> The further I delive into SQL Server replication, the more apparent it
> becomes what a complete and utter lash-up the whole "technology" is.
> Has anyone ever encountered this particular problem? I have transactional
> push replication in use, with a publication with
> @.centralized_conflicts='false' (i.e. requiring conflicts to be logged at
> both publisher and subscriber).
> However, when a conflict occurs, SQL Agent falls over with an error
> "Incorrect syntax near the keyword 'null'". On further investigation, the
> SQL generated by Agent for inserting the conflict into the subscriber's
> conflict table is missing a comma in the VALUES list!!
> It seems incredible to me how something as important as data replication
> can have been implemented using nothing more than a whole load of (pretty
> poorly written) triggers and stored procedures that may or may not be
> restored when you backup and restore a database, that cause replication to
> fail when you restore a published database on the subscriber unless you
> first run the pathetic sp_scriptpublicationcustomprocs to generate the
> world's longest nvarchars containing dynamically generated SQL, and which
> cause utterly meaningless errors about invalid SQL to be logged to the
> event log.
> The rant is over now, but the serious question remains - is anyone out
> there actually using SQL Server Replication within a serious Enterprise
> class application or should I just accept that it really is the
> half-hearted, conceived-at-the-last-minute, hack that it appears to be?
>|||Thanks for the info, Hilary. It's interesting to see how and where it is
being used for real.
The number of transactions and subscribers doesn't surprise me - I've
thrashed it myself with good throughput results. Neither am I surprised, I
guess, that some companies have achieved results with it... but it doesn't
change my opinion that it's quite clearly an afterthought that is
implemented by clear "hacks", and which make it very messy to deal with. I
guess that with enough time, patience and frustration, you could get
TicketMaster running on a million networked Commodore 64s with BASIC as the
programming language. But that wouldn't make it an elegant, sensible or
extensible solution.
I'm using transactional replication with a single queued updating
subscriber. sp_addpublication allows you to specify @.centralized_conflicts
= false, which is supposed to ensure conflicts are logged at both publisher
and subscriber (but which causes the error):
http://msdn.microsoft.com/library/d... />
l_4s32.asp .
I'm using SQL Server 2000 SP4 (Microsoft SQL Server 2000 - 8.00.2039 (Intel
X86)).
Cheers,
Chris.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OOT0%232%23zFHA.3124@.TK2MSFTNGP12.phx.gbl...
> It works well for my clients. I have worked on replication projects with
> several hundred subscribers.On one implementation we had a publisher
> replicating 2500 transactions per sec! Barnes and Noble runs an
> implementation with over 600 subscribers. Ticket Master uses transactional
> replication and is the largest implementation of transactional replication
> or was last time I spoke with some of the MS folks. In one of the channel
> 9 videos Bren Newman talks about repro'ing a topology with over 200. Its
> done, its stable, it offers high performance.
> I am curious though as to what method of transactional replication you are
> using? There is a problem with the GUI in transactional replication
> methods. IIRC it seems to indicate that you can log conflicts centrally or
> locally; whereas you can only log them centrally. Perhaps this is what you
> are encountering. What sp and version of SQL (7 or 2000) are you running?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
> news:%23f7FFZ%23zFHA.904@.tk2msftngp13.phx.gbl...
>