Escaping [characters with text lengths over 4000 characters

In a previous post I mentioned you will get into problems when trying to
update DataSets containing rows with [ characters in ntext or text field
columns. Andrew Conrad (thanks) mentioned that you should escape these, like
so [[]. This will help you, except when the length of the value is over 4000
characters (for ntext) in length. After that the problem of zero affected
rows arises again, despite escaping. How come? How to solve?
Thanks.
AlexThe REPLACE fuction (and other SQL Server string functions) will not operate
on data larger than 8000 bytes -- for an NVARCHAR datatype, that means 4000
characters (2 bytes per character). Can you perform the replace on the
client, before passing the data to SQL Server?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Alex Thissen" <athissen A T killer-apps.nl> wrote in message
news:uNIrcZPFFHA.3284@.TK2MSFTNGP09.phx.gbl...
> In a previous post I mentioned you will get into problems when trying to
> update DataSets containing rows with [ characters in ntext or text field
> columns. Andrew Conrad (thanks) mentioned that you should escape these,
like
> so [[]. This will help you, except when the length of the value is over
4000
> characters (for ntext) in length. After that the problem of zero affected
> rows arises again, despite escaping. How come? How to solve?
> Thanks.
> Alex
>|||Hi Adam,
Thanks for thinking with me on this one. I don't use the REPLACE functions.
Instead, I make sure that the OriginalVersion of my DataRow in the DataSet
has the replaced value. Then the SqlXmlAdapter builds the UPDATE statement
for me, but it uses an optimistic locking scheme by comparing all columns
with the original values.For the (n)text fields the LIKE operator is used,
but as I mentioned this one breaks with values over 8000 bytes and [ chars
in it. I also wrote a (teasing) weblog entry on it, that you can find here:
http://www.alexthissen.nl/weblog/Pe...br />
8c42070.
You might want to read up on it, since this problem is hardly related to
SQLXML. It could be circumvented if there was a possibility to influence the
SQL that is generated. An option to exclude fields from the optimistic
locking would solve it directly.
So, my question remains, given that the LIKE operator breaks for lengths
over 8000 bytes WITH escaped [ characters in it ( as [[] ) in it, how do I
get my DataSets that contain such values to get updated through SQLXML?
Thanks, Alex
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uvUE$$VFFHA.1348@.TK2MSFTNGP14.phx.gbl...
> The REPLACE fuction (and other SQL Server string functions) will not
> operate
> on data larger than 8000 bytes -- for an NVARCHAR datatype, that means
> 4000
> characters (2 bytes per character). Can you perform the replace on the
> client, before passing the data to SQL Server?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Alex Thissen" <athissen A T killer-apps.nl> wrote in message
> news:uNIrcZPFFHA.3284@.TK2MSFTNGP09.phx.gbl...
> like
> 4000
>sql