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).

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 acting strangely

I have some problems with this query. It returns some information but not all.
SELECT PAGE0.DOL, PAGE3.STATUS1, PAGE0.STATUS
FROM PAGE0 INNER JOIN PAGE3. ON PAGE0.SERIAL = PAGE3.P_SERIAL
WHERE (PAGE3.STATUS1 <> 'TOA') AND (PAGE0.STATUS = 'OPEN')
For whatever reason this query doesn't return all the info in the table. I tested it somewhat and I noticed that if I reference only one table in the WHERE clouse all is working fine (except I can not filter my query the way I want to). If I remove PAGE3.
STATUS <> 'TOA' then I get all the results. Is there sometihing wrong with this query? I am using SQL server 2000 SP3.
Thanks For any help.
Sebastian
On Thu, 8 Apr 2004 03:46:03 -0700, Sebastian wrote:

>I have some problems with this query. It returns some information but not all.
>SELECT PAGE0.DOL, PAGE3.STATUS1, PAGE0.STATUS
>FROM PAGE0 INNER JOIN PAGE3. ON PAGE0.SERIAL = PAGE3.P_SERIAL
>WHERE (PAGE3.STATUS1 <> 'TOA') AND (PAGE0.STATUS = 'OPEN')
>For whatever reason this query doesn't return all the info in the table. I tested it somewhat and I noticed that if I reference only one table in the WHERE clouse all is working fine (except I can not filter my query the way I want to). If I remove PAGE3
.STATUS <> 'TOA' then I get all the results. Is there sometihing wrong with this query? I am using SQL server 2000 SP3.
>Thanks For any help.
>Sebastian
I can't answer this without more information. Please post a repro
script that can help me reproduce and diagnose the problem.
A repro script consists of:
* DDL (create table statements) for the relevant tables, constraints
and indexes,
* insert statements with enough sample data to reproduce the error,
* the text of the query you're having trouble with.
Run the repro script in an empty (test) database to check that it
really reproduces the observed behaviour. Then, post it here, along
with the output you expected from that query.
Many of the regulars here will step in to help you correct the problem
if you post as suggested. Without that information, though, it's very
hard to help us at all (it's like phoning the garage, saying "why
doesn't my car work - it worked fine yesterday")
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||i am going to try to do what you just told me. Thank You. I will post my results soon.
Thank You again

Query across all colums

Hi folks. This is my first post around here.
So I want to query a select on a table. Is there a simpler way to match the where clause with all columns than referencing every single column in the where clause?
Thanks
Shabassanot really|||Ok, thanks was worth a question.|||If you are comparing all the columns in one table to all the columns in another, you may be able to use the CHECKSUM or BINARYCHECKSUM functions:

select subA.APKey, sbuB.BPKey
from
(select A.PKey as APKey, CHECKSUM(*) as ChecksumA from A) subA
full outer join
(select B.PKey as BPKey, CHECKSUM(*) as ChecksumB from B) subB
on subA.ChecksumA = subB.ChecksumB
where ...|||blindman, i think the problem was to write this --

... WHERE a LIKE '%x%' OR b LIKE '%x%' OR c LIKE '%x%' OR d LIKE '%x%'

in some easier fashion, e.g.

... WHERE allcolumns LIKE '%x%'

and the answer, of course, is "not really" :cool:|||While Rudy is quite right in terms of how SQL itself does things, there are extensions to many database engines that make this kind of search easier. In MS-SQL, this is called Full Text Indexing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_5dro.asp).

-PatP|||pat, all things considered, full text indexing is not "simpler" (one of shabassa's original requirements)

:)|||From the standpoint of managing the server, you are quite right. Full-Text Indexing brings its own problems to the table.

From the standpoint of writing a search query though, I feel that it is simpler and I don't think anyone would argue it is much less code.

-PatP|||blindman, i think the problem was to write this --

... WHERE a LIKE '%x%' OR b LIKE '%x%' OR c LIKE '%x%' OR d LIKE '%x%'

in some easier fashion, e.g.

... WHERE allcolumns LIKE '%x%'

and the answer, of course, is "not really" :cool:

Could be. Without a doubt he needs to be more specific.|||I doubt it though....

I think they're looking for duplicates...

Or they're just trying to establish a join between to related tables, just in the keys?|||Or attempting to create a search feature for their application that searches against a catalog of objects where the entry could match a value in any number of fields (Ex: Name, Description, Price, etc).|||Step right up, folks! Play "Guess the user requirements" and win a prize for the little lady! Who's next? Every player has a chance to win! You sir, yes you...!|||Well if I was programming an application that searched across the table there would be no problem doing it in some kind of loop. I was just lazy and wanted to look for a certain row where I entered a date in an application accessing that db. But I didn't know to which colum the text field in the app corresponded to. So I wanted to search across all columns at once using the Enterprise Manager.
I hope that clears things up. ;)

How would this be possible with full text indexing? Do I magically get some kind of search field?

Shabassa

query a LDAP server from MS SQL

I try to connect to a LDAP server from SQL query analyzer with the following string

(SELECT * FROM OPENQUERY
(ADSI,'SELECT * FROM ''LDAP://DC=test-02,DC=test,CD=no'' '))

This resulted in a error meesage:

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 [Non-interface error: OLE DB provider ADSDSOObject returned DBPROP_STRUCTUREDSTORAGE without DBPROPVAL_OO_BLOB being supported].
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].

What is the problem ? It is something wrong with my db-setup, the query or what ? I try to do the same thing in a VB-program and that worked fine.Did you setup the linked server?

exec sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

instead of select * try and name them to see if you can at least return something

SELECT [Name],SN[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://DC=test-02,DC=test,CD=no'' ')

also, if objects are in containers you can specify the container name by CN= or if in OU's - OU=

if you are only wanting users you can specify

WHERE objectCategory = ''Person'' AND objectClass = ''user''

HTHsql

Query 2 databases

Hi all,
Does anyone know how can I select data across 2 databases in SQL ? I want
to do something like this:
Select A.SentDate from dbo.DBA.TblA A, dbo.DBB.TblB B
Where B.FileName = '01012005.txt'
And A.AKey = B.BKey
But it doesn't work and give me an error "Invalid object name dbo.DBA.TblA",
guessing could be syntax error (?)... any idea?
Thanks !!
K.K>It should be
Database.owner.objectname as in
DBA.dbo.TblA
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"K.K." <someone@.microsoft.com> wrote in message
news:%23F$cuW6IFHA.4060@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> Does anyone know how can I select data across 2 databases in SQL ? I want
> to do something like this:
> Select A.SentDate from dbo.DBA.TblA A, dbo.DBB.TblB B
> Where B.FileName = '01012005.txt'
> And A.AKey = B.BKey
> But it doesn't work and give me an error "Invalid object name
> dbo.DBA.TblA", guessing could be syntax error (?)... any idea?
> Thanks !!
> K.K>
>
>

Query "hightest value" contents of each "folder"?

Hi,

Can anyone tell me how to select the "most recent" date values from a
grouped query? Consider the following:

CREATE TABLE [dbo].[TestQuery] ( [ID] [int] NOT NULL , [ID_Parent] [int] NOT
NULL , [Date] [datetime] NOT NULL ) ON [PRIMARY]

This is a simplified adjacency list. What I want to do is find the highest
valued item by date for each sub-tree. In other words, the single highest
date item when This.ID_Parent = That.ID_Parent. I think I first need to
group by ID_Parent, then select the TOP 1 from this query, but how to
aggregate them so I get the TOP 1 for each ID_Parent?

Thanks for any help you can give me,

RobinAnother way:

SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
FROM TestQuery AS T1
WHERE T1.date =
(SELECT MAX(date)
FROM TestQuery AS T2
WHERE T1.id_parent = T2.id_parent)
GROUP BY T1.id_parent, T1.date

--
David Portas
SQL Server MVP
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Try this:

SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
FROM TestQuery AS T1
LEFT JOIN TestQuery AS T2
ON T1.id_parent = T2.id_parent
AND T1.date < T2.date
WHERE T2.date IS NULL
GROUP BY T1.id_parent, T1.date

--
David Portas
SQL Server MVP
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||here is a related article:
subject: select first record from a group ordered by 3 columns
date: Nov 27 2002, 5:01 am

http://groups-beta.google.com/group...23b16be2b945c0f

David Portas wrote:
> Another way:
> SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
> FROM TestQuery AS T1
> WHERE T1.date =
> (SELECT MAX(date)
> FROM TestQuery AS T2
> WHERE T1.id_parent = T2.id_parent)
> GROUP BY T1.id_parent, T1.date
> --
> David Portas
> SQL Server MVP
> --
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Query - SUM multi-column

I use a query to get customer unpaid amount into different colomns according to due day as the following:

Select c.credit_controller,c.customer,c.name,i.unall_amount,
CASE /*cash due*/
when i.kind='CSH' then i.unall_amount
else 0
end as amtcash,
CASE /*sales_item.due_date - today < 0 */
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate()) < 0)
THEN i.unall_amount
ELSE 0
END AS amtcur,
CASE /*sales_item.due_date - today > 0 & <= 30*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
BETWEEN 0 AND 30) THEN i.unall_amount
ELSE 0
END AS amt30,
CASE /*sales_item.due_date - today > 30 & <= 60*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
BETWEEN 31 AND 60) THEN i.unall_amount
ELSE 0
END AS amt60,
CASE /*sales_item.due_date - today > 60 & <= 90*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
BETWEEN 61 AND 90) THEN i.unall_amount
ELSE 0
END AS amt90,
CASE /*sales_item.due_date - today > 90*/
WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())
> 90) THEN i.unall_amount
ELSE 0
END AS amt120,
c.analysis_codes
From scheme.slcustm c INNER JOIN scheme.slitemm i ON c.customer=i.customer

I want to SUM those records and GROUP BY c.credit_controller & c.customer in a SELECT query.

If I use SUM(i.unall_amount) instead of i.unall_amount, there're errors as the following:

Column 'c.cusomter' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column 'i.kind' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column c.analaysis_code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How to SUM in this query?

Thanks!

yabing

When you use GROUP BY statetment, then any column used in select list must be also GROUP BY list or under some aggregation function.

So if you want GROUP BY by c.credit_controller, c.customer you also need include c.name into GROUP BY, then use SUM(i.unall_amount) and SUM() around each CASE statement:

Code Snippet

Select c.credit_controller,c.customer,c.name, SUM(i.unall_amount),

SUM( CASE /*cash due*/

when i.kind='CSH' then i.unall_amount

else 0

end) as amtcash,

SUM( CASE /*sales_item.due_date - today < 0 */

WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate()) < 0)

THEN i.unall_amount

ELSE 0

END )AS amtcur,

SUM( CASE /*sales_item.due_date - today > 0 & <= 30*/

WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())

BETWEEN 0 AND 30) THEN i.unall_amount

ELSE 0

END )AS amt30,

SUM( CASE /*sales_item.due_date - today > 30 & <= 60*/

WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())

BETWEEN 31 AND 60) THEN i.unall_amount

ELSE 0

END )AS amt60,

SUM( CASE /*sales_item.due_date - today > 60 & <= 90*/

WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())

BETWEEN 61 AND 90) THEN i.unall_amount

ELSE 0

END )AS amt90,

SUM( CASE /*sales_item.due_date - today > 90*/

WHEN (i.kind='INV' OR i.kind='CRN') AND (DATEDIFF (day, i.due_date, getdate())

> 90) THEN i.unall_amount

ELSE 0

END ) AS amt120

From scheme.slcustm c INNER JOIN scheme.slitemm i ON c.customer=i.customer

GROUP BY c.credit_controller,c.customer,c.name

|||

Hi Konstantin,

Thanks for your replay. It works.

I understand "When you use GROUP BY statetment, then any column used in select list must be also GROUP BY list or under some aggregation function." But I only want to group value by two columns - c.credit_controller and c.customer, not c.name and c.analysis_codes. When using "GROUP BY c.credit_controller,c.customer,c.name,c.analysis_codes", how it groups columns? does it group the columns one by one for all the four columns in the list in the GROUP BY clause? does it aggregate values for everty possible combination of the columns in the GROUP BY clause?

Though it seems it doesn't group that way when testing. But why?

Thanks,

yabing

|||

If you use "GROUP BY c.credit_controller,c.customer,c.name,c.analysis_codes" its group and aggregate for every possible combinations. So if you have few different values of c.name,c.analysis_codes for each unique pair of c.credit_controller,c.customer you will get additional lines.

But for example if you have following values:

c.credit_controller c.customer c.name

1 1 A

1 1 B

and use GROUP BY c.credit_controller,c.customer plus c.name in SELECT list. What do you want see in your result set? A or B?

You must include c.name in aggregation. For example you could use MIN or MAX or (if you have SQL Server 2005) write you own User-Defined Aggregate.This UDAG could concat values or do something else.

|||

Dear Konstantin.

I have one question of GROUP BY,

When I use GROUP BY and use SUM around each CASE statement, it take very long query time.

Can we set a condition eg, query for c.name='A' only?

Thanks

pps1

|||You could write WHERE for filtering. GROUP BY works after WHERE

Query

Greetings,

I have three queries that I have to put together and they are based on the SELECT and GROUP BY.

1st Query requirement:
group the Employees by Job Code. Use a SELECT query.and modify the SQL

My answer is (and I'm not able to see the actual names and job codes):
SELECT 'EmployeeID' AS EmployeeID, 'FirstName' AS FirstName, 'LastName' AS LastName, 'JobTitleCode' AS JobTitleCode
FROM Employees_Table
GROUP BY EmployeeID, FirstName, LastName, JobTitleCode
ORDER BY JobTitleCode;

2nd Query requirement:
group the Employees by Salary. Use a SELECT Query and modify the SQL

My answer is (and very similar to the above, I'm not seeing the information):
SELECT 'Employee ID' AS EmployeeID, 'FirstName' AS FirstName, 'LastName' AS LastName, 'Salary' AS Salary
FROM Employees_Table
GROUP BY EmployeeID, FirstName, LastName, [Salary]
ORDER BY [Salary];

Last Query requirement:
group the Employees by Salary within their Job Code. Use a SELECT Query and modify the SQL

My rough draft answer (not really sure at this point :-( is:
SELECT 'First Name' AS FirstName, 'Last Name' AS LastName, 'Salary' AS Salary
FROM Employees_Table
GROUP BY 'Salary', 'Job Title Code';

Any words of wisdom would be greatly appreciated.

Thanks for your time!Looks like a homework...Are you sure the fieldnames are spelled correctly? I don't think your queries will run...

1st - ...will give an error on invalid field name
2nd - ...will do the same
3rd - ...same difference

But conceptually ... I don't think you are answering the questions. You need to loose GROUP BY and reverse the order of the fields in ORDER BY clause.|||Yes, the field names in my database are spelled the same exact way as I've listed them in the SQL statements?

Any other words of advice?

Thanks for your time.|||Yes, the field names in my database are spelled the same exact way as I've listed them in the SQL statements?

Any other words of advice?

Thanks for your time.
So, you're saying that 'EmployeeID' and 'Employee ID' are the same field or 2 different fields?|||You are correct and I've modified to the new statements below:

Query 1:
SELECT EmployeeID, FirstName, JobTitleCode
FROM Employees_Table
GROUP BY EmployeeID, FirstName, JobTitleCode
ORDER BY JobTitleCode;

Query 2:
SELECT EmployeeID, FirstName, [Salary]
FROM Employees_Table
WHERE Salary>45000
GROUP BY EmployeeID, FirstName, [Salary]
ORDER BY [Salary];

I hope I'm going in the correct direction with my answers?

Thanks for your responses!|||See my first post (loose GROUP BY, because you're not doing any aggregation)sql

Query

Hello friends!

I have a row like this

Select * from SalMax

Sid Sname Salary
---- ------- ---------
1 Raja 3000.0
2 Vasu 7000.0
5 Siva 1700.0
6 Viswa 10700.0
3 Prabu 5000.0
4 Vikram 9000.0
9 Sachin 10000.0
15 Dravid 14000.0

The Output I want is just the Reverse without using Order by any column

the Output I want is
15 Dravid 14000.0
9 Sachin 10000.0
4 Vikram 9000.0
3 Prabu 5000.0
6 Viswa 10700.0
5 Siva 1700.0
2 Vasu 7000.0
1 Raja 3000.0

Thanks & Regards
E.ElayaRajawithout using an ORDER BY? why not? that's the only way

rudy|||Originally posted by ThankuGod
Hello friends!

I have a row like this

Select * from SalMax

Sid Sname Salary
---- ------- ---------
1 Raja 3000.0
2 Vasu 7000.0
5 Siva 1700.0
6 Viswa 10700.0
3 Prabu 5000.0
4 Vikram 9000.0
9 Sachin 10000.0
15 Dravid 14000.0

The Output I want is just the Reverse without using Order by any column

the Output I want is
15 Dravid 14000.0
9 Sachin 10000.0
4 Vikram 9000.0
3 Prabu 5000.0
6 Viswa 10700.0
5 Siva 1700.0
2 Vasu 7000.0
1 Raja 3000.0

Thanks & Regards
E.ElayaRaja|||RE: Hello friends!

I have a row like this

Select * from SalMax
Sid Sname Salary
---- ------- ---------
1 Raja 3000.0
2 Vasu 7000.0
The Output I want is just the Reverse without using Order by any column
the Output I want is
15 Dravid 14000.0
9 Sachin 10000.0
Thanks & Regards E.ElayaRaja

Q1 The Output I want is [descending Salary] without using Order by
A1 Create a clustered descending index on Salary.|||My Question is not answered. I think u didnt undestand my question.
I repeat

I have a row like this

Select * from SalMax

Sid Sname Salary
---- ------- ---------
7 Raja 3000.0
2 Vasu 7000.0
5 Siva 1700.0
15 Dravid 14000.0

The Output I want is just the Reverse without using Order by any column

the Output I want is
15 Dravid 14000.0
5 Siva 1700.0
2 Vasu 7000.0
7 Raja 3000.0

I just want the reverse of the query .

Thanks & Regards
E.ElayaRaja|||Do you mean that you first run a query which results in a set of records, returned in no particular order at all - then, you would like to get that very same set of records just displayed in the reverse order?

May I ask what the problem behind is?

I guess you could select the records into a temporary table, to which you then add the clustered descending index?|||RE: My Question is not answered. I think u didnt undestand my question...
I just want the reverse of the query . Thanks & Regards E.ElayaRaja

Q1 [I think u didnt undestand my question]?
A1 True I did not, that was why it was restated and paraphrased.

Q2 [Given a set of arbitrary query results, (returned in no particular order): How would may one get that very same set of records returned in the reverse order, (BUT) without using ORDER BY?]

A2 The Coolberg post correctly alluded to the answer of Q2. Insert the result set into a work table with an additional autoincrementing identity column. Then alter the work table to make the additional identity column clustered and descending. Finally, a select from the work table of all columns (except the additional identity column) should return the very same set of records, in the reverse order.|||Insert the result set into a work table with an additional autoincrementing identity column. Then alter the work table to make the additional identity column clustered and descending. Finally, a select from the work table of all columns (except the additional identity column) should return the very same set of records, in the reverse order.

maybe it's me but the performance of the above strategy is going to be a lot worse than ORDER BY

what the heck is wrong with using ORDER BY anyway?

rudy|||RE: quote:
------------------------
Insert the result set into a work table with an additional autoincrementing identity column. Then alter the work table to make the additional identity column clustered and descending. Finally, a select from the work table of all columns (except the additional identity column) should return the very same set of records, in the reverse order.
------------------------
maybe it's me but the performance of the above strategy is going to be a lot worse than ORDER BY
what the heck is wrong with using ORDER BY anyway? rudy

Q1 [What the heck is wrong with using ORDER BY anyway?]
A1 The question being addressed excluded using ORDER BY [Given a set of arbitrary query results, (returned in no particular order): How would may one get that very same set of records returned in the reverse order without using ORDER BY?]

Q2 [Maybe it's me but the performance of the above strategy is going to be a lot worse than ORDER BY?]
A2 Not you, but rather the DBMS / query engine. I would expect avoiding using ORDER BY as a general practice to be a rather expensive endevour. (Perhaps the exclusion is related to some sort of performance testing?)|||Hello friends!

Thanks for answering me. Ur solutions are really useful.

Thanks & Regards
E.ElayaRaja

Query

Hi!

I need to create a query(stored procedure) that will select all rows from the second table and only the rows from table 1 where the maxValues for Col1 and Col2 do not exist in table 2 so that the result would look something like table 3

table 1

id	Col1	Col2	maxValues
1	aa	bb	10
2	cc	dd	10
3	ee	ff	10
4	gg	hh	10
5	jj	kk	10

table 2

id	Col1	Col2	CurrentValue
1	cc	dd	7
2	gg	hh	3
3	jj	kk	5

table 3

id	Col1	Col2	AvailableEntries
1	aa	bb	10
2	cc	dd	7
3	ee	ff	10
4	gg	hh	3
5	jj	kk	5

Thanks for your help.

If ALL values in table2 are also in table1, and maxValue always >= currentValue, then this should work:
select col1, col2, MIN(theValues)
from (
select col1, col2, maxValues as 'theValues'
from table1
UNION
select col1, col2, currentValue as 'theValues'
from table2
) as tempTable
group by col1, col2
|||It is perfect, thank you

query

SELECT rd.RequestID, rd.RequestDate, rd.EndRequestDate, rd.HoursSelected, rd.BeginTime, rd.EndTime, rd.Approved, rd.ApprovedBy, e.FirstName, e.LastName, e.DeptID FROM RequestData rd INNER JOIN Employee e ON e.EmployeeID = rd.EmployeeID WHERE (rd.RequestDate = @.ReqDate AND rd.EndRequestDate IS NOT NULL) OR (rd.RequestDate = @.ReqDate AND rd.EndRequestDate = @.EndReq)

How can I do something like the above query? I want to look for rd.RequestDate = @.ReqDate and rd.EndRequestDate = something(doesn't matter as long as it has a value). And in the same token I want to search for where they both equal some parameter.

In my app. a user can select a single date on a calendar which I would plug into the @.ReqDate in the first part of the WHERE clause or a week (where I take the first day of the selected week and the last day of the selected week and plug them in the @.ReqDate and @.EndReq in the second part of the WHERE clause.)

As I understand, you want to do is: if the 'rd.EndRequestDate' field is not null then search with only rd.RequestDate; else search with both date. Then a 'ISNULL' function can help you:

SELECT rd.RequestID, rd.RequestDate, rd.EndRequestDate, rd.HoursSelected, rd.BeginTime, rd.EndTime, rd.Approved, rd.ApprovedBy, e.FirstName, e.LastName, e.DeptID
FROM RequestData rd INNER JOIN Employee e
ON e.EmployeeID = rd.EmployeeID
WHERE rd.RequestDate = @.ReqDate
AND rd.EndRequestDate = ISNULL(EndRequestDate,@.EndReq)

|||

That works, but I also need to adjust my SP a little bit, I need it to do the following: The first where can do a rd.RequestDate = @.ReqDate where EndRequest isn't NULL. However, if both Dates are filled in then I need to do something like the following: rd.RequestDate >= @.ReqDate AND EndRequest <= @.EndReq. Any ideas on this?

|||

Got it-- just added an OR Clause to my SP--

SELECT rd.RequestID, rd.RequestDate, rd.EndRequestDate, rd.HoursSelected, rd.BeginTime, rd.EndTime, rd.Approved, rd.ApprovedBy, e.FirstName, e.LastName, e.DeptID
FROM RequestData rd INNER JOIN Employee e ON e.EmployeeID = rd.EmployeeID
WHERE ((rd.RequestDate = @.ReqDate) AND (rd.EndRequestDate = ISNULL(rd.EndRequestDate, @.EndReq))) OR
((rd.RequestDate >= @.ReqDate) AND (rd.EndRequestDate <= @.EndReq))

Query

Hi

I am trying to execute the below query in Query analyzer, the same query is executed in Sybase with the corresponding columns.

select (a.OnlineAccessId) + CASE
WHEN (a.onlineaccessid2) <> '00000000000000' THEN (a.onlineaccessid2)
else ''
End + CASE
WHEN (a.onlineaccessid3) <> '00000000000000' THEN (a.onlineaccessid3)
else ''
End as onlineaccessids, a.id as AcctInfoId, BankNo, RegionNo, OfficeNo, AcctNo,AcctShortNm ,SecLending, q.id as UsersId
from AcctInfo AS a INNER JOIN "104030" AS p ON a.AdminOfficer = p.CrmCode
inner join Users as q ON p.CrmDesc = q.OnlineaccessId union select
OnlineAccessIds = (a.OnlineAccessId) + CASE
WHEN (a.onlineaccessid2) <> '00000000000000' THEN (a.onlineaccessid2)
else ''
End + CASE
WHEN (a.onlineaccessid3) <> '00000000000000' THEN (a.onlineaccessid3)
else ''
End,q.id, a.Id, BankNo, RegionNo,
OfficeNo, AcctNo,AcctShortNm, SecLending
from AcctInfo as a
inner join users as q on
(q.onlineaccessid = substring(onlineaccessids, 1, 3))

The error message is

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'onlineaccessids'..

It is able to recognize the field "onlineaccessids" and giving me the results in Sybase database and giving the error in SQL Server.
Try to help me out.

Thanx in advanceFirst, double check that both the AcctInfo and users tables have an attribute "onlineaccessids".

Secondly, add an alias to:

(q.onlineaccessid = substring(a/q.onlineaccessids, 1, 3))

query

Hi,

select * from test a where test.col1 = a;

The above query will not work and it will throw the error 'The column prefix 'test' does not match with a table name or alias name used in the query'. i.e if alias defined for a table, then the columns can be qualified only using using the alias. Is it true ? Is it true in all the places like "Group By', 'Order' , 'SELECT list' etc. Is that true in all databases, is it ANSI standard ?

Please advice,

Thanks,
MiraJhi

from BOL
"If an alias is assigned to a table, all explicit references to the table in the Transact-SQL statement must use the alias, not the table name."

Query

I have attached the Adventureworks database to SQL Server 2005.

Whenerver I try to run a new query to this database

Ex: select * from sales.customer

I get this error message

Msg 208, Level 16, State 1, Line 1

Invalid object name 'sales.customer'.

I don't know why this is happining?

Hi,

make sure you are deling with the right database (Select db_name()).

Didi you change the default collation ? Seems that you are using a CS Coallation, that means that its case sensity, you can check that by using the query:

SELECT databasepropertyex('Adventureworks','collation')

Select * from Sales.Customer

So I guess, if you *are* connected to the right database, you should be fine using the proper typing of the name (otherwise, if you are feeling not comfortable withthis solution, you could change the collation of the database, by using the statemetn ALTER DATABSE, look in the BOL for more information)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks a lot Jenssql