Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I've been struggling hard with trying to get the Configurable ODBC directory service authentication to fully work. Currently i can pull in users but I am not able to see groups.
We are using version 11 and we have created a view on our source system to pull the appropriate fields in the appropriate manner (from what i can tell).
When i look at the log I see this:
7/25/2012 10:33:19.4190937 Information Start web service call ResolveGroups for user QV\george clooney
7/25/2012 10:33:19.4347187 Information (Database.DatabaseProvider) rd have been successfully created
7/25/2012 10:33:19.4347187 Error (Database.DatabaseProvider) DB-search wasn't successful:
System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'GROUP'.
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at Database.DatabaseProvider.DoSearch(String[] pattern, eSearchType[] searchTypes, String otherAttribute)
7/25/2012 10:33:19.4347187 Error (Database.DatabaseProvider) Exception 'System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'GROUP'.
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at Database.DatabaseProvider.DoSearch(String[] pattern, eSearchType[] searchTypes, String otherAttribute)
at Database.AbstractDSP.Search(String[] pattern, eSearchType type, String otherattribute)' when searching for 'Database.AbstractDSP+QueryBase'
7/25/2012 10:33:19.4347187 Warning (Database.DatabaseProvider) SearchCache lock inconsistency found (reader: False writer: True)
7/25/2012 10:33:19.4347187 Warning Resolving groups caused an exception ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'GROUP'.
Here is the SQL used to generate the 2 views:
User/Group view:
create view [sysdba].[QVENTITY] as (
SELECT distinct
cast(lower(u.userid) as nvarchar(30)) as entityid,
cast(lower(u.username) as nvarchar(30)) as entity_name,
cast(lower(u.username) as nvarchar(30)) as descr,
cast(lower(isnull(U.EMAIL,' ')) as nvarchar(30)) as email
from [A_DEV_75P3HF4].[sysdba].[USERINFO] u
union
SELECT distinct
cast(lower(roleid) as nvarchar(30)) as entityid,
cast(lower(rolename) as nvarchar(30)) as entity_name,
cast(lower(rolename) as nvarchar(30)) as descr,
cast(' ' as nvarchar(30)) as email
from [A_DEV_75P3HF4].[sysdba].ROLE
)
Membership view:
create view [sysdba].[QVUSERROLE] as (
SELECT distinct
cast(lower(u.userid) as nvarchar(30)) as memberid,
cast(lower(r.rolename) as nvarchar(30)) as groupid
FROM [A_DEV_75P3HF4].[sysdba].[ROLE] r
join [A_DEV_75P3HF4].[sysdba].[USERROLE] ur on ur.roleid=r.roleid
join [A_DEV_75P3HF4].[sysdba].[USERINFO] u on u.userid=ur.userid
)
This works fine.. by the way i grabbed this off someone elses post.. how did one peek at the query being used by Qlikview???
SELECT
e.entityid,
e.NAME,
e.descr,
e.email,
COUNT(g.memberid) AS noGroupMembers
FROM QVENTITY e
LEFT JOIN QVuserrole g ON e.entityid=g.groupid
WHERE
e.NAME = 'george clooney'
OR
e.descr = 'george clooney'
OR
e.NAME = 'george clooney'
GROUP BY
e.entityid,e.NAME,e.descr, e.email
When I look at the data types in the view they are all nvarchar.
SELECT distinct
sql_variant_property(groupid,'BaseType'),
sql_variant_property(memberid,'BaseType')
FROM [A_DEV_75P3HF4].[sysdba].[QVUSERROLE]
SELECT distinct
sql_variant_property(entityid,'BaseType'),
sql_variant_property(entity_name,'BaseType'),
sql_variant_property(descr,'BaseType'),
sql_variant_property(email,'BaseType')
FROM [A_DEV_75P3HF4].[sysdba].[QVENTITY]
Here is the configuration (which seems to work fine, again, i can pull up a list of users without a problem):
<?xml version="1.0" encoding="utf-8"?><dscresource><id>c8c41604-6b55-463f-b7bf-a16b9bebd978</id><name>QV (ODBC://sql2k5-x64-01)</name><type>odbc</type><path>ODBC://sql2k5-x64-01</path><uid>fd4NDOdMx+E=</uid><pwd>eWOTXmlgo5XUOpy+s9TLDg==</pwd><StringDictionarySerializer><Entry Key="ServiceTimeoutInSeconds" Value="30" /><Entry Key="DirectoryLabel" Value="QV" /><Entry Key="EntityName" Value="entity_name" /><Entry Key="EntityTableDbName" Value="sysdba.QVENTITY" /><Entry Key="GroupsTableDbName" Value="sysdba.QVUSERROLE" /><Entry Key="DataSourceName" Value="SQL Server" /><Entry Key="ConnDbName" Value="A_DEV_75P3HF4" /><Entry Key="OverrideConnectionString" Value="" /><Entry Key="CacheExpiryInMinutes" Value="15" /></StringDictionarySerializer></dscresource>
Is there a bug in qv 11 with configurable ODBC? I wouldn't think it should be this difficult.
Thanks in advance,
Chris
Chris, when you say you are not able to see groups, do you mean in QEMC Users tab?
Yes exactly, thanks Ralph, I can see the users I have brought in using the configurable ODBC but when i try to view a groups listing I don't see the groups I am trying to utilize per the query and it gives the log entry I pasted. It looks like there is a problem with a string not getting single quotes when the query is constructed on the qv server.
Thanks,
Chris
Hi Chris,
I got the same error using Configurable ODBC DSC connecting to MySQL.
I changed the type of the keys entityid, groupid and memberid from string to int - and everything works fine!
Got that hint from http://community.qlik.com/message/162432:
"Keep in mind, for group resolution to work at this time, the entityid field of your entity table, and the memberid and groupid fields in the group table cannot contain values greater than max(int), or 2.1 billion, because there is a bug in the group resolution code that doesn't allow numbers larger than that size to be resolved."
Thomas.