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.
Note on permissions: To install CLR functions, the database user must have sysadmin permissions. When installing on SQL Server in an Amazon AWS RDS instance, this permission is not available. Instead, you need to create a DB Parameter group in RDS. This advanced feature enables installation of CLRs.
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 in the banner message to download the required script, named "groupConcatInstall.sql"
- Click View installation instructions in the banner message 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)
Related Topics
