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 : Running a job/step in a loop for ALL databases

Hi,

I've written a job to export user and database permissions for all
d/b's on a server. As you can see below, the T-SQL commands are the
same for each d/b. Can anyone assist with regard to re-writing this so
that any new d/b's added do not require ammending the job (loop)?

Thx,

GC.

use master
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'
use msdb
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'
use test1
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'
use test2
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'"Garry Clarke" <gclarke@.euro.banta.com> wrote in message
news:fed38413.0310240324.77f4ce60@.posting.google.c om...
> Hi,
> I've written a job to export user and database permissions for all
> d/b's on a server. As you can see below, the T-SQL commands are the
> same for each d/b. Can anyone assist with regard to re-writing this so
> that any new d/b's added do not require ammending the job (loop)?
> Thx,
> GC.
> use master
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'
> use msdb
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'
> use test1
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'
> use test2
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'

A cursor is one way to do this (cursors are usually a bad idea in
application code, but can be useful for admin scripts):

declare @.db sysname

declare cur_dbs cursor fast_forward
for select name from master..sysdatabases
order by name

open cur_dbs

fetch next from cur_dbs into @.db

while @.@.fetch_status = 0
begin
select @.db
exec('exec ' + @.db + '..sp_helpuser')
exec('exec ' +@.db + '..sp_helprotect NULL, NULL, NULL, ''os''')
fetch next from cur_dbs into @.db
end

close cur_dbs
deallocate cur_dbs

Simon

query - specify multiple items

Hi,
I am trying to develop a report where the user is able to specify the
information to be pulled from one specific field.
For example... the table has 2 fields... Company ID, Company Name
I only want to see information where Company ID = ABC and XYZ
but I want the user to be able to specify multiple Company ID's in the
report parameter
Thanks,
JenI've got part of the query figured out:
SELECT Company ID, Company Name
FROM Company Profile
WHERE (Company ID (@.CompanyID))
But this only lets me query one company id at a time. how can I alter the
query to allow users to query multiple company id's?
thanks
jen
"Jennifer Mar" <jmar@.cityharvest.org> wrote in message
news:O6rTcrxZHHA.1300@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am trying to develop a report where the user is able to specify the
> information to be pulled from one specific field.
> For example... the table has 2 fields... Company ID, Company Name
> I only want to see information where Company ID = ABC and XYZ
> but I want the user to be able to specify multiple Company ID's in the
> report parameter
> Thanks,
> Jen
>|||Hello Jennifer,
If you are using the SQL Server 2005, you could use the Multi-value
Parameter.
When you enable the Multi-value for the parameter, you could get a dropdown
list in the report.
Then you need to use the IN statement to query the data.
You could refer this article:
Adding a Multivalue Parameter
http://msdn2.microsoft.com/en-us/aa337396.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||We're currently running sql server 2000. i am using visual studio .net to
build the reports.
The way the query is build now, it allows the user to enter the parameters
in a seperate text box for each company id they query. how can i build the
query so that they can just enter a string of unlimited company id's?
thanks!
jen
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:OO42Wm4ZHHA.1784@.TK2MSFTNGHUB02.phx.gbl...
> Hello Jennifer,
> If you are using the SQL Server 2005, you could use the Multi-value
> Parameter.
> When you enable the Multi-value for the parameter, you could get a
dropdown
> list in the report.
> Then you need to use the IN statement to query the data.
> You could refer this article:
> Adding a Multivalue Parameter
> http://msdn2.microsoft.com/en-us/aa337396.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
>
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Jen,
You could use the parameter as the String and use the comma mark to
seperate each company id.
And in the query, you could use the parameter like this:
Select * from tbl_companies where companyid in (@.Parameters)
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||hi wei,
thanks so much for checking in. everything worked out great :)
jen
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:5JytuQ7aHHA.1028@.TK2MSFTNGHUB02.phx.gbl...
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||Hello Jen,
My pleasure!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Query - pop-up menu for user data entry

Using the following syntax:
Select fname, lastn
From list
Where fname like 'jones'
I need the syntax that the user can enter a name to perform a query (the
user will enter a name on a pop-up menu before the query is performed).Roy,
You didn't say in what context you would be using this, so the below-code
assumes you are using a Stored Procedure:
CREATE STORED PROCEDURE [dbo].[usp_FindName]
@.LName varchar(100)=''
AS
IF @.LName<>''
BEGIN
Select fname, lastn
From list
Where fname like @.LName
END
GO
Hope thi assists,
Tony
"ROY A. DAY" wrote:

> Using the following syntax:
> Select fname, lastn
> From list
> Where fname like 'jones'
> I need the syntax that the user can enter a name to perform a query (the
> user will enter a name on a pop-up menu before the query is performed).

query

Hi
I have a following problem;
Lets say i have table with names and value in this table "hkan"
Now user enters text "hakan" for search and query must return alsow
"hkan".
Is in SQL any "special" function for this ?
one option is create own function, but there is toooo many different options
a-, o- aso aso.
Regards;
Meelis
Meelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI =
@.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "hkan"
> Now user enters text "hakan" for search and query must return alsow
> "hkan".
> Is in SQL any "special" function for this ?
> one option is create own function, but there is toooo many different
> options a-, o- aso aso.
>
> Regards;
> Meelis
>

query

hi ...
how do i use a field say user_id in my USER table in another table say PRODUCT?

Quote:

Originally Posted by priyankanair

hi ...
how do i use a field say user_id in my USER table in another table say PRODUCT?

At this point it's a bit unclear what you want to do.

If you want to have the same field in both tables you have to define them separately as such when defining the tables. If you already have them defined in both tables and want to use data from both tables at the same time, you have to join the tables. If you want to insert data to a new table, you need to use the proper INSERT statement.

Please provide a bit more info on the context and your goal so that we can help.|||

Quote:

Originally Posted by priyankanair

hi ...
how do i use a field say user_id in my USER table in another table say PRODUCT?

Could you please tell us, what you are trying to do.

query

Hi
I have a following problem;
Lets say i have table with names and value in this table "håkan"
Now user enters text "hakan" for search and query must return alsow
"håkan".
Is in SQL any "special" function for this :)?
one option is create own function, but there is toooo many different options
a-ä, o-ö aso aso.
Regards;
MeelisMeelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI = @.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "håkan"
> Now user enters text "hakan" for search and query must return alsow
> "håkan".
> Is in SQL any "special" function for this :)?
> one option is create own function, but there is toooo many different
> options a-ä, o-ö aso aso.
>
> Regards;
> Meelis
>

query

Hi
I have a following problem;
Lets say i have table with names and value in this table "hkan"
Now user enters text "hakan" for search and query must return alsow
"hkan".
Is in SQL any "special" function for this ?
one option is create own function, but there is toooo many different options
a-, o- aso aso.
Regards;
MeelisMeelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI =
@.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "hkan"
> Now user enters text "hakan" for search and query must return alsow
> "hkan".
> Is in SQL any "special" function for this ?
> one option is create own function, but there is toooo many different
> options a-, o- aso aso.
>
> Regards;
> Meelis
>

Queried default values

Hello!
Iâ'm using reporting services in a web application. I have made my own user
interface (asp.net / c#) it works rather well except handling queried default
values.
Iâ'm using Stored Procedures to get the default values (non-queried default
values are no problem). The available values are selected the same way
without problems. The SP to get the default and valid values are using one of
the report parameters as a parameter and it works fine using the report
manager.
ReportParams = rs.GetReportParameters(ReportPath,null,true,ParamValues,null);
Above returns NULL or empty default values.
Can anyone help me how to handle queried default values?
If anyone is familiar with my problem, please reply with a solution.
Best Regards,
daniel_bI've done this in vb.net, and it works without problems. Are you sure
you are using the web service correctly?
I set the ParamValues parameter to be Nothing (null) if I have no known
parameter values yet, or to be an array of ParameterValues (with
prompt, name and value set) if I already know the value of some of the
parameters. I sometimes know the value of some parameters when I am
dealing with dependant parameters where the list of values for one
parameter is not known until another parameter has its value set.
ParameterList = service.GetReportParameters(ReportName, Nothing,
True, values, Nothing)
Does this help?
Kulgan.

Quaterly Data selection problem

Hi all,

I have one table with oper_year and oper_month , those two are int.
Now i want to take the quaterly data from the table as per the user selection like this
If user selects...200001 to 200212 means in that we have 8 quaters...4 in 2001 and 4 in 2002 and if user selects the range from 200101 to 200209 it has 7 quaters..means in 2001..4 Quaters.. and in 2002 --3 Quaters...
i have the following query it is working but if i give the 200101 to 200209 it is shows only 6 quaters...what is the wrong in that if possible please correct it....

SELECT CASE WHEN (oper_month) IN (1,2,3)THEN 'Q1-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (4,5,6)THEN 'Q2-' + Cast(oper_year as varchar)
WHEN(oper_month) IN (7,8,9)THEN 'Q3-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (10,11,12)THEN 'Q4-'+ Cast(oper_year as varchar)END
,sum(amount)
FROM oper_sundata
where oper_year between 2001 and 2002
and oper_month between 01 and 09
group by oper_year,
CASE WHEN (oper_month) IN (1,2,3)THEN 'Q1-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (4,5,6)THEN 'Q2-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (7,8,9)THEN 'Q3-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (10,11,12)THEN 'Q4-' + Cast(oper_year as varchar)End
order by oper_year,
CASE WHEN (oper_month) IN (1,2,3)THEN 'Q1-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (4,5,6)THEN 'Q2-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (7,8,9)THEN 'Q3-' + Cast(oper_year as varchar)
WHEN (oper_month) IN (10,11,12)THEN 'Q4-' + Cast(oper_year as varchar)End

thanksyou have asked this quarterly question three times now

received answers twice but have not replied whether the answers you already received were suitable

here is another answer

please, please let us know if this is suitable, don't just ignore us, or your future questions will go unanswered

this is my original answer, modified by ionut (http://dbforums.com/member.php?action=getinfo&userid=26346), then modified by me to add date range testing
select 'Q' + cast(cast( (month(period)+2) / 3 as integer ) as char(1))
+ '-' + year(period) as QuarterYear
, sum(amount) as SumAmount
from oper_sundata
where year(period)*12+month
between cast(@.startyyyymm as integer)
and cast(@.endyyyymm as integer)
group
by cast( (month(period)+2) / 3 as integer )
, year(period)
order
by cast( (month(period)+2) / 3 as integer )
, year(period)

rudy
http://r937.com/

Quaterly Data From Selected Range

Hi all,

I have one table with period data time and composite integer
now i want to select the sum(composite) for quaterly like
if user selects 200001 to 200303 i want all the quater which fall under the selection/
in this we have q1.q2.q3.q4 of 2000 and
q1.q2.q3.q4 of 2001 and q1.q2.q3.q4 of 2002 and q1 2003...i want all those quaters..each one row...
so how to do that let me know the query ...did you try this (http://dbforums.com/showthread.php?threadid=751460) ?

rudy|||Like R937 said (with one corection of mine), try this:

select year(period) as year,cast( (month(period)+2) / 3 as integer ) as quarter, sum(composite)
from cdh_price_gap
group by year(period),cast( (month(period)+2) / 3 as integer )

or

select year(period) as year,cast( ((month(period)-0.3)/ 3)+1 as integer ) as quarter, sum(composite)
from cdh_price_gap
group by year(period),cast( ((month(period)-0.3)/ 3)+1 as integer )

IONUT CALIN

PS
From what I understant you need you need separate sums for q1 2002 and q2 2003 (in this case you alo have to group by year of date field)

Good Luck!

Qualified table names

I have a quick question on how to qualify table names as it relates to
"dbo" vs. user names. Suppose that I am a user named "dwuser1", and
that I need to create a table named "dw_stage_1". Do I use dbo as in
"dbo.dw_stage_1" or do I use "dwuser1.dw_stage_1" for the qualified
table name? Are both OK? If so, what would be the implications of
each?php newbie (newtophp2000@.yahoo.com) writes:
> I have a quick question on how to qualify table names as it relates to
> "dbo" vs. user names. Suppose that I am a user named "dwuser1", and
> that I need to create a table named "dw_stage_1". Do I use dbo as in
> "dbo.dw_stage_1" or do I use "dwuser1.dw_stage_1" for the qualified
> table name? Are both OK? If so, what would be the implications of
> each?

If you are user dwuser1, and you have CREATE TABLE permissions, and
you say:

CREATE TABLE dw_stage_1 (a int NOT NULL)

The full qualification for that table will be dwuser1.dw_stage_1. You
cannot refer to the table as dbo.dw_stage_1. As dwuser1 you can refer
to the table as dw_stage_1 and dwuser1.dw_stage_1. All other users,
including dbo, must refer to ut as dwuser1.dw_stage_1.

If you then log in as sa or any other login that map to dbo and say

CREATE TABLE dw_stage_1 (a int NOT NULL)

again, you have now created to dw_stage_1. As dbo you can refer to the
table as dbo.dw_stage_1 or dw_stage_1 only, and so can all other users
except for dwuser1, who must use dbo.dw_stage_1, since his only table
is ahead in the seatch path.

Best practice recommended my Microsoft is to refer to the table as
dbo.dw_stage_1. This is particularly important for loose SQL statements,
since SQL Server then can skip the search for user.dw_stage_1. They
say that this is also good in stored procedure, but in my opinion,
the dbo. becomes a four-letter line noice in a procedure that is owned
by dbo.

As for when to use objects not owned by dbo - beats me. I say, keep it
simple and only use dbo.

(In SQL 2005 where users and schema are separated, it's another story.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

QA simple question

Hi
I'm a new MS SQL user. When I'm using QA it shows erros in such a way:
"Server: Msg 156, Level 15, State 1, Procedure XX_IMPORT, Line 45
Incorrect syntax near the keyword 'select'."
I wonder how to force it to show correct line. In the example above I
checked line 45 and there is no "select" statement.
I suspect that it ignores lines with comments and blank lines. So debugging
where the error occurs is very difficult.
Best Regards
(and sorry for the simplicity of the question)
Darek T.Dariusz Tomon,
Go to the messages window and double click over the error message. QA will
take you directly to the line where the error is.
AMB
"Dariusz Tomon" wrote:
> Hi
> I'm a new MS SQL user. When I'm using QA it shows erros in such a way:
> "Server: Msg 156, Level 15, State 1, Procedure XX_IMPORT, Line 45
> Incorrect syntax near the keyword 'select'."
> I wonder how to force it to show correct line. In the example above I
> checked line 45 and there is no "select" statement.
> I suspect that it ignores lines with comments and blank lines. So debugging
> where the error occurs is very difficult.
> Best Regards
> (and sorry for the simplicity of the question)
> Darek T.
>
>|||Hi Darek
You can double-click on the error message, and QA will take you to the line
in the Query Window where the error is.
--
HTH
Kalen Delaney, SQL Server MVP
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:esH%23a97jGHA.4884@.TK2MSFTNGP03.phx.gbl...
> Hi
> I'm a new MS SQL user. When I'm using QA it shows erros in such a way:
> "Server: Msg 156, Level 15, State 1, Procedure XX_IMPORT, Line 45
> Incorrect syntax near the keyword 'select'."
> I wonder how to force it to show correct line. In the example above I
> checked line 45 and there is no "select" statement.
> I suspect that it ignores lines with comments and blank lines. So
> debugging where the error occurs is very difficult.
> Best Regards
> (and sorry for the simplicity of the question)
> Darek T.
>
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:95A3E7C1-AAAA-46B6-BEAC-74AE111023E4@.microsoft.com...
> Dariusz Tomon,
> Go to the messages window and double click over the error message. QA will
> take you directly to the line where the error is.
>
> AMB
Thank you - I did not know that it's so simple :)
>
> "Dariusz Tomon" wrote:
>> Hi
>> I'm a new MS SQL user. When I'm using QA it shows erros in such a way:
>> "Server: Msg 156, Level 15, State 1, Procedure XX_IMPORT, Line 45
>> Incorrect syntax near the keyword 'select'."
>> I wonder how to force it to show correct line. In the example above I
>> checked line 45 and there is no "select" statement.
>> I suspect that it ignores lines with comments and blank lines. So
>> debugging
>> where the error occurs is very difficult.
>> Best Regards
>> (and sorry for the simplicity of the question)
>> Darek T.
>>
>>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:umPIQL8jGHA.1640@.TK2MSFTNGP02.phx.gbl...
> Hi Darek
> You can double-click on the error message, and QA will take you to the
line
> in the Query Window where the error is.
Dang, in all these years, I never realized that.
Thanks.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
> news:esH%23a97jGHA.4884@.TK2MSFTNGP03.phx.gbl...
> > Hi
> >
> > I'm a new MS SQL user. When I'm using QA it shows erros in such a way:
> >
> > "Server: Msg 156, Level 15, State 1, Procedure XX_IMPORT, Line 45
> > Incorrect syntax near the keyword 'select'."
> >
> > I wonder how to force it to show correct line. In the example above I
> > checked line 45 and there is no "select" statement.
> > I suspect that it ignores lines with comments and blank lines. So
> > debugging where the error occurs is very difficult.
> >
> > Best Regards
> > (and sorry for the simplicity of the question)
> >
> > Darek T.
> >
> >
> >
>

Q3: Trusted SQL Logins

I can not connect to my Local SQL Server 2005 in C# code,

it throws the error,

The user is not associated with a trusted SQL Server connection.

i tried to change the mode to Mixed Mode, but i don't see it, I see only Windows and SQL Server Auth mode,

i tried to change to SQL Server Mode, but none of user log will work, it says, this login is not in trusted connection,

so i would make any login associated with trusted account.
Thanks,

I have covered how to choose both Windows and SQL authentication in SQL Server in the thread below but if you are getting this error in a web application then the problem is not SQL Server but IIS. In IIS Deny anonymous users and in your Web.Config enable impersonation. Hope this helps.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1894423&SiteID=1

|||

"Mixed mode" = "SQL Server and Windows authentication mode"

Maybe you are trying to login using a SQL Login when your authentication is Windows only? Perhaps you need to change your connections string:

Windows Auth

integrated secruity=true

SQL Auth

integrated security=false; uid=user;pwd=pword

HTH!

|||

Thanks for your reply, but i followed the steps, when I change it to SQLServer Authentication mode, it ask for log info, I put SA account, but it gives this error,

SA is not associated with Trusted Account.

Also I don't see the Security page as well as Mixed Mode option when i go to Server properties.
(On the Security page, under Server authentication, select the new server authentication mode, and then click OK)

Any idea,

Thanks,|||

Where are you trying to change the authentication? Open SSMS, right click your servername in Object Explorer, click properties. Down the left hand side you should have a list of pages including the security one where you need to make these changes.

Is this not what you see? Are you using Management Studio? What version of SQL Server is it?

|||You are not in the correct place because you have only two options Window or Both SQL Server and Windows is the only other option so right click on the name of your server in object explorer and go to properties then security. But you still need to enable the System Admin account because during installation if you choose Windows authentication SQL Server disables the SA account. Hope this helps.|||Thanks,

i found it and changed it but still i get an error when I try to connect to Database Engine using that sa account that i enabled it,

1) A connection was succesfully established with the server, but then an error occured during the login process. (provider: shared memory Provider, error: 0 - No process is on the other end of the pipe)(MSSql Server , Error:233)

2) from asp.net code, i get error like , sa is not in trusted connection list.

any idea,

Thanks,|||

I think you'll need to get yourself back to the SQL Configuration Manager and perhaps enable named pipes? I did a quick search on google for: "No process is on the other end of the pipe" and there were a fair few resources. Have a look there and let us know how you get on.

Good luck!

|||

No you need to enable the SA account before you try to use it and error 233 is covered in the link below and you need to reboot the box after you make all these changes. I have also seen error 233 related to certificates not installed in SQL Server. Hope this helps.

http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx