RE: [dev] OTRS with MS SQL Server

-----Original Message----- From: Martin Edenhofer [mailto:martin@edenhofer.de] Sent: Wednesday, July 09, 2003 11:12 AM To: dev@otrs.org Subject: Re: [dev] OTRS with MS SQL Server
Hi Martin,
yes, normally '\'
That means you don't need to quote \?
ups, sorry, i meant you have to quote \
I improved Kernel/System/DB.pm with mssql specs. Use the 1.29 version from cvs (http://cvs.otrs.org/cvsweb.cgi/otrs/Kernel/System/DB.pm).
-=> With ODBC is no auto database type detection possible, so you need to set the database type in your Kernel/Config.pm
$Self->{'Database::Type'} = 'mssql';
And? :)
maybe i should describe, what i have done at first to connect to the SQL Server. my Config.pm looked like this: $Self->{Server} = 'SQL Server'; # DatabaseDSN $Self->{DatabaseDSN} ="DBI:ODBC:driver=$Self->{Server};Server=$Self->{DatabaseHost};database=$Sel f->{Database};uid=$Self->{DatabaseUser};pwd=$Self->{DatabasePw};"; with this i can connect to the SQL Server, and most things are working as far as i can see. if i try this: # DatabaseDSN $Self->{DatabaseDSN} = "DBI:ODBC:driver=$Self->{Database}"; $Self->{'Database::Type'} = 'mssql'; i get this error: (sorry, only german error messages ;-) Fehler: [Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben. (SQL-IM002)(DBD: db_login/SQLConnect err=-1) so next i tried was this: $Self->{Server} = 'SQL Server'; # DatabaseDSN $Self->{DatabaseDSN} ="DBI:ODBC:driver=$Self->{Server};Server=$Self->{DatabaseHost};database=$Sel f->{Database};uid=$Self->{DatabaseUser};pwd=$Self->{DatabasePw};"; $Self->{'Database::Type'} = 'mssql'; this worked for me, i can connect now. an now my last error is gone :-) but now i have a new one :-/ in the queue view no more tickets can be viewed. this error message appears in the log: Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Zeile 1: Falsche Syntax in der Nähe von 'LIMIT'. (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 st.id, st.queue_id FROM ticket st, queue sq WHERE sq.id = st.queue_id AND st.ticket_state_id in ( 1, 4, 6, 7, 8 ) AND st.ticket_lock_id in ( 3, 1 ) AND st.queue_id in ( 5 ) AND sq.group_id IN ( 1, 2, 3, 24, 4, 10, 5, 11, 6, 12, 20, 7, 13, 21, 8, 14, 22, 23, 15, 9, 16, 17, 18, 19 ) ORDER BY st.ticket_priority_id DESC, st.create_time_unix ASC LIMIT 16' Traceback (2012): Module: Kernel::Modules::AgentQueueView::Run (v1.36) Line: 173 Module: Apache::ROOT::otrs::index_2epl::handler (v1.58) Line: 608 Module: (eval) (v2.01) Line: 149 Module: Apache::Registry::handler (v2.01) Line: 149 maybe i schould switch back to mysql ;-) Holger

Hi Holger, On Wed, Jul 09, 2003 at 12:13:58PM +0200, Holger.Struck@sqs.de wrote:
That means you don't need to quote \?
ups, sorry, i meant you have to quote \
Opps. Sorry. :) So a \ should be quoted with '\ or with '\' ?
I improved Kernel/System/DB.pm with mssql specs. Use the 1.29 version from cvs (http://cvs.otrs.org/cvsweb.cgi/otrs/Kernel/System/DB.pm).
-=> With ODBC is no auto database type detection possible, so you need to set the database type in your Kernel/Config.pm
$Self->{'Database::Type'} = 'mssql';
And? :) [...] so next i tried was this:
$Self->{Server} = 'SQL Server'; # DatabaseDSN $Self->{DatabaseDSN} ="DBI:ODBC:driver=$Self->{Server};Server=$Self->{DatabaseHost};database=$Sel f->{Database};uid=$Self->{DatabaseUser};pwd=$Self->{DatabasePw};"; $Self->{'Database::Type'} = 'mssql';
this worked for me, i can connect now.
That's fine. .-)
an now my last error is gone :-) but now i have a new one :-/ in the queue view no more tickets can be viewed.
this error message appears in the log:
Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Zeile 1: Falsche Syntax in der Nähe von 'LIMIT'. (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 st.id, st.queue_id FROM ticket st, queue sq WHERE sq.id = st.queue_id AND st.ticket_state_id in ( 1, 4, 6, 7, 8 ) AND st.ticket_lock_id in ( 3, 1 ) AND st.queue_id in ( 5 ) AND sq.group_id IN ( 1, 2, 3, 24, 4, 10, 5, 11, 6, 12, 20, 7, 13, 21, 8, 14, 22, 23, 15, 9, 16, 17, 18, 19 ) ORDER BY st.ticket_priority_id DESC, st.create_time_unix ASC LIMIT 16'
Traceback (2012): Module: Kernel::Modules::AgentQueueView::Run (v1.36) Line: 173 Module: Apache::ROOT::otrs::index_2epl::handler (v1.58) Line: 608 Module: (eval) (v2.01) Line: 149 Module: Apache::Registry::handler (v2.01) Line: 149
maybe i schould switch back to mysql ;-)
Hmmm... not yet! The nice work! .-) I think it's my mistake. .-( Could you try it with an additional config option: $Self->{'Database::Limit'} = 0; (I added limit support to the mssql spec) And the "Zeile 1: Falsche Syntax in der Nähe von 'LIMIT'" error should go away.
Holger
Martin -- Martin Edenhofer - <martin at edenhofer.de> - http://martin.edenhofer.de/ -- "Security is a process, not a product." - Bruce Schneier
participants (2)
-
Holger.Struck@sqs.de
-
Martin Edenhofer