Last modified by Thomas Mortagne on 2023/10/10

<
From version < 12.1 >
edited by Thomas Mortagne
on 2010/09/03
To version < 14.1 >
edited by JohannesStoldt
on 2011/01/17
>
Change comment: Added some info on how to use with MSSQL 2008 R2

Summary

Details

Page properties
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.ThomasMortagne
1 +XWiki.JohannesStoldt
Syntax
... ... @@ -1,1 +1,1 @@
1 -XWiki 1.0
1 +XWiki 2.1
Content
... ... @@ -1,29 +1,36 @@
1 -1 Microsoft SQL Server Installation
1 += Microsoft SQL Server Installation =
2 2  
3 -#info("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)")
3 +{{info}}
4 +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)
5 +{{/info}}
4 4  
5 -#warning("Case&#45;insensitive search does not work with this database; see below for more info. You may want to use the the [Lucene search>code:Applications.SearchApplication]")
7 +{{warning}}
8 +Case-insensitive search does not work with this database; see below for more info. You may want to use the the [[Lucene search>>extensions:Extension.Search Application]]
9 +{{/warning}}
6 6  
7 -1.1 Create the xwiki user and database
11 +== Create the xwiki user and database ==
8 8  
9 9  * Use the Enterprise Manager to create a database named 'XWiki' and a user named 'xwiki'.
10 10  * Set xwiki's password to 'xwiki'.
11 11  * Give database ownership of the database XWiki to the user xwiki.
12 12  
13 -1.1 Install the JDBC driver
17 +== Install the JDBC driver ==
14 14  
15 -* Download the [jtds jdbc driver>http://jtds.sourceforge.net/] and install the jar file into your servers lib directory (for JBoss this could be ~~server\\\default\\\lib\\\~~, for Tomcat this might be ~~common\\\lib\\\~~)\\
19 +* Download the [[jtds jdbc driver>>http://jtds.sourceforge.net/]] and install the jar file into your servers lib directory (for JBoss this could be //server\default\lib\//, for Tomcat this might be //common\lib\//)
16 16  
17 -#info("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. ")
21 +{{info}}
22 +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.
23 +{{/info}}
18 18  
19 -* Alternatively, you can use the [JDBC driver provided by Microsoft>http://www.microsoft.com/downloads/details.aspx?familyid=07287B11-0502-461A-B138-2AA54BFDC03A&displaylang=en], however this has not been thoroughly tested.
25 +* Alternatively, you can use the [[JDBC driver provided by Microsoft>>http://www.microsoft.com/downloads/en/details.aspx?FamilyID=a737000d-68d0-4531-b65d-da0f2a735707&displaylang=en]], however this has not been thoroughly tested.
20 20  
27 +== XWiki configuration ==
21 21  
22 -1.1 XWiki configuration
29 +* Tell 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):
23 23  
24 -* Tell XWiki to use MSSQL. To do this, edit the ~~WEB&#45;INF/hibernate.cfg.xml~~ file. Replace the matching properties with the following ones (or uncomment them if they are present):
31 +=== JTDS ===
25 25  
26 -{code:xml}
33 +{{code language="xml"}}
27 27  <property name="connection.url">jdbc:jtds:sqlserver://<server-url>:1433/XWiki;tds=8.0;lastupdatecount=true</property>
28 28  <property name="connection.username">xwiki</property>
29 29  <property name="connection.password">xwiki</property>
... ... @@ -35,25 +35,42 @@
35 35  <mapping resource="xwiki.mssql.hbm.xml"/>
36 36  <mapping resource="feeds.hbm.xml"/>
37 37  <mapping resource="activitystream.hbm.xml"/>
38 -{code}
45 +{{/code}}
39 39  
47 +=== MS JDBC driver ===
40 40  
49 +{{info}}
50 +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!
51 +{{/info}}
41 41  
42 -1.1 Troubleshooting
53 +{{code language="xml"}}
54 +<property name="connection.url">jdbc:sqlserver://localhost:1433;DatabaseName=XWiki</property>
55 +<property name="connection.username">xwiki</property>
56 +<property name="connection.password">xwiki</property>
57 +<property name="connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
58 +<property name="dialect">org.hibernate.dialect.SQLServerDialect</property>
59 +<property name="connection.provider_class">com.xpn.xwiki.store.DBCPConnectionProvider</property>
60 +<property name="connection.pool_size">2</property>
61 +<property name="statement_cache.size">2</property>
62 +<mapping resource="xwiki.mssql.hbm.xml"/>
63 +<mapping resource="feeds.hbm.xml"/>
64 +<mapping resource="activitystream.hbm.xml"/>
65 +{{/code}}
43 43  
44 -1.1.1 Fix search function
67 +== Troubleshooting ==
45 45  
46 -If you try to do a search, via [Main.WebSearch], you will get an error. This is because the UPPER() function doesn not 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&#45;sensitive.
69 +=== Fix search function ===
47 47  
48 -Alternatively, you may want to use the [Lucene search>code:Applications.SearchApplication] instead of the default search.
71 +If you try to do a search, via [[Main.WebSearch]], you will get an error. This is because the UPPER() function doesn not 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.
49 49  
50 -1.1.1 Hints for upgrading to MS SQL 2005
73 +Alternatively, you may want to use the [[Lucene search>>extensions:Extension.Search Application]] instead of the default search.
51 51  
75 +=== Hints for upgrading to MS SQL 2005 ===
76 +
52 52  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)
53 53  
54 -Since the standard schema in 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)\\
55 -\\
79 +Since the standard schema in 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)
56 56  There are 2 possible solutions to this problem:
57 57  
58 58  * Reconfigure your server to use "xwiki" as standard scheme inside the xwiki-database.
59 -* 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 tables you can find in the file.
83 +* 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 tables you can find in the file.
XWiki.XWikiComments[5]
Comment
... ... @@ -1,6 +1,1 @@
1 -Comment for 'Fix search function' on SQL server 2008
2 -After googling I found out that sql UPPER function does not work with text sql types.
3 -You don't have to remove all upper() calls in websearch, you need to modify three calls to upper() that uses text sql type.
4 -upper(doc.content) replace with UPPER(SUBSTRING(doc.content,1,DATALENGTH(doc.content)))
5 -upper(prop.value) replace with UPPER(SUBSTRING(prop.value,1,DATALENGTH(prop.value))) - on two places
6 -After that I got rid of nasty exception: xwiki search Failed to execute macro...
1 +Comment for 'Fix search function' on SQL server 2008 After googling I found out that sql UPPER function does not work with text sql types. You don't have to remove all upper() calls in websearch, you need to modify three calls to upper() that uses text sql type. upper(doc.content) replace with UPPER(SUBSTRING(doc.content,1,DATALENGTH(doc.content~)~)~) upper(prop.value) replace with UPPER(SUBSTRING(prop.value,1,DATALENGTH(prop.value~)~)~) - on two places After that I got rid of nasty exception: xwiki search Failed to execute macro...
XWiki.XWikiComments[6]
Comment
... ... @@ -1,8 +1,11 @@
1 1  To get XWiki Enterprise 2.2.1 running in GlassFish 2.1.1 (Java 6) with MSSQL Server 2005, we found these infos very helpful:
2 2  
3 -* [http://xwiki.475771.n2.nabble.com/XWiki-Glassfish-2-1-1-cglib-td4186341.html]
4 -* the Microsoft JDBC driver <tt>sqljdbc-2.0.1803.jar</tt> works great
5 -* hibernate.cfg.xml:{code:type=xml}<property name="connection.url">jdbc:sqlserver://localhost:1433;DatabaseName=xwiki</property>
3 +* [[http://xwiki.475771.n2.nabble.com/XWiki-Glassfish-2-1-1-cglib-td4186341.html]]
4 +* the Microsoft JDBC driver ##sqljdbc-2.0.1803.jar## works great
5 +* hibernate.cfg.xml:
6 +
7 +{{code type="xml"}}
8 +<property name="connection.url">jdbc:sqlserver://localhost:1433;DatabaseName=xwiki</property>
6 6  <property name="connection.username">xwiki</property>
7 7  <property name="connection.password">xwiki</property>
8 8  <property name="connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
... ... @@ -12,6 +12,8 @@
12 12  <property name="statement_cache.size">2</property>
13 13  <mapping resource="xwiki.mssql.hbm.xml"/>
14 14  <mapping resource="feeds.hbm.xml"/>
15 -<mapping resource="activitystream.hbm.xml"/>{code}
18 +<mapping resource="activitystream.hbm.xml"/>
19 +{{/code}}
20 +
16 16  * create C:/Programme/glassfish/glassfish/domains/xwiki/applications/j2ee-modules/xwiki-enterprise-web-2.2.1/WEB-INF/classes/xwiki.mssql.hbm.xml
17 -* set <tt>xwiki.store.hibernate.updateschema=1</tt> and <tt>xwiki.store.migration=1</tt> in <tt>xwiki.cfg</tt>
22 +* set ##xwiki.store.hibernate.updateschema=1## and ##xwiki.store.migration=1## in ##xwiki.cfg##
Date
... ... @@ -1,1 +1,1 @@
1 -2010-09-03 12:49:22.391
1 +2010-09-03 12:49:22.0

Get Connected