Hello Sean, thanks a lot for your mail and your willingness to help out here. For now I'd like to ask how it could be that SQL Azure does create the tables but then fails on running INSERT statements. That doesn't seem right to me. Can you provide a link to some documentation on this?
These are all tables where we don't need a primary key, that's why they don't have one. Regards, mg Am 29.03.14 02:50, schrieb Sean Killeen: > *Summary: *I'd like to alter some DB scripts to add clustered indexes so > they work better for MSSQL (and work at all for SQL Azure). How should I > go about this? > > Hi all, > > New to contributing to OTRS and to the dev mailing list so please > forgive me if I stumble around a bit. > > *Background / Issue* > > I wanted to set up OTRS on Azure using a SQL Azure database back-end, so > I thought I'd play around a bit. > > SQL Azure apparently chokes when confronted with tables that don't have > any clustered indexes. Womp womp. > > As of v3.3.5, the setup script for OTRS apparently creates 33 tables > without clustered indexes or primary keys (the list in full is at the > end of this post). This leads to inoperability and a ton of errors in > the log when attempting to use OTRS with SQL Azure. > > *What I'd like to do about it * > * > * > I'd like to help out with this and submit a pull request for the MSSQL > setup to deal with this (as well as make things a little more snappy for > MSSQL users). Was hoping to start this out as a Github issue, but I > don't believe Issues are enabled for the otrs/otrs project. > > However, I'm as of yet completely unfamiliar with the data structure, so > I would be guessing at best here and I don't think that's wise. :) > > Is there a solid reference point for the database architecture available > that I could reference to deduce where primary keys or clustered indexes > could be used in the affected tables? Or is there some other approach > the community would prefer I take to addressing this? > > OTRS is a great product, and I'm looking forward to being able to > hopefully give something back. > > Thanks, > -- > Sean > > *Appendix: The list of tables without clustered indexes* > > Found by running: > > SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID) > FROM SYS.INDEXES > WHERE INDEX_ID = 0 > AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1 > ORDER BY [TABLE] > > acl_sync > article_flag > customer_preferences > generic_agent_jobs > gi_object_lock_state > group_customer_user > group_role > group_user > link_relation > notification_event_item > personal_queues > pm_entity > pm_entity_sync > postmaster_filter > process_id > queue_preferences > queue_standard_template > role_user > search_profile > service_customer_user > service_preferences > service_sla > sla_preferences > support_bench_test > ticket_flag > ticket_index > ticket_lock_index > ticket_loop_protection > ticket_watcher > user_preferences > virtual_fs_preferences > web_upload_cache > xml_storage > > > > > _______________________________________________ > OTRS mailing list: dev - Webpage: http://otrs.org/ > Archive: http://lists.otrs.org/pipermail/dev > To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev > -- Martin Gruner Senior Developer R&D OTRS AG Bahnhofplatz 1a 94315 Straubing T: +49 (0)6172 681988 0 F: +49 (0)9421 56818 18 I: www.otrs.com/ Geschäftssitz: Bad Homburg, Amtsgericht: Bad Homburg, HRB 10751, USt-Nr.: DE256610065 Aufsichtsratsvorsitzender: Burchard Steinbild, Vorstand: André Mindermann (Vorsitzender), Christopher Kuhn, Sabine Riedel Einfache Planung, bessere Übersicht - Mit OTRS 3.3 einfach besseres Service Management - Jetzt downloaden und testen _______________________________________________ OTRS mailing list: dev - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/dev To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
