[dev] OTRS and MS SQL 2000 again

Martin Edenhofer <EMAIL-PROTECTED>
Sat, 12 Jul 2003 12:11:20 +0200

Hi Holger,

On Thu, Jul 10, 2003 at 12:28:43PM +0200, EMAIL-PROTECTED wrote:
> It looks like, as if otrs runs now with the SQL server. 
> A few data types and a table name in the database had to be however adapted.
> the table name system_user cannot be used, in MS SQL this name is reserved
> of the system.
> Therefore I renamed the table in sys_user.
> The data base schema I use is attached on this mail.
> A (hopefully) last problem exists however still: 
> the field a_body in the table article is in the origin of the data type
> text.
> The data type text can only with IS ZERO or LIKE be compared or sorted
> within MS SQL, not with GROUP BY.
> ( ntext and image too) 
> Therefore I use varchar with a length of 8000 for as data type for a_body.
> But I think 8000 indications could be too few, for a very big incoming
> email?

That's right. Data type text would be better.

> The error message in the logfiles, if the data type stands on text: 
> This error messages will appear in the logfiles if I want to Zoom a ticket
> (a_body data type = text)
> otrs log:
> [Thu Jul 10 11:48:32 2003][Error][Kernel::Modules::AgentZoom::Run][164]
> [Microsoft][ODBC SQL Server Driver][SQL Server]Die Datentypen text, ntext
> und image können nur mithilfe des Operators IS NULL oder LIKE verglichen
> oder sortiert werden. (SQL-42000)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Anweisung(en) konnte(n) nicht
> vorbereitet werden. (SQL-42000)(DBD: st_execute/SQLExecute err=-1), SQL:
> 'SELECT sa.id, st.tn, sa.a_from, sa.a_to, sa.a_cc, sa.a_subject, sa.a_body,
> [...]
> any idea somebody?

Yes! :)

In the current CVS version we rewrote the AgentZoom module and moved the 
SQL stuff to the backend. 

 -=> So this old 'bin' SQL statements exists not anymore. :)

If you are not familiar with CVS try the CVS snapshot: 


> Holger


Martin Edenhofer - <martin at edenhofer.de> - http://martin.edenhofer.de/
"The number of Unix installations has grown to 10, with more expected."
                      The Unix Programmer's Manual, 2nd Edition, June 1972