Hi,
I'm trying to write a query in a script library to get a number of accounts in ADSAccountInADSGroupTotal that are members of a certain group. Then have a top(nn) clause to limit the number for moving them to another group.
I am using this:
Dim colObjects As IColDbObject = Nothing
Dim currentObject As ISingleDbObject = Nothing
Dim f As ISqlFormatter = Connection.SqlFormatter
Dim uidObject As String
colObjects = Connection.CreateCol("ADSAccountInADSGroupTotal")
colObjects.Prototype.WhereClause = f.Paging("select uid_ADSAccount from ADSAccountInADSGroupTotal Where UID_ADSGroup = '" & Current_UID_ADSGroup & "'","UID_ADSAccount",1,10)
colObjects.Prototype("UID_ADSAccount").IsDisplayItem = True
colObjects.Load()
(in case the code tag doesn't work:
Dim colObjects As IColDbObject = Nothing
Dim currentObject As ISingleDbObject = Nothing
Dim f As ISqlFormatter = Connection.SqlFormatter
Dim uidObject As String
colObjects = Connection.CreateCol("ADSAccountInADSGroupTotal")
colObjects.Prototype.WhereClause = f.Paging("select uid_ADSAccount from ADSAccountInADSGroupTotal Where UID_ADSGroup = '" & Current_UID_ADSGroup & "'","UID_ADSAccount",1,10)
colObjects.Prototype("UID_ADSAccount").IsDisplayItem = True
colObjects.Load()
And have been getting this error when doing a Test Script:
Exception has been thrown by the target of an invocation.
Loading list of 'Active Directory user accounts: assignments to groups' objects failed.
Error during execution of statement: select UID_ADSGroup, UID_ADSAccount from ADSAccountInADSGroupTotal where (select * from (select *, row_number() over(order by UID_ADSAccount) as XVIRowNumber from (select uid_ADSAccount from ADSAccountInADSGroupTotal Where UID_ADSGroup = '416fdf4a-f3e5-4859-8c29-c48f940b48d9') as x) as y where XVIRowNumber between 1 and 10 order by XVIRowNumber) order by UID_ADSAccount, UID_ADSGroup
Database error 1033:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
I am using the paging method for the first time and couldn't find any doc for it.
This is my attempt to get a batch of uid_ADSAccounts for further processing.
If you know other ways to perform the query I am open to suggestions too?
Any help is appreciated.
Thank You, Todd