March 30, 2009 at 5:22 pm
We have transactional replication setup on SQL Server 2005 (9.0.3077 on publisher, distributor and subscriber) and have recently noticed that some updates on NTEXT fields are being sent over as "corrupted" text. Although the fields are NTEXT, we're not using non-ascii characters in the fields (application is designed to support unicode) The ASCII value of first byte shipped is lower than 10 and the text is full of invalid characters. When I trace, I see that the garbage characters are being sent over to the subscriber by the distributor. Here's one such command:
exec [sp_MSupd_dboSampleTable] default,default,default,default,default,default,default,default,default,default,default,default,default,default,default,default,default,default,default,N' > 鋉&ᎀ枹H ᎀ枹 㒾 枸黟ɦ簩튕NJ','N',default,default,default,default,default,default,default,default,'Mar 30 2009 3:25:40:407PM',0x000000009BE2E685,default,default,default,NULL,default,default,default,default,default,default,default,default,default,default,default,default,default,293295,0x000018600400
We have seen this on multiple tables, but only SOME of the update statements. Both DBs are running with the same collation if that matters.
March 31, 2009 at 1:41 pm
This is a shot in the dark: Do you have "max text repl size (B)" setup to 2GB?
* Noel
March 31, 2009 at 1:43 pm
It's set to 65536. The string in question is <32 bytes long.
I have also confirmed that the commands in the distribution database have the "corrupted" values by using sp_browsereplcmds
March 31, 2009 at 1:51 pm
Maybe you have a "corrupted" Source too then.
You should check the source table and see if there is any Error regarding the LogReader.
* Noel
March 31, 2009 at 1:56 pm
The problem seems to happen randomly on multiple tables, only on some updates where the NTEXT field is updated (some updates come across ok). No errors are logged by the log reader
March 31, 2009 at 2:01 pm
The updates are being done with standard "UPDATE" commands, right? not UPDATETEXT, correct ?
* Noel
March 31, 2009 at 2:03 pm
Correct,
In all the cases I've seen, they're SP calls that simply use the UPDATE statement to update the NTEXT values from the value passed in the parameter. I see consequent updates, to the same table, using the same sp, setting the same column to the same value. Some come across to the distributor ok, some come corrupted.
March 31, 2009 at 2:16 pm
Ali (3/31/2009)
Correct,In all the cases I've seen, they're SP calls that simply use the UPDATE statement to update the NTEXT values from the value passed in the parameter. I see consequent updates, to the same table, using the same sp, setting the same column to the same value. Some come across to the distributor ok, some come corrupted.
Have you run dbcc checktable on the Source ?
* Noel
March 31, 2009 at 2:18 pm
I just did, and no problems were found
March 31, 2009 at 2:56 pm
Because some seem "bad" and some others don't I would then check the clients maybe that garbage is actually being sent into the db for a mis-behaving client!
* Noel
March 31, 2009 at 2:58 pm
In this case, they are all coming through the same client which is the application web server. And the data is just fine in our production server - it just gets corrupted when it gets shipped out to the distributor.
March 31, 2009 at 4:01 pm
I would think the last bullet would be to run a full dbcc checkdb on it but it does not seems logical ...
* Noel
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply