May 25, 2006 at 2:56 am
We've run into a strange problem. Setting up a SQL 2000 (SP3) server for replication, we had publications setup etc, but doing some trial and error work.
Having worked out the 'best' approach, we removed the publication & subscriptions etc, removed replication and went to start from scratch.
Unfortunately, we now get this when we run the wizard:
SQL Server Enterprise Manager could not configure 'server' as the
distributor for 'server'.
Error 8152: String or Binary data would be truncated.
We've restarted services and the server, checked everything we can think of, but just can't get replication configured again. Has anyone encountered this? Any suggestions how to fix it?
May 25, 2006 at 3:31 am
I'm not saying this will necessarily fix your problem, but if you're using replication then I recommend upgrading to SP4.
As for the problem itself, this is the error that you get when you try to insert data into a column that isn't wide enough to take it (for example an 11-character string into a varchar(10) column). I would suggest using Profiler to find out exactly what SQL Server is trying to do when it throws this error. If you post the result of that then I'm sure someone will be able to help.
John
May 25, 2006 at 4:38 pm
Unfortunately, we're stuck with SP3 for the moment due to other requirements.
In isolation, we know what the 8152 means - we just have no idea why it has happened when we try to re-enable replication. It looks like the removal of replication hasn't "cleaned house" properly, and left some sort of wreckage behind.
Thanks for your suggestion - I'll pass it on to the DBA on the task. As this is a newly built server, they are now considering it may be quicker to rebuild than to try to fix the cause.
May 26, 2006 at 7:20 am
When disabling replication it unfortunately does leave some "crud" behind in the system tables.
The info below is from an article I read some time ago and have copied to my notes. Hope it helps.
(Can't remember who wrote the article or where so like to point out this is not my writing but words written by someone else).
"Other common items that are left behind are database options, replication objects, sysobject settings, rowguid column, conflict tables.
To remove the database options, you simply execute sp_dboption for published, merge publish, or subscribed, and set it to false. Removing the sysobject settings is more involved in that you have to turn on allow updates, update the replinfo column and set it to 0 where it is 128, and then turn off allow updates. At this point, you should have a fully functional database that allows you to alter/drop objects and drop databases. You could stop here without having any side effects. But, we might as well take everything out. You get rid of the rowguid column, if it exists, by altering the table and dropping the column. Conflict tables are simply user tables and can be removed with a simple drop command. The same goes for any triggers, views, or procedures that replication created.
Strongly recommend stopping there and not touching any of the replication system tables or system stored procedures that were created when you setup replication. They do not have any effect on your environment, but you never know if something got left behind in the bowels of the SQL Server code that will cause something to blow up if you remove these. Leave the system objects intact on the machines and just ignore them."
I did however find out myself that:
§ Distributor_admin login remains on distributor when use drop script
§ Remote Publisher and subscribers as remote servers remain also.
Paul R Williams.
June 29, 2006 at 8:30 pm
Just thought I'd post an update on this in case someone else runs into this.
Our problem turned out to be related to server hardening (for security). One of the server guys had applied hardening to the server, but hadn't mentioned it to us. It was only after several days of frustration and rebuilds that it came to light.
It appears that some part of the process they used affected server permissions, group policies etc. With the hardening backed off, the problem disappeared.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply