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
