• Sign In
×
The 2025 Client Survey is now open! Responses will be accepted up until 10/31. Please feel free to share this link within your organization and we thank you in advance for your participation!
  1. Messages

MS SQL Server problem

Installation Forum (Inactive)

View Message

view list print
MS SQL Server problem Lind-Thomsen  2015-09-16 05:10
Status: Closed
 
Hi All

I'm having trouble with using MS SQL server 2012 (Build number: 11.0.3381.0)

I made a manual install on Windows 8 (danish language):
labkey 15.2 (build39349.20)
Java 8
Tomcat 7.0
postgresql 9.4

And it worked. But with our setup it would be better to use MS SQL Server because we use that for everything else.

I then changed the database to SQL Server and then I did get errors like:
java.sql.SQLException: Parameter #20 has not been set.

Does anybody knows about anything which might cause this? Any hints and thoughts will be appreciated.

I have attached labkey.xml and labkey.log.

Best wishes
Allan Lind-Thomsen
 
 
adam responded:  2015-09-16 08:50
Allan,

The first exception in the log points out the problem: the database user doesn't have permission to install the GROUP_CONCAT aggregate function. This function is used in many places in the system, including the experiment runs query that's failing with the "Parameter #20" message.

At this point, the easiest solution is to run the GROUP_CONCAT installation script manually as a user with the necessary permissions. See https://www.labkey.org/wiki/home/Documentation/page.view?name=groupconcatinstall

Adam
 
Lind-Thomsen responded:  2015-09-17 06:44
Dear Adam

Thanks for your reply. I had noticed the message about group_concat but only thought it was a nice to have feature and not a need to have.

I actually have it installed, and is able to run the test script, but when I run labkey it still says that it is not installed.
Are you aware of any configuration/permission issues that might be different between using management studio and JDBC?

Best Wishes
Allan
 
Jon (LabKey DevOps) responded:  2015-09-17 15:29
Hi Allan,

Can you provide us with the newer error you're receiving saying it's not installed? A full stack error will help us better troubleshoot the issue.

Regards,

Jon
 
Lind-Thomsen responded:  2015-09-18 00:57
Dear Jon

Thanks for your effort.

I guess it is not really a new error because the group_concat script has been installed the whole time it just wasn't recognised.
My guess is still some permission issue.

I have looked through the logs (labkey.log, catalina) and found what might be a related error from a nightly maintenance step.
I located one stacktrace from the installation attempt of group_concat.
These stacktraces are in a seperate file attachment: labkey_summary.log.

I have also attached the whole log, but I think this is basically the same as the log I initially attached.

Best wishes
Allan
 
adam responded:  2015-09-18 08:48
The script installs, and LabKey Server expects to find, a couple specific functions in the "core" schema. Try executing these two statements from Microsoft SQL Server Management Studio while logged in as the labkey user:
  • SELECT core.GroupConcatVersion() -- Should return 1.00.23696
  • SELECT x.G, core.GROUP_CONCAT('Foo') FROM (SELECT 1 AS G) x GROUP BY G -- Should return a result 1, 'Foo'
If they don't execute correctly then you'll need to run the script manually.

In addition, your labkey database user doesn't have permission to run "EXEC sp_updatestats" which you'll want to fix.

Adam
 
Lind-Thomsen responded:  2015-09-21 00:34
Dear Adam

Thank you, that gave the last piece of the puzzle I missed to solve the problem.

I found that the group_concat function was installed under the dbo schema and not the core schema, when I moved the functions and restarted the server it worked.

Best wishes Allan