Useful queries for MSSQL on Clarity

Discussion created by ivan_obradovic Employee on Aug 20, 2013
Latest reply on Aug 21, 2013 by another_martink
If you are using Clarity on MSSQL database server, here are some tips that might help you check your database.

1. Which port is SQL using?

By default MSSQL is using port 1433. However this port can be changed for security reasons. To find out which port your SQL instance is using you can run the following query:

USE master
xp_readerrorlog 0, 1, N'Server is listening on', 'any', NULL, NULL

2. Database user and Instance login are not linked?

When restoring the database, database user ‘niku’ is not automatically linked to instance user ‘niku’. Usual action is to delete database user and create a new user ‘niku’ linking him to instance user. Easyer way is to run the following query:

use <clarity_db>
EXEC sp_change_users_login 'Update_One', 'niku', 'niku'

3. What is compatibility level?
Current compatibility level of all Clarity servers is 90 (MSSQL 2005). It can be checked in database properties, under ‘Options’. Also you can use this query to find out compatibility mode:

select compatibility_level from sys.databases where name=<clarity_db>

4. Is the default schema ‘niku’?

In order to connect the user to Clarity, database user needs to have default schema set to niku.
You can find it out in database user properties. Query for this is:

use <clarity_db>
select name as db_username, default_schema_name
from sys.database_principals
where name='niku'

5. Were required scripts executed on my SQL server?

There is a set of scripts that can be found in Installation guide that are required to be executed for Clarity database to be set properly.
To confirm that your Clarity database is set correctly, check the outcome of this query:

from sys.databases where name = 'Database Name'

Result should be the following:
1 1 1 90 SQL_Latin1_General_CP1_CI_AS 1