May 25, 2006 at 8:03 am
Hello everyone. I hope some of you Replication Gurus can help me. I have about 25 tables setup for Transactional Replication. Well, I went to add another yesterday into production and I successfully added it. Unfortunately, about 20 minutes later replication failed...the Distribution Agent to be more exact. The error and Last Action error reads "Syntax error or access violation". So, I removed the last Pull Subscription that I set up and attempted to restart the Distribution Agent with no luck. The only way I was able to get the Distribution Agent going again was to start deleting subscriptions. I deleted about 6 and attempted to restart the Distrib Agent again successfully.
Next I coped this same table to a test environment and setup replication again to get the same results. I appreciate any thoughts.
May 26, 2006 at 7:21 am
Hello everyone. I was able to figure out the problem, but since I was not really able to find any good information about it online I will post what I found. The reason that replication was bombing with this particular error message was because the table that I was attempting to replicate had a space in it. SQL Server Replication does not like tables with spaces in the name. So, I just replaced Transactional Replication with Snapshot Replication. Crisis averted.
May 29, 2006 at 8:18 pm
Thats a might ig step going from Transactionl replication to Snapshot replication.
I take it your subscribers don't send data back to the publisher.
From a different perspective, table names with spaces is not good design practice. Unfortunately you leave yourself open to bumping into issues that you would never get if you took a more cautious approach to naming conventions.
May 30, 2006 at 6:44 am
I agree it's a big step, but the table is not very large. I also agree that its not good design practice to create a table with a space in the name. Unfortunately, I am inheriting this system from years of bad decisions. Additionally, this table name is embedded into much code and if I decided to change the table name everything would come crumbling down. Literally!!
This is just a reporting server that we have the suscriber does not send any data back to the publisher.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply