Query advice

I need a suggestion.
I have a table with
Columns
(Rec_ID, Amt_Recieved, Amt_given, Rec_Status, Rec_Frequency , Rec_date) here
Rec_ID and Rec_date are primary key
I need a resultset out of this table which has columns Rec_ID,
Amt_Recieved, Amt_given from the day previous to Rec_Date and Rec_Status,
Rec_Frequency , Rec_date corresponds to the Rec_date
For Eg.
(Rec_ID, Amt_Recieved, Amt_given, Rec_Status, Rec_Frequency , Rec_date)
1, 100,50,A,5,01/21/2005
1, 200,60,B,6,01/22/2005
1, 300,70,C,7,01/23/2005
2, 200,45,B,6,01/23/2005
2, 250,50,C,4,01/24/2005
I need out of it
1, 100,50,B,6,01/22/2005
1, 200,60,C,7,01/23/2005
1, 300,70,B,6,01/23/2005
2, 200,45,C,4,01/24/2005
I have tried inline queries for each column but it takes a lot of time.
Any advice shall be appreciated.
Thanks,
siajset dateformat mdy
create table #temp(Rec_ID int, Amt_Recieved int, Amt_given int,
Rec_Status char(1), Rec_Frequency int, Rec_date
smalldatetime)
insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
Rec_Frequency , Rec_date)
values (1, 100,50,'A',5,'01/21/2005')
insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
Rec_Frequency , Rec_date)
values (1, 200,60,'B',6,'01/22/2005')
insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
Rec_Frequency , Rec_date)
values (1, 300,70,'C',7,'01/23/2005')
insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
Rec_Frequency , Rec_date)
values (2, 200,45,'B',6,'01/23/2005')
insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
Rec_Frequency , Rec_date)
values (2, 250,50,'C',4,'01/24/2005')
select a.Rec_ID, a.Amt_Recieved, a.Amt_given,
b.Rec_Status, b.Rec_Frequency , b.Rec_date
from #temp a
inner join #temp b on a.Rec_Date=dateadd(day,-1,b.Rec_Date)
drop table #temp|||Thanks...Mark for the Reply..
There is a bit more to this the Previous day. The Previous date may not be
the immediate previous I am lloking for the maximum data previous to
Rec_date. Meaning if there is Record for 01/22 and 01/24 and not for 01/23
then we want data for 01/22
siaj
"markc600@.hotmail.com" wrote:

> set dateformat mdy
> create table #temp(Rec_ID int, Amt_Recieved int, Amt_given int,
> Rec_Status char(1), Rec_Frequency int, Rec_date
> smalldatetime)
> insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
> Rec_Frequency , Rec_date)
> values (1, 100,50,'A',5,'01/21/2005')
> insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
> Rec_Frequency , Rec_date)
> values (1, 200,60,'B',6,'01/22/2005')
> insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
> Rec_Frequency , Rec_date)
> values (1, 300,70,'C',7,'01/23/2005')
> insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
> Rec_Frequency , Rec_date)
> values (2, 200,45,'B',6,'01/23/2005')
> insert into #temp(Rec_ID, Amt_Recieved, Amt_given, Rec_Status,
> Rec_Frequency , Rec_date)
> values (2, 250,50,'C',4,'01/24/2005')
> select a.Rec_ID, a.Amt_Recieved, a.Amt_given,
> b.Rec_Status, b.Rec_Frequency , b.Rec_date
> from #temp a
> inner join #temp b on a.Rec_Date=dateadd(day,-1,b.Rec_Date)
> drop table #temp
>|||Can you try this:
select T1.Rec_ID,
T1.Amt_Received,
T1.Amt_given,
T2.Rec_Status,
T2.Rec_Frequency ,
T2.Rec_date,
T2.Rec_date
from TryRec T1,
TryRec T2
where (T1.Rec_date < T2.Rec_date
and not exists (select *
from TryRec T3
where (T3.Rec_date > T1.Rec_date and T3.Rec_date <
T2.Rec_date)
or (T3.Rec_date = T2.Rec_date and T3.Rec_ID <
T2.Rec_ID)
)
and T1.Rec_ID = (select max(Rec_ID)
from TryRec T4
where T1.Rec_date = T4.Rec_date))
or (T1.Rec_date = T2.Rec_date
and T1.Rec_ID < T2.Rec_ID
and not exists (select *
from TryRec T5
where (T5.Rec_date = T1.Rec_date
and T5.Rec_ID > T1.Rec_ID
and T5.Rec_ID < T2.Rec_ID)
)
)
order by 1, 2
Perayu
"siaj" <siaj@.discussions.microsoft.com> wrote in message
news:E417D534-1B46-4957-B352-0E3CFA0ACEC3@.microsoft.com...
>I need a suggestion.
> I have a table with
> Columns
> (Rec_ID, Amt_Recieved, Amt_given, Rec_Status, Rec_Frequency , Rec_date)
> here
> Rec_ID and Rec_date are primary key
>
> I need a resultset out of this table which has columns Rec_ID,
> Amt_Recieved, Amt_given from the day previous to Rec_Date and Rec_Status,
> Rec_Frequency , Rec_date corresponds to the Rec_date
>
> For Eg.
> (Rec_ID, Amt_Recieved, Amt_given, Rec_Status, Rec_Frequency , Rec_date)
> 1, 100,50,A,5,01/21/2005
> 1, 200,60,B,6,01/22/2005
> 1, 300,70,C,7,01/23/2005
> 2, 200,45,B,6,01/23/2005
> 2, 250,50,C,4,01/24/2005
> I need out of it
> 1, 100,50,B,6,01/22/2005
> 1, 200,60,C,7,01/23/2005
> 1, 300,70,B,6,01/23/2005
> 2, 200,45,C,4,01/24/2005
>
> I have tried inline queries for each column but it takes a lot of time.
> Any advice shall be appreciated.
>
> Thanks,
> siaj|||thanks Much ...It helps..
siaj
"Perayu" wrote:

> Can you try this:
> select T1.Rec_ID,
> T1.Amt_Received,
> T1.Amt_given,
> T2.Rec_Status,
> T2.Rec_Frequency ,
> T2.Rec_date,
> T2.Rec_date
> from TryRec T1,
> TryRec T2
> where (T1.Rec_date < T2.Rec_date
> and not exists (select *
> from TryRec T3
> where (T3.Rec_date > T1.Rec_date and T3.Rec_date
<
> T2.Rec_date)
> or (T3.Rec_date = T2.Rec_date and T3.Rec_ID <
> T2.Rec_ID)
> )
> and T1.Rec_ID = (select max(Rec_ID)
> from TryRec T4
> where T1.Rec_date = T4.Rec_date))
> or (T1.Rec_date = T2.Rec_date
> and T1.Rec_ID < T2.Rec_ID
> and not exists (select *
> from TryRec T5
> where (T5.Rec_date = T1.Rec_date
> and T5.Rec_ID > T1.Rec_ID
> and T5.Rec_ID < T2.Rec_ID)
> )
> )
> order by 1, 2
> Perayu
>
> "siaj" <siaj@.discussions.microsoft.com> wrote in message
> news:E417D534-1B46-4957-B352-0E3CFA0ACEC3@.microsoft.com...
>
>sql

query active directory

[code]
SELECT * FROM OPENQUERY(ADSI, 'SELECT name FROM 'LDAP://mydomain' WHERE objectClass='User'')

[/code
it worked just fine. but i want to select everything from active directory base on NT account (the account that user uses for their window logs in) and the user has to belong to a certain group (for instance: group = student)

now how do i do that? can you guys help. thank you

try:

Code Snippet

SELECT * FROM OPENQUERY(ADSI, 'SELECT * FROM 'LDAP://mydomain' WHERE objectClass='User'') tb

where name = suser_sname()

|||yeah but does this tell me if that user is a member of that group?|||

If you want to know if an user is a member of the group, you can use MemberOf.

e.g.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT name
FROM ''LDAP://cn=user,dc=ads''
WHERE MemberOf=''cn=,cn=users,dc=ads''
')derived

To determine all available groups, you can extract the ADsPath value.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT ADsPath
FROM ''LDAP://cn=user,dc=ads''
WHERE objectCategory=''Group''
')derived

You're better of doing all these ADSI stuffs through scripting and not via linked server.

|||what does cn, dc mean? I have to do it this way, ....doesn't want any other way to do it. so i have to follow orders.
|||

CN = Common Name

DC = Domain Content

Querying AD is a tricky business and very limited in SQL. You have to (1) have valid credential, (2) have a valid LDAP attribute.

|||

CREATE view View1
AS
select sAMAccountname, [name], physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath from openquery
(
ADSI,'SELECT sAMAccountName, name, physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath
FROM ''LDAP://my_domain''
WHERE MemberOf=''My_Student''')
Go

it selected nothing and i know there are many people that belong to that group|||

You need to fully qualify your member group.

e.g.

MemberOf=''cn=My_Student,cn=Users,dc=my_domain''

If you do not have a valid object, you will just get empty resultset for ADSI linked server. This is by design.

Post the a sample of a AdsPath value here so we can tell you exactly what your cn/dc would be.

|||

this is how i get to the group in active directory; I go into teachers, then groups, then "my teacher" (group name has space between)

here is the adspath

LDAP://mydomain/CN=Carlos Lopez,OU=US Histroy,OU=History Deparemnt,DC=teacher,DC=com

the adspath below is what i got from the below code

select sAMAccountname, [name], physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath from openquery
(
ADSI,'SELECT sAMAccountName, name, physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath
FROM ''LDAP://mydomain''
WHERE objectclass=''user''
')

what i want to do is to select the user on the specific group. plz help guys i need it quick. thank you|||

Try:

MemberOf=''OU=US Histroy,OU=History Deparemnt,DC=teacher,DC=com''

To get the right object for MemberOf, you should use the ADsPath value returned from the objectCategory=''Group''.

|||i will try this today. i think i tried it b4 already. but let me try it again today.
|||still got 0 records.|||

Alright, here is the _hard_ way.

1. Use ADSI and browse to the group you want to query.

2. Right click -> select Properties.

3. Find the distinguishedName value. (e.g. CN=Domain User,CN=Users,DC=pdx,DC=com)

4. Use that exact value for your MemberOf.

e.g.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT name,sAMAccountName,ADsPath
FROM ''LDAP://CN=Users,DC=pdx,DC=com''
WHERE MemberOf=''CN=Domain User,CN=Users,DC=pdx,DC=com''
') derived

Note that your LDAP will affect the resultset. It is the PARENT value for your memberOf value.

|||

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare:repare returned 0x80040e14].

that is what i got. dang it

|||

This typically refers to an invalid ldap value. At this point I suggest you contact PSS. You will need to give them the exact values to your AD (which you've obfuscated and not posted here).

query active directory

[code]
SELECT * FROM OPENQUERY(ADSI, 'SELECT name FROM 'LDAP://mydomain' WHERE objectClass='User'')

[/code
it worked just fine. but i want to select everything from active directory base on NT account (the account that user uses for their window logs in) and the user has to belong to a certain group (for instance: group = student)

now how do i do that? can you guys help. thank you

try:

Code Snippet

SELECT * FROM OPENQUERY(ADSI, 'SELECT * FROM 'LDAP://mydomain' WHERE objectClass='User'') tb

where name = suser_sname()

|||yeah but does this tell me if that user is a member of that group?|||

If you want to know if an user is a member of the group, you can use MemberOf.

e.g.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT name
FROM ''LDAP://cn=user,dc=ads''
WHERE MemberOf=''cn=,cn=users,dc=ads''
')derived

To determine all available groups, you can extract the ADsPath value.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT ADsPath
FROM ''LDAP://cn=user,dc=ads''
WHERE objectCategory=''Group''
')derived

You're better of doing all these ADSI stuffs through scripting and not via linked server.

|||what does cn, dc mean? I have to do it this way, ....doesn't want any other way to do it. so i have to follow orders.
|||

CN = Common Name

DC = Domain Content

Querying AD is a tricky business and very limited in SQL. You have to (1) have valid credential, (2) have a valid LDAP attribute.

|||

CREATE view View1
AS
select sAMAccountname, [name], physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath from openquery
(
ADSI,'SELECT sAMAccountName, name, physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath
FROM ''LDAP://my_domain''
WHERE MemberOf=''My_Student''')
Go

it selected nothing and i know there are many people that belong to that group|||

You need to fully qualify your member group.

e.g.

MemberOf=''cn=My_Student,cn=Users,dc=my_domain''

If you do not have a valid object, you will just get empty resultset for ADSI linked server. This is by design.

Post the a sample of a AdsPath value here so we can tell you exactly what your cn/dc would be.

|||

this is how i get to the group in active directory; I go into teachers, then groups, then "my teacher" (group name has space between)

here is the adspath

LDAP://mydomain/CN=Carlos Lopez,OU=US Histroy,OU=History Deparemnt,DC=teacher,DC=com

the adspath below is what i got from the below code

select sAMAccountname, [name], physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath from openquery
(
ADSI,'SELECT sAMAccountName, name, physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath
FROM ''LDAP://mydomain''
WHERE objectclass=''user''
')

what i want to do is to select the user on the specific group. plz help guys i need it quick. thank you|||

Try:

MemberOf=''OU=US Histroy,OU=History Deparemnt,DC=teacher,DC=com''

To get the right object for MemberOf, you should use the ADsPath value returned from the objectCategory=''Group''.

|||i will try this today. i think i tried it b4 already. but let me try it again today.
|||still got 0 records.|||

Alright, here is the _hard_ way.

1. Use ADSI and browse to the group you want to query.

2. Right click -> select Properties.

3. Find the distinguishedName value. (e.g. CN=Domain User,CN=Users,DC=pdx,DC=com)

4. Use that exact value for your MemberOf.

e.g.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT name,sAMAccountName,ADsPath
FROM ''LDAP://CN=Users,DC=pdx,DC=com''
WHERE MemberOf=''CN=Domain User,CN=Users,DC=pdx,DC=com''
') derived

Note that your LDAP will affect the resultset. It is the PARENT value for your memberOf value.

|||

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare:repare returned 0x80040e14].

that is what i got. dang it

|||

This typically refers to an invalid ldap value. At this point I suggest you contact PSS. You will need to give them the exact values to your AD (which you've obfuscated and not posted here).

query active directory

[code]
SELECT * FROM OPENQUERY(ADSI, 'SELECT name FROM 'LDAP://mydomain' WHERE objectClass='User'')

[/code
it worked just fine. but i want to select everything from active directory base on NT account (the account that user uses for their window logs in) and the user has to belong to a certain group (for instance: group = student)

now how do i do that? can you guys help. thank you

try:

Code Snippet

SELECT * FROM OPENQUERY(ADSI, 'SELECT * FROM 'LDAP://mydomain' WHERE objectClass='User'') tb

where name = suser_sname()

|||yeah but does this tell me if that user is a member of that group?|||

If you want to know if an user is a member of the group, you can use MemberOf.

e.g.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT name
FROM ''LDAP://cn=user,dc=ads''
WHERE MemberOf=''cn=,cn=users,dc=ads''
')derived

To determine all available groups, you can extract the ADsPath value.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT ADsPath
FROM ''LDAP://cn=user,dc=ads''
WHERE objectCategory=''Group''
')derived

You're better of doing all these ADSI stuffs through scripting and not via linked server.

|||what does cn, dc mean? I have to do it this way, ....doesn't want any other way to do it. so i have to follow orders.
|||

CN = Common Name

DC = Domain Content

Querying AD is a tricky business and very limited in SQL. You have to (1) have valid credential, (2) have a valid LDAP attribute.

|||

CREATE view View1
AS
select sAMAccountname, [name], physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath from openquery
(
ADSI,'SELECT sAMAccountName, name, physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath
FROM ''LDAP://my_domain''
WHERE MemberOf=''My_Student''')
Go

it selected nothing and i know there are many people that belong to that group|||

You need to fully qualify your member group.

e.g.

MemberOf=''cn=My_Student,cn=Users,dc=my_domain''

If you do not have a valid object, you will just get empty resultset for ADSI linked server. This is by design.

Post the a sample of a AdsPath value here so we can tell you exactly what your cn/dc would be.

|||

this is how i get to the group in active directory; I go into teachers, then groups, then "my teacher" (group name has space between)

here is the adspath

LDAP://mydomain/CN=Carlos Lopez,OU=US Histroy,OU=History Deparemnt,DC=teacher,DC=com

the adspath below is what i got from the below code

select sAMAccountname, [name], physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath from openquery
(
ADSI,'SELECT sAMAccountName, name, physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath
FROM ''LDAP://mydomain''
WHERE objectclass=''user''
')

what i want to do is to select the user on the specific group. plz help guys i need it quick. thank you|||

Try:

MemberOf=''OU=US Histroy,OU=History Deparemnt,DC=teacher,DC=com''

To get the right object for MemberOf, you should use the ADsPath value returned from the objectCategory=''Group''.

|||i will try this today. i think i tried it b4 already. but let me try it again today.
|||still got 0 records.|||

Alright, here is the _hard_ way.

1. Use ADSI and browse to the group you want to query.

2. Right click -> select Properties.

3. Find the distinguishedName value. (e.g. CN=Domain User,CN=Users,DC=pdx,DC=com)

4. Use that exact value for your MemberOf.

e.g.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT name,sAMAccountName,ADsPath
FROM ''LDAP://CN=Users,DC=pdx,DC=com''
WHERE MemberOf=''CN=Domain User,CN=Users,DC=pdx,DC=com''
') derived

Note that your LDAP will affect the resultset. It is the PARENT value for your memberOf value.

|||

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare:repare returned 0x80040e14].

that is what i got. dang it

|||

This typically refers to an invalid ldap value. At this point I suggest you contact PSS. You will need to give them the exact values to your AD (which you've obfuscated and not posted here).

query active directory

[code]
SELECT * FROM OPENQUERY(ADSI, 'SELECT name FROM 'LDAP://mydomain' WHERE objectClass='User'')

[/code
it worked just fine. but i want to select everything from active directory base on NT account (the account that user uses for their window logs in) and the user has to belong to a certain group (for instance: group = student)

now how do i do that? can you guys help. thank you

try:

Code Snippet

SELECT * FROM OPENQUERY(ADSI, 'SELECT * FROM 'LDAP://mydomain' WHERE objectClass='User'') tb

where name = suser_sname()

|||yeah but does this tell me if that user is a member of that group?|||

If you want to know if an user is a member of the group, you can use MemberOf.

e.g.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT name
FROM ''LDAP://cn=user,dc=ads''
WHERE MemberOf=''cn=,cn=users,dc=ads''
')derived

To determine all available groups, you can extract the ADsPath value.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT ADsPath
FROM ''LDAP://cn=user,dc=ads''
WHERE objectCategory=''Group''
')derived

You're better of doing all these ADSI stuffs through scripting and not via linked server.

|||what does cn, dc mean? I have to do it this way, ....doesn't want any other way to do it. so i have to follow orders.
|||

CN = Common Name

DC = Domain Content

Querying AD is a tricky business and very limited in SQL. You have to (1) have valid credential, (2) have a valid LDAP attribute.

|||

CREATE view View1
AS
select sAMAccountname, [name], physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath from openquery
(
ADSI,'SELECT sAMAccountName, name, physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath
FROM ''LDAP://my_domain''
WHERE MemberOf=''My_Student''')
Go

it selected nothing and i know there are many people that belong to that group|||

You need to fully qualify your member group.

e.g.

MemberOf=''cn=My_Student,cn=Users,dc=my_domain''

If you do not have a valid object, you will just get empty resultset for ADSI linked server. This is by design.

Post the a sample of a AdsPath value here so we can tell you exactly what your cn/dc would be.

|||

this is how i get to the group in active directory; I go into teachers, then groups, then "my teacher" (group name has space between)

here is the adspath

LDAP://mydomain/CN=Carlos Lopez,OU=US Histroy,OU=History Deparemnt,DC=teacher,DC=com

the adspath below is what i got from the below code

select sAMAccountname, [name], physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath from openquery
(
ADSI,'SELECT sAMAccountName, name, physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath
FROM ''LDAP://mydomain''
WHERE objectclass=''user''
')

what i want to do is to select the user on the specific group. plz help guys i need it quick. thank you|||

Try:

MemberOf=''OU=US Histroy,OU=History Deparemnt,DC=teacher,DC=com''

To get the right object for MemberOf, you should use the ADsPath value returned from the objectCategory=''Group''.

|||i will try this today. i think i tried it b4 already. but let me try it again today.
|||still got 0 records.|||

Alright, here is the _hard_ way.

1. Use ADSI and browse to the group you want to query.

2. Right click -> select Properties.

3. Find the distinguishedName value. (e.g. CN=Domain User,CN=Users,DC=pdx,DC=com)

4. Use that exact value for your MemberOf.

e.g.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT name,sAMAccountName,ADsPath
FROM ''LDAP://CN=Users,DC=pdx,DC=com''
WHERE MemberOf=''CN=Domain User,CN=Users,DC=pdx,DC=com''
') derived

Note that your LDAP will affect the resultset. It is the PARENT value for your memberOf value.

|||

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare:repare returned 0x80040e14].

that is what i got. dang it

|||

This typically refers to an invalid ldap value. At this point I suggest you contact PSS. You will need to give them the exact values to your AD (which you've obfuscated and not posted here).

query active directory

[code]
SELECT * FROM OPENQUERY(ADSI, 'SELECT name FROM 'LDAP://mydomain' WHERE objectClass='User'')

[/code
it worked just fine. but i want to select everything from active directory base on NT account (the account that user uses for their window logs in) and the user has to belong to a certain group (for instance: group = student)

now how do i do that? can you guys help. thank you

try:

Code Snippet

SELECT * FROM OPENQUERY(ADSI, 'SELECT * FROM 'LDAP://mydomain' WHERE objectClass='User'') tb

where name = suser_sname()

|||yeah but does this tell me if that user is a member of that group?|||

If you want to know if an user is a member of the group, you can use MemberOf.

e.g.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT name
FROM ''LDAP://cn=user,dc=ads''
WHERE MemberOf=''cn=,cn=users,dc=ads''
')derived

To determine all available groups, you can extract the ADsPath value.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT ADsPath
FROM ''LDAP://cn=user,dc=ads''
WHERE objectCategory=''Group''
')derived

You're better of doing all these ADSI stuffs through scripting and not via linked server.

|||what does cn, dc mean? I have to do it this way, ....doesn't want any other way to do it. so i have to follow orders.
|||

CN = Common Name

DC = Domain Content

Querying AD is a tricky business and very limited in SQL. You have to (1) have valid credential, (2) have a valid LDAP attribute.

|||

CREATE view View1
AS
select sAMAccountname, [name], physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath from openquery
(
ADSI,'SELECT sAMAccountName, name, physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath
FROM ''LDAP://my_domain''
WHERE MemberOf=''My_Student''')
Go

it selected nothing and i know there are many people that belong to that group|||

You need to fully qualify your member group.

e.g.

MemberOf=''cn=My_Student,cn=Users,dc=my_domain''

If you do not have a valid object, you will just get empty resultset for ADSI linked server. This is by design.

Post the a sample of a AdsPath value here so we can tell you exactly what your cn/dc would be.

|||

this is how i get to the group in active directory; I go into teachers, then groups, then "my teacher" (group name has space between)

here is the adspath

LDAP://mydomain/CN=Carlos Lopez,OU=US Histroy,OU=History Deparemnt,DC=teacher,DC=com

the adspath below is what i got from the below code

select sAMAccountname, [name], physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath from openquery
(
ADSI,'SELECT sAMAccountName, name, physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath
FROM ''LDAP://mydomain''
WHERE objectclass=''user''
')

what i want to do is to select the user on the specific group. plz help guys i need it quick. thank you|||

Try:

MemberOf=''OU=US Histroy,OU=History Deparemnt,DC=teacher,DC=com''

To get the right object for MemberOf, you should use the ADsPath value returned from the objectCategory=''Group''.

|||i will try this today. i think i tried it b4 already. but let me try it again today.
|||still got 0 records.|||

Alright, here is the _hard_ way.

1. Use ADSI and browse to the group you want to query.

2. Right click -> select Properties.

3. Find the distinguishedName value. (e.g. CN=Domain User,CN=Users,DC=pdx,DC=com)

4. Use that exact value for your MemberOf.

e.g.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT name,sAMAccountName,ADsPath
FROM ''LDAP://CN=Users,DC=pdx,DC=com''
WHERE MemberOf=''CN=Domain User,CN=Users,DC=pdx,DC=com''
') derived

Note that your LDAP will affect the resultset. It is the PARENT value for your memberOf value.

|||

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare:repare returned 0x80040e14].

that is what i got. dang it

|||

This typically refers to an invalid ldap value. At this point I suggest you contact PSS. You will need to give them the exact values to your AD (which you've obfuscated and not posted here).

query active directory

[code]
SELECT * FROM OPENQUERY(ADSI, 'SELECT name FROM 'LDAP://mydomain' WHERE objectClass='User'')

[/code
it worked just fine. but i want to select everything from active directory base on NT account (the account that user uses for their window logs in) and the user has to belong to a certain group (for instance: group = student)

now how do i do that? can you guys help. thank you

try:

Code Snippet

SELECT * FROM OPENQUERY(ADSI, 'SELECT * FROM 'LDAP://mydomain' WHERE objectClass='User'') tb

where name = suser_sname()

|||yeah but does this tell me if that user is a member of that group?|||

If you want to know if an user is a member of the group, you can use MemberOf.

e.g.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT name
FROM ''LDAP://cn=user,dc=ads''
WHERE MemberOf=''cn=,cn=users,dc=ads''
')derived

To determine all available groups, you can extract the ADsPath value.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT ADsPath
FROM ''LDAP://cn=user,dc=ads''
WHERE objectCategory=''Group''
')derived

You're better of doing all these ADSI stuffs through scripting and not via linked server.

|||what does cn, dc mean? I have to do it this way, ....doesn't want any other way to do it. so i have to follow orders.
|||

CN = Common Name

DC = Domain Content

Querying AD is a tricky business and very limited in SQL. You have to (1) have valid credential, (2) have a valid LDAP attribute.

|||

CREATE view View1
AS
select sAMAccountname, [name], physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath from openquery
(
ADSI,'SELECT sAMAccountName, name, physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath
FROM ''LDAP://my_domain''
WHERE MemberOf=''My_Student''')
Go

it selected nothing and i know there are many people that belong to that group|||

You need to fully qualify your member group.

e.g.

MemberOf=''cn=My_Student,cn=Users,dc=my_domain''

If you do not have a valid object, you will just get empty resultset for ADSI linked server. This is by design.

Post the a sample of a AdsPath value here so we can tell you exactly what your cn/dc would be.

|||

this is how i get to the group in active directory; I go into teachers, then groups, then "my teacher" (group name has space between)

here is the adspath

LDAP://mydomain/CN=Carlos Lopez,OU=US Histroy,OU=History Deparemnt,DC=teacher,DC=com

the adspath below is what i got from the below code

select sAMAccountname, [name], physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath from openquery
(
ADSI,'SELECT sAMAccountName, name, physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath
FROM ''LDAP://mydomain''
WHERE objectclass=''user''
')

what i want to do is to select the user on the specific group. plz help guys i need it quick. thank you|||

Try:

MemberOf=''OU=US Histroy,OU=History Deparemnt,DC=teacher,DC=com''

To get the right object for MemberOf, you should use the ADsPath value returned from the objectCategory=''Group''.

|||i will try this today. i think i tried it b4 already. but let me try it again today.
|||still got 0 records.|||

Alright, here is the _hard_ way.

1. Use ADSI and browse to the group you want to query.

2. Right click -> select Properties.

3. Find the distinguishedName value. (e.g. CN=Domain User,CN=Users,DC=pdx,DC=com)

4. Use that exact value for your MemberOf.

e.g.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT name,sAMAccountName,ADsPath
FROM ''LDAP://CN=Users,DC=pdx,DC=com''
WHERE MemberOf=''CN=Domain User,CN=Users,DC=pdx,DC=com''
') derived

Note that your LDAP will affect the resultset. It is the PARENT value for your memberOf value.

|||

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare:repare returned 0x80040e14].

that is what i got. dang it

|||

This typically refers to an invalid ldap value. At this point I suggest you contact PSS. You will need to give them the exact values to your AD (which you've obfuscated and not posted here).