• Sign In
  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