Transactional Replication - Gives 'Syntax error or access violation'

[JohnBates]

hi experts,

I have set up transactional (push) replication for 1 table.

Oddly, it replicates UPDATES/CHANGES to the subscriber database just fine. But any INSERTS cause the Distribution Agent to fail with this vague error:
"Syntax error or access violation. The step failed."

I think I can rule out access violation since the Inserts replicate ok.

Any idea why inserts will not replicate, but updates will?
SQL Server 2000

Thanks ! John

 

[JohnBates]

.... Maybe this will help someone else.

We have (unfortunately) several tables whose name is not contiguous. ex. Product Codes

oops... When I added Product Codes as an article in the Transaction Replication, it creates 3 system stored procedures - MSupd_Product Codes, MSdel_Product Codes and
MSinsert_Product Codes or some such sp names. The provlem is those spaces in the stored proc names - so the Replication Agent fails with a 'syntax error' when you change/delete/insert a row at the publicher.

I think you can assign a diff sp name when you add an article - I'll try MSinsert_ProductCodes and see if that works.

I knew there was a million reasons to never have spaces in a column/table name - but now I've discovered yet another one !


John