This topic explains how to install the Microsoft SQL Server GROUP_CONCAT CLR (Common Language Runtime) functions. You may need to install these functions as part of setting up a shared SQL Server installation.
GROUP_CONCAT is a SQL aggregate function (similar to SUM, MIN, or MAX) that combines values from multiple rows into a single string value. For example, executing GROUP_CONCAT on a column with row values "First", "Second", and "Third" produces a single value "First, Second, Third". Some databases, such as MySQL, include this as a built-in function. Microsoft SQL Server does not, so LabKey requires a CLR function that implements the capability.
This function is typically installed automatically as part of the regular LabKey installation process. However, the process can fail if, for example, the database user does not have permission to install CLR functions. In these cases, a database administrator needs to install the function manually.
On a workstation with a connection to the Microsoft SQL Server Database Server:
- If the automatic installation has failed, site administrators will see a banner message on the running server reading "The GROUP_CONCAT aggregate function is not installed. This function is required for optimal operation of this server." with two links:
- Click Download installation script to download the required script: "groupConcatInstall.sql"
- Click View installation instructions to open to this topic.
- Connect to the Microsoft SQL Server using an account with membership in the sysadmin role.
- Execute the downloaded SQL script in the database.
- Confirm that group_concat is installed in the core schema.
- Restart Tomcat. The changes to the database will be recognized by the server only after a restart.
The Microsoft SQL Server GROUP_CONCAT CLR functions were developed by opcthree
Code is published at http://groupconcat.codeplex.com/
and licensed under the Microsoft Public License (Ms-PL)