Alfred,
Thank you for coming back here to let us know how you got on and the
resolution. The answer you got from Microsoft is very interesting and it
is certainly worth remembering the workaround.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
Mickautsch, Alfred wrote:
Hallo,
I got a solution for this problem so I thought you might be interested to hear
it.
At Martin's advice I called Microsoft and Hans Lindgren from Microsoft Product
Support confirmed the bug and found a workaround. I got the the OK from Hans
Lindgren to post the summary of the case to the list, so here it is:
---
Action:
INSERTing strings containing '\<LF>' or '\<CR><LF>' into the database
Result:
Query succeeds but the Backslash and the following <LF> or <CR><LF> are removed
from the string
Cause:
The T-SQL string parser removes these characters due to a parser artefact.
Resolution:
Parameters passed using RPC (remote procedure calls) are not parsed in the same way as strings used in T-SQL. Altering the INSERTion behaviour to use RPC avoids this issue (in this case the work around is to create a SP that handles the insert and to call it using RPC).
---
Besides the Microsoft case I did my own debugging and found out, that the bug
does only occur when the data is sent to SQL Server with SQL_DATA_AT_EXEC and
SQLParamData/SQLPutData. But this was not confirmed by Microsoft so I cannot
guarantee that this works so in all cases. Nevertheless it works for me.
Servus -- Alfred
--
Alfred Mickautsch
Schuler Business Solutions AG
Karl-Berner-Str. 4
D-72285 Pfalzgrafenweiler
tel: +49 (0)74 45 830-184
fax: +49 (0)74 45 830-349
e-mail: [EMAIL PROTECTED]
-----Ursprüngliche Nachricht-----
Von: Martin Evans [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 30. Januar 2007 15:54
An: [email protected]
Betreff: Re: CLOB Problem with DBD::ODBC/DBD::ADO for SQL Server
Mickautsch, Alfred wrote:
-----Ursprüngliche Nachricht-----
Von: Martin Evans [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 30. Januar 2007 12:40
An: [email protected]
Betreff: Re: AW: AW: Re: AW: CLOB Problem with
DBD::ODBC/DBD::ADO for
SQL Server
[...]
I insert 24 chars (16 \ and 8 line feeds) and I get back 24 chrs.
I am unsure why the code does not insert CR\LF as I am
100% sure the
file itself contains CR\LF.
I have tried with ntext, text and char fields - no difference.
I have run it through a tds spy and can see the line feeds
going through
fine in both directions.
[...]
Yes, it is a weird problem. There seems to be a byte count
limit under which this effect does not occur. It happens with
my text example of 102858 bytes. With a text of 569 bytes it
does not seem to happen. It is very confusing.
Thank you for your efforts.
Servus -- Alfred
As you indicate, the length has something to do with it. At
24000 chrs
it works and at 48000 chrs you lose each \ followed by a
<linefeed>. It
is of no consolation to you but I have duplicated it with the MS SQL
Server driver and also demonstrated it works fine with our sql server
driver. There is a difference. The MS SQL Server driver execs
sp_execute
to do the insert with the literal text whilst our driver calls
sp_execute with a parameter.
I had thought turning off translation might help but it doesn't.
I am of the opinion this may be a ms sql server odbc driver
bug in which
your options are limited unless you have a support contract
or can come
up with a workaround.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
SCHULER Business Solutions AG
Aktiengesellschaft mit Sitz in D-72285 Pfalzgrafenweiler, Karl-Berner-Straße 4
Registergericht Stuttgart HRB 430947
Vorstand: Uwe Jonas, Harald Sieber
Vorsitzender des Aufsichtsrates: Gerhard Schuler
