Active Directory OpenQuery 1000 row limit

Hello. When doing an OPENQUERY against Active Directory,
I hit the 1000 row limit. THat's all this query below
will return. We can get this to work, by writing in
VBSCRIPT and then reading multiple sets of 1000. Would
anyone know a way to use the SQL format below to get
beyond the 1000 row limit? I know there's a RANGE
parameter in ADO, but not sure IF or HOW to code for that
in an OPENQUERY... THanks, Bruce
SELECT givenName, sn, cn, mail, samAccountName
FROM OPENQUERY( ADSI, '<LDAP://domain_name>;
(&(objectCategory=person)(objectClass=User));
givenName,sn,cn,mail,samAccountName;subtree')Bruce,
You can do something like this:
1. Order the data returning from ADSI.
2. Find the max value returned.
3. Query again, starting after the last max value.
4. Loop until done.
I don't know of a way to get the entire result set back with TSQL.
Russell Fields
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:090301c3bdaa$70026140$a501280a@.phx.gbl...
> Hello. When doing an OPENQUERY against Active Directory,
> I hit the 1000 row limit. THat's all this query below
> will return. We can get this to work, by writing in
> VBSCRIPT and then reading multiple sets of 1000. Would
> anyone know a way to use the SQL format below to get
> beyond the 1000 row limit? I know there's a RANGE
> parameter in ADO, but not sure IF or HOW to code for that
> in an OPENQUERY... THanks, Bruce
>
> SELECT givenName, sn, cn, mail, samAccountName
> FROM OPENQUERY( ADSI, '<LDAP://domain_name>;
> (&(objectCategory=person)(objectClass=User));
> givenName,sn,cn,mail,samAccountName;subtree')
>
>|||Thanks Russell. Yes, it doesn't appear that you can get
ALL rows returned in one SQL statement, and I'm fine with
the loop method via SQL. But I can't seem to get that
working either. Seems like howeer I feliter in my query,
it's filtering within the 1000 max rows... Any way you
know to get more then the 1000 rows? Is there a range
parameter in LDAP, that will do the NEXT 1000 rows,
something like that' THanks, Bruce
>--Original Message--
>Bruce,
>You can do something like this:
>1. Order the data returning from ADSI.
>2. Find the max value returned.
>3. Query again, starting after the last max value.
>4. Loop until done.
>I don't know of a way to get the entire result set back
with TSQL.
>Russell Fields
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:090301c3bdaa$70026140$a501280a@.phx.gbl...
>> Hello. When doing an OPENQUERY against Active
Directory,
>> I hit the 1000 row limit. THat's all this query below
>> will return. We can get this to work, by writing in
>> VBSCRIPT and then reading multiple sets of 1000. Would
>> anyone know a way to use the SQL format below to get
>> beyond the 1000 row limit? I know there's a RANGE
>> parameter in ADO, but not sure IF or HOW to code for
that
>> in an OPENQUERY... THanks, Bruce
>>
>> SELECT givenName, sn, cn, mail, samAccountName
>> FROM OPENQUERY( ADSI, '<LDAP://domain_name>;
>> (&(objectCategory=person)(objectClass=User));
>> givenName,sn,cn,mail,samAccountName;subtree')
>>
>
>.
>|||Bruce,
I don't see my earlier reply, so I will try again.
Actually you need to plan your queries to bring back less that 1000 rows.
(NOT as clean as getting 1000, then the next 1000, etc.) For example,
within the query you might have:
x = 'smtp:Pager1*' -- brings back everything that matches this pattern
(in SQL terms "Like 'Pager1%'")
x = 'smtp:Pager2*' etc.
Russell Fields
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:078801c3be5b$44f05b90$a001280a@.phx.gbl...
> Thanks Russell. Yes, it doesn't appear that you can get
> ALL rows returned in one SQL statement, and I'm fine with
> the loop method via SQL. But I can't seem to get that
> working either. Seems like howeer I feliter in my query,
> it's filtering within the 1000 max rows... Any way you
> know to get more then the 1000 rows? Is there a range
> parameter in LDAP, that will do the NEXT 1000 rows,
> something like that' THanks, Bruce
>
> >--Original Message--
> >Bruce,
> >
> >You can do something like this:
> >1. Order the data returning from ADSI.
> >2. Find the max value returned.
> >3. Query again, starting after the last max value.
> >4. Loop until done.
> >
> >I don't know of a way to get the entire result set back
> with TSQL.
> >
> >Russell Fields
> >
> >"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> >news:090301c3bdaa$70026140$a501280a@.phx.gbl...
> >> Hello. When doing an OPENQUERY against Active
> Directory,
> >> I hit the 1000 row limit. THat's all this query below
> >> will return. We can get this to work, by writing in
> >> VBSCRIPT and then reading multiple sets of 1000. Would
> >> anyone know a way to use the SQL format below to get
> >> beyond the 1000 row limit? I know there's a RANGE
> >> parameter in ADO, but not sure IF or HOW to code for
> that
> >> in an OPENQUERY... THanks, Bruce
> >>
> >>
> >> SELECT givenName, sn, cn, mail, samAccountName
> >> FROM OPENQUERY( ADSI, '<LDAP://domain_name>;
> >> (&(objectCategory=person)(objectClass=User));
> >> givenName,sn,cn,mail,samAccountName;subtree')
> >>
> >>
> >>
> >
> >
> >.
> >

accessing reports via application

Hi,
Does anyone know how I can limit SS report access only through our
application? Thanks.
SSRS Standard EditionOn Oct 28, 7:44 am, Monet <Mon...@.gmail.com> wrote:
> Hi,
> Does anyone know how I can limit SS report access only through our
> application? Thanks.
> SSRS Standard Edition
There are three options for integrating Reporting Services into custom
applications: the Report Server Web service, also known as the
Reporting Services SOAP API, the ReportViewer controls for Visual
Studio 2005 and URL access. Each option provides a different approach
for integrating Reporting Services into your applications.
For Detail Refer http://msdn2.microsoft.com/en-us/library/ms153697.aspx|||Amit mentions the different ways to integrate with SSRS but you have an
additional requirement of the user only using this through your report. I
think the only way to do this is via forms authentication. This allows you
to control access. Next you would need to use webservices.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Monet" <MonetD@.gmail.com> wrote in message
news:1193539463.338544.262060@.22g2000hsm.googlegroups.com...
> Hi,
> Does anyone know how I can limit SS report access only through our
> application? Thanks.
> SSRS Standard Edition
>