Last modified by Thomas Mortagne on 2023/10/10

Hide last authors
Vincent Massol 21.2 1 {{warning}}
2 This database is [[not supported>>dev:Community.SupportStrategy.DatabaseSupportStrategy]] by the XWiki developers.
3 {{/warning}}
4
JohannesStoldt 13.1 5 {{info}}
Manuel Smeria 14.4 6 This has been tested on MSSQL 2000 and MSSQL 2005 (text types are only deprecated in MSSQL 2005; this tutorial won't work when they'll be dropped).
JohannesStoldt 13.1 7 {{/info}}
Tobias2Roth2 1.2 8
JohannesStoldt 13.1 9 {{warning}}
Manuel Smeria 14.4 10 Case-insensitive search does not work with this database; see below for more info. You may want to use the the [[Lucene search application>>extensions:Extension.Search Application]].
JohannesStoldt 13.1 11 {{/warning}}
Tobias2Roth2 1.5 12
Manuel Smeria 14.4 13 = Create the xwiki user and database =
Tobias2Roth2 1.3 14
Ecaterina Moraru (Valica) 20.1 15 * Use the [[multi-wiki feature>>extensions:Extension.Wiki Application]] to create a database named "XWiki" and a user named "xwiki"
Manuel Smeria 14.4 16 * Set xwiki's password to //xwiki//
17 * Give database ownership of the XWiki database to the "xwiki" user
Tobias2Roth2 1.2 18
Manuel Smeria 14.4 19 = Install the JDBC driver =
Tobias2Roth2 1.2 20
Josef Haimerl 15.1 21 * Download the [[jtds jdbc driver>>http://sourceforge.net/projects/jtds/]] and install the jar file into your server's lib directory (for JBoss this could be ##server\default\lib\##, for Tomcat this might be ##common\lib\##)(((
JohannesStoldt 13.1 22 {{info}}
Manuel Smeria 14.4 23 The connection was successfully tested with version 1.2.1 of the driver and version 1.5 of XWiki. Some problems occurred when using drivers > 1.2.1 so it might be better to use the old one.
JohannesStoldt 13.1 24 {{/info}}
Manuel Smeria 14.4 25 )))
26 * Alternatively, you can use the [[JDBC driver provided by Microsoft>>http://www.microsoft.com/en-us/download/details.aspx?id=21599]], however this has not been thoroughly tested
Vincent Massol 3.2 27
Manuel Smeria 14.4 28 = XWiki configuration =
Tobias2Roth2 1.3 29
Manuel Smeria 14.4 30 * Configure XWiki to use MSSQL. To do this, edit the ##WEB-INF/hibernate.cfg.xml## file. Replace the matching properties with the following ones (or uncomment them if they are present):
tbkdan 9.1 31
Manuel Smeria 14.4 32 == JTDS ==
Tobias2Roth2 1.2 33
JohannesStoldt 13.1 34 {{code language="xml"}}
Thomas Mortagne 12.1 35 <property name="connection.url">jdbc:jtds:sqlserver://<server-url>:1433/XWiki;tds=8.0;lastupdatecount=true</property>
36 <property name="connection.username">xwiki</property>
37 <property name="connection.password">xwiki</property>
38 <property name="connection.driver_class">net.sourceforge.jtds.jdbc.Driver</property>
39 <property name="dialect">org.hibernate.dialect.SQLServerDialect</property>
40 <property name="connection.provider_class">com.xpn.xwiki.store.DBCPConnectionProvider</property>
41 <property name="connection.pool_size">2</property>
42 <property name="statement_cache.size">2</property>
Zaheer Abid 22.1 43 <mapping resource="xwiki.mssql.hbm.xml"></mapping>
44 <mapping resource="feeds.hbm.xml"></mapping>
45 <mapping resource="activitystream.hbm.xml"></mapping>
JohannesStoldt 13.1 46 {{/code}}
Tobias2Roth2 1.2 47
Manuel Smeria 14.4 48 == MS JDBC driver ==
JohannesStoldt 14.1 49
50 {{info}}
Manuel Smeria 14.4 51 Copy //sqljdbc.jar// from the downloaded package into ##\webapps\xwiki\WEB-INF\lib\##. If you have installed Sun JRE 1.6 (or above) you need to copy //**sqljdbc4.jar**// instead!
JohannesStoldt 14.1 52 {{/info}}
53
54 {{code language="xml"}}
55 <property name="connection.url">jdbc:sqlserver://localhost:1433;DatabaseName=XWiki</property>
56 <property name="connection.username">xwiki</property>
57 <property name="connection.password">xwiki</property>
58 <property name="connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
59 <property name="dialect">org.hibernate.dialect.SQLServerDialect</property>
60 <property name="connection.provider_class">com.xpn.xwiki.store.DBCPConnectionProvider</property>
61 <property name="connection.pool_size">2</property>
62 <property name="statement_cache.size">2</property>
Zaheer Abid 22.1 63 <mapping resource="xwiki.mssql.hbm.xml"></mapping>
64 <mapping resource="feeds.hbm.xml"></mapping>
65 <mapping resource="activitystream.hbm.xml"></mapping>
JohannesStoldt 14.1 66 {{/code}}
67
Manuel Smeria 14.4 68 = Troubleshooting =
Helmut Nagy 5.1 69
Manuel Smeria 14.4 70 == Fix search function ==
tbkdan 10.1 71
Thomas Mortagne 23.1 72 If you try to do a search, via [[Main.WebSearch]], you will get an error. This is because the UPPER() function doesn't work on TEXT or NTEXT as used by MSSQL 2000 for blobs. The only solution I have found is to remove all the calls to UPPER() in [[Main.WebSearch]]. This is however not very practical, because it makes search case-sensitive.
Tobias2Roth2 1.2 73
Manuel Smeria 14.4 74 Alternatively, you may want to use the [[Lucene search application>>extensions:Extension.Search Application]] instead of the default search.
steel 8.1 75
Josef Haimerl 15.1 76
77 == Fix filter in Livetable Macro ==
78
Ecaterina Moraru (Valica) 19.1 79 The filter function in the [[Livetable Macro>>doc:extensions:Extension.Livetable Macro]] is not working on MSSQL. The macro function livetable_addColumnToQuery (defined in XWiki.LiveTableResultsMacros) uses in an HQL Statement the function str (Line 576) with an string parameter. This function call is not correctly mapped to the SQL statement.
Josef Haimerl 15.1 80 A solution to fix the problem is to implement an extension of the SQLServerDialect class to be able to overwrite the registration of the STR function through the following one:
81
82 {{code language="java"}}
83 public class IWikiSQLServerDialect extends SQLServerDialect
84 {
85 /**
86 * constructor
87 */
88 public WikiSQLServerDialect()
89 {
90 super();
91 registerFunction("str", new SQLFunctionTemplate(StandardBasicTypes.STRING, "cast(?1 as varchar)"));
92 }
93 }
94 {{/code}}
95
Denis Gervalle 17.1 96 An XWiki extension is available for XWiki 6.2.2 (and later): [[SQL Server Hibernate Dialect for XWiki>>extensions:Extension.SQL Server Hibernate Dialect for XWiki]]
Denis Gervalle 16.1 97
Thomas Mortagne 18.1 98 For details see also [[Issue XWIKI-10606>>https://jira.xwiki.org/browse/XWIKI-10606]] on Jira.
Josef Haimerl 15.1 99
Zaheer Abid 22.1 100
101 == Fix Error Executing DDL with MSSQL Server 2019 ==
102
103 If you see the following error message when deploying XWiki with MSSQL Server 2019 and Tomcat9: -
104
105 {{{org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table xwiki.activitystream_events (ase_eventid varchar(48) not null, ase_requestid varchar(48), ase_stream varchar(255), ase_date datetime, ase_priority int, ase_type varchar(255), ase_application varchar(255), ase_user varchar(255), ase_wiki varchar(255), ase_space varchar(255), ase_page varchar(255), ase_hidden bit, ase_url text, ase_title text, ase_body text, ase_version varchar(30), ase_param1 text, ase_param2 text, ase_param3 text, ase_param4 text, ase_param5 text, primary key (ase_eventid))" via JDBC Statement
106 }}}
107
108 (% class="wikigeneratedid" %)
109 This can be fixed by creating the Schema manually in your MSSQL database. Queries generated by Hibernate only create the tables, not Schema, so if you create a Schema manually and assign it to a relevant owner then it will fix the error mentioned above.
110
111 (% class="wikigeneratedid" %)
112 For details, please visit [[Issue XWiki-18318>>https://jira.xwiki.org/browse/XWIKI-18318]].
113
114
Manuel Smeria 14.4 115 == Hints for upgrading to MS SQL 2005 ==
Tobias2Roth2 1.2 116
Manuel Smeria 14.4 117 In MS SQL 2005 the standard schema in all databases is dbo by default. However, if you upgrade your server with the standard Microsoft software, all tables in your xwiki database will be configured to use a schema whichs name is equivalent to the database name. (If your database name is "xwiki", the schema name will also be "xwiki" and the full qualified tablenames will be "xwiki.xwikidoc", and so on).
steel 8.1 118
Manuel Smeria 14.4 119 Since the standard schema is configured to be "dbo", xwiki will not find any tables (since it searches for "dbo.xwikidoc", ...). This will result in an exception when calling the xwiki webapp (in the logfiles you will see an "object not found" - exception for xwikidoc, because xwikidoc is the first table to be mapped via hibernate).
120
steel 8.1 121 There are 2 possible solutions to this problem:
122
Manuel Smeria 14.4 123 * Reconfigure your server to use "xwiki" as the standard scheme inside the xwiki-database
124 * Adopt the hibernate file (//xwiki.mssql.hbm.xml//). Look for the table definitions, eg. //table="xwikidoc"// and set it to //table="xwiki.xwikidoc"// or to whatever your schema/database is called. Do this for all the tables you can find in the file.

Get Connected