db time conversion: sql

Showing posts with label sql. Show all posts

Friday, March 30, 2012

Query advice

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

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

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

query active directory

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

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

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

try:

Code Snippet

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

where name = suser_sname()

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

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

e.g.

Code Snippet

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

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

Code Snippet

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

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

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

CN = Common Name

DC = Domain Content

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

|||

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

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

You need to fully qualify your member group.

e.g.

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

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

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

|||

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

here is the adspath

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

the adspath below is what i got from the below code

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

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

Try:

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

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

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

Alright, here is the _hard_ way.

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

2. Right click -> select Properties.

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

4. Use that exact value for your MemberOf.

e.g.

Code Snippet

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

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

|||

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare: Stick out tongue 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'')

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.

|||

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

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.

|||

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

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.

|||

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

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.

|||

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

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.

|||

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

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.

|||

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

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.

|||

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

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.

|||

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

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.

|||

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

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.

|||

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

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.

|||

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

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.

|||

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 servers

I'm new to SQL and I'm trying to write a stored procdeure in my current database that queries a different database on a different server. What is the best way to do this?
Thanks and sorry if it's too remedial a question.you will need to set up a linked server. there is a great product documentation tool called Books Online under Start\Programs\Micorsoft SQL Server. Use the search feature for "linked server". You can then reference your the linked server using openquery or you can create a view that created with openquery. again see books online about openquery and views.|||You can use OpenDataSource. Check that from BOL.

SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories

Query Across Servers

Thanks! I'll try again.

quote:

>--Original Message--
>Yes it is possible. You will need to fully qualify your
>select path. For example
>SELECT A.TESTDATA1, B.TESTDATA2
> FROM <Server1>.<Database>.dbo.<Table> A,
> <Server2>.<Database>.dbo.<Table> B
>WHERE A.TESTDATA1 = B.TESTDATA2
>As long as the User account you are using has access to
>both database (Trusted Connection) it should work fine.
>
data[QUOTE]
>.
>

you can indeed do joins across tables between sqlservers. However watch out
for performance. If you want it done more flexiibly & faster or want to do
updates, check out my company.
Regards
David Penney - MetaMatrix
http://www.metamatrix.com
"J" <anonymous@.discussions.microsoft.com> wrote in message
news:8de901c3ea6d$a19c8470$a101280a@.phx.gbl...
> Thanks! I'll try again.
>
> datasql

Query Across Servers

Hi,
Is it possible to execute a query asking for joined data
from different tables on two different servers and
databases?
SQLServer1 SQLServer2
-- --
09.0.0.30\TestData 198.160.30.20\TestData2
I need to run a query against tables in the TestData &
TestData2 databases located at the I.P. locations
simulated above. Joins are necessary.
Thanks in advance.You can use linked servers and then fully qualify database objects, for
example:
SELECT * FROM SQLServer1.database.dbo.tablename -- from server2
You can find more info about linked servers in books online...
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"DBa" <anonymous@.discussions.microsoft.com> wrote in message
news:639101c3e5cb$e41662e0$a601280a@.phx.gbl...

quote:

> Hi,
> Is it possible to execute a query asking for joined data
> from different tables on two different servers and
> databases?
> SQLServer1 SQLServer2
> -- --
> 09.0.0.30\TestData 198.160.30.20\TestData2
> I need to run a query against tables in the TestData &
> TestData2 databases located at the I.P. locations
> simulated above. Joins are necessary.
> Thanks in advance.

|||alternate solution is to use OPENROWSET if you dont have permissions to
create linked server
Ex:
SELECT a.*
FROM OPENROWSET('SQLOLEDB','server1';'user1';
'password1',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
FROM OPENROWSET('SQLOLEDB','server2';'user1';
'password1',
'SELECT * FROM pubs.dbo.titleauthor') AS b
where a.au_id = b.au_id
GO
SQL Booksonline has more samples on OPENROWSET
Sethu Srinivasan
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:O2EjQ6h5DHA.2496@.TK2MSFTNGP09.phx.gbl...

quote:

> You can use linked servers and then fully qualify database objects, for
> example:
> SELECT * FROM SQLServer1.database.dbo.tablename -- from server2
> You can find more info about linked servers in books online...
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 programming by Example
>
> "DBa" <anonymous@.discussions.microsoft.com> wrote in message
> news:639101c3e5cb$e41662e0$a601280a@.phx.gbl...
>

Query Across Multiple SQL Server Registrations

I have defined 2 SQL Server registrations in Ent Mgr. One is running locally and the other is located on another server. I want to be able to create a stored procedure in the local database that pulls data into a local table from the remote server.

I have wasted much time trying to define Linked Servers and Remote servers, and find the documentation all confusing and subsequently I have gotten nowhere (except frustrated). How do I configure the remote database so I can access it from a stored procedure in the local database?

Here is the code for creating the linked server. Run script against your local database. Hope you have access permissions on remote db. Also replace the IP address 999.999.999.999 with your remote system's IP address. You can query the tables on remote db with four part name.

Code Snippet

USE [master]
EXEC master.dbo.sp_addlinkedserver @.server = N'Lnk_RemoteDB', @.srvproduct=N'sqlserver', @.provider=N'SQLOLEDB',
@.datasrc = '999.999.999.999', -- IP Address
@.catalog=N'RemoteDB'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'rpc', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'Lnk_RemoteDB', @.locallogin = NULL , @.useself = N'True'

|||

I ran the script and it created Lnk_RemoteDB. I want to access a table called Agents from a database named AgentDB, what's the correct syntax?

Thanks

|||

Sniegel wrote:

I ran the script and it created Lnk_RemoteDB. I want to access a table called Agents from a database named AgentDB, what's the correct syntax?
Thanks

select * from Lnk_RemoteDB.AgentDB.dbo.Agents|||I get an Authentication failed error. I tried switching the mode through EM and entering a username/password, but it didn't seem to work.

Query across multiple db's

I have two databases on one server. I want do a query across the databases. I
was moving the data and out of 370K records, 24 did not come across. How do I
query to find out which records did not make it across.
Hi,
select * from DB1..tablename where id not in(select id from DB2..tablename)
Thanks
Hari
MCDBA
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:12D870F3-4DE5-44CA-89BC-9BD387517EFC@.microsoft.com...
> I have two databases on one server. I want do a query across the
databases. I
> was moving the data and out of 370K records, 24 did not come across. How
do I
> query to find out which records did not make it across.
|||Did not work. I think because the PK is across 5 fields
"Hari Prasad" wrote:

> Hi,
> select * from DB1..tablename where id not in(select id from DB2..tablename)
>
> Thanks
> Hari
> MCDBA
>
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:12D870F3-4DE5-44CA-89BC-9BD387517EFC@.microsoft.com...
> databases. I
> do I
>
>
|||One way to finese this problem is:
SELECT * FROM DB1..tablename
WHERE CONVERT(CHAR(10), IDCol1) +
CONVERT(CHAR(10), IDCol2) +
CONVERT(CHAR(10), IDCol3) +
CONVERT(CHAR(10), IDCol4) +
CONVERT(CHAR(10), IDCol5)
NOT IN
(SELECT
CONVERT(CHAR(10), IDCol1) +
CONVERT(CHAR(10), IDCol2) +
CONVERT(CHAR(10), IDCol3) +
CONVERT(CHAR(10), IDCol4) +
CONVERT(CHAR(10), IDCol5)
FROM DB2..tablename)
That assumes that CHAR(10) will hold the conversion of your identifying
columns. Adjust appropriately.
Russell Fields
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:8655C387-155D-4AE6-BFAF-7DB3DFD50593@.microsoft.com...[vbcol=seagreen]
> Did not work. I think because the PK is across 5 fields
> "Hari Prasad" wrote:
DB2..tablename)[vbcol=seagreen]
How[vbcol=seagreen]
|||Or you could use NOT EXISTS, which in my opinion reads a bit better:
SELECT *
FROM DB1..tablename T1
WHERE NOT EXISTS
(SELECT *
FROM DB2..tablename T2
WHERE T2.IDCol1 = T1.IDCol1
AND T2.IDCol2 = T2.IDCol2
AND T2.IDCol3 = T2.IDCol3
AND T2.IDCol4 = T2.IDCol4
AND T2.IDCol5 = T2.IDCol5)
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23hli%23S7hEHA.3536@.TK2MSFTNGP12.phx.gbl...
> One way to finese this problem is:
> SELECT * FROM DB1..tablename
> WHERE CONVERT(CHAR(10), IDCol1) +
> CONVERT(CHAR(10), IDCol2) +
> CONVERT(CHAR(10), IDCol3) +
> CONVERT(CHAR(10), IDCol4) +
> CONVERT(CHAR(10), IDCol5)
> NOT IN
> (SELECT
> CONVERT(CHAR(10), IDCol1) +
> CONVERT(CHAR(10), IDCol2) +
> CONVERT(CHAR(10), IDCol3) +
> CONVERT(CHAR(10), IDCol4) +
> CONVERT(CHAR(10), IDCol5)
> FROM DB2..tablename)
> That assumes that CHAR(10) will hold the conversion of your identifying
> columns. Adjust appropriately.
> Russell Fields

Query across multiple db's

I have two databases on one server. I want do a query across the databases.
I
was moving the data and out of 370K records, 24 did not come across. How do
I
query to find out which records did not make it across.Hi,
select * from DB1..tablename where id not in(select id from DB2..tablename)
Thanks
Hari
MCDBA
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:12D870F3-4DE5-44CA-89BC-9BD387517EFC@.microsoft.com...
> I have two databases on one server. I want do a query across the
databases. I
> was moving the data and out of 370K records, 24 did not come across. How
do I
> query to find out which records did not make it across.|||Did not work. I think because the PK is across 5 fields
"Hari Prasad" wrote:

> Hi,
> select * from DB1..tablename where id not in(select id from DB2..tablename
)
>
> Thanks
> Hari
> MCDBA
>
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:12D870F3-4DE5-44CA-89BC-9BD387517EFC@.microsoft.com...
> databases. I
> do I
>
>|||One way to finese this problem is:
SELECT * FROM DB1..tablename
WHERE CONVERT(CHAR(10), IDCol1) +
CONVERT(CHAR(10), IDCol2) +
CONVERT(CHAR(10), IDCol3) +
CONVERT(CHAR(10), IDCol4) +
CONVERT(CHAR(10), IDCol5)
NOT IN
(SELECT
CONVERT(CHAR(10), IDCol1) +
CONVERT(CHAR(10), IDCol2) +
CONVERT(CHAR(10), IDCol3) +
CONVERT(CHAR(10), IDCol4) +
CONVERT(CHAR(10), IDCol5)
FROM DB2..tablename)
That assumes that CHAR(10) will hold the conversion of your identifying
columns. Adjust appropriately.
Russell Fields
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:8655C387-155D-4AE6-BFAF-7DB3DFD50593@.microsoft.com...[vbcol=seagreen]
> Did not work. I think because the PK is across 5 fields
> "Hari Prasad" wrote:
>
DB2..tablename)[vbcol=seagreen]
How[vbcol=seagreen]|||Or you could use NOT EXISTS, which in my opinion reads a bit better:
SELECT *
FROM DB1..tablename T1
WHERE NOT EXISTS
(SELECT *
FROM DB2..tablename T2
WHERE T2.IDCol1 = T1.IDCol1
AND T2.IDCol2 = T2.IDCol2
AND T2.IDCol3 = T2.IDCol3
AND T2.IDCol4 = T2.IDCol4
AND T2.IDCol5 = T2.IDCol5)
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23hli%23S7hEHA.3536@.TK2MSFTNGP12.phx.gbl...
> One way to finese this problem is:
> SELECT * FROM DB1..tablename
> WHERE CONVERT(CHAR(10), IDCol1) +
> CONVERT(CHAR(10), IDCol2) +
> CONVERT(CHAR(10), IDCol3) +
> CONVERT(CHAR(10), IDCol4) +
> CONVERT(CHAR(10), IDCol5)
> NOT IN
> (SELECT
> CONVERT(CHAR(10), IDCol1) +
> CONVERT(CHAR(10), IDCol2) +
> CONVERT(CHAR(10), IDCol3) +
> CONVERT(CHAR(10), IDCol4) +
> CONVERT(CHAR(10), IDCol5)
> FROM DB2..tablename)
> That assumes that CHAR(10) will hold the conversion of your identifying
> columns. Adjust appropriately.
> Russell Fields

Query across multiple databases/SQL Servers

Is it possible to run a single query that pulls data from multiple
tables in multiple databases, perhaps also from multiple SQL Servers?
Any input would be appreciated.
Thanks!
RichardF
Yes. Specify the server and database:
select Select_Column_List
from Server_Name.DB_Name.Object_owner.Table_or_view_nam e
If from another server, you need to set up a linked server. See Linked
Server in Books Online.
"RichardF" <no.one@.no.where.com> wrote in message
news:41acf571.7680223@.msnews.microsoft.com...
> Is it possible to run a single query that pulls data from multiple
> tables in multiple databases, perhaps also from multiple SQL Servers?
> Any input would be appreciated.
> Thanks!
> RichardF
sql

Query across multiple databases/SQL Servers

Is it possible to run a single query that pulls data from multiple
tables in multiple databases, perhaps also from multiple SQL Servers?
Any input would be appreciated.
Thanks!
RichardFYes. Specify the server and database:
select Select_Column_List
from Server_Name.DB_Name.Object_owner.Table_or_view_name
If from another server, you need to set up a linked server. See Linked
Server in Books Online.
"RichardF" <no.one@.no.where.com> wrote in message
news:41acf571.7680223@.msnews.microsoft.com...
> Is it possible to run a single query that pulls data from multiple
> tables in multiple databases, perhaps also from multiple SQL Servers?
> Any input would be appreciated.
> Thanks!
> RichardF

Query across multiple databases/SQL Servers

Is it possible to run a single query that pulls data from multiple
tables in multiple databases, perhaps also from multiple SQL Servers?
Any input would be appreciated.
Thanks!
RichardFYes. Specify the server and database:
select Select_Column_List
from Server_Name.DB_Name.Object_owner.Table_or_view_name
If from another server, you need to set up a linked server. See Linked
Server in Books Online.
"RichardF" <no.one@.no.where.com> wrote in message
news:41acf571.7680223@.msnews.microsoft.com...
> Is it possible to run a single query that pulls data from multiple
> tables in multiple databases, perhaps also from multiple SQL Servers?
> Any input would be appreciated.
> Thanks!
> RichardF

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 accross servers

Hi,
Please could someone provide me with a best example of how to query records
from different tables in different databases, where the databases are
located on different servers on the same network.
Your assistance is much appreciated.If you want to use different servers you have to build up some linked
servers (look in the BOL). There you can go with the four-point name to
query them.
Select * from [Servername].[Databasename].[owner].[Objectname]
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"11Oppidan" <11Oppidan@.community.nospam> schrieb im Newsbeitrag
news:us3gSr6WFHA.3092@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Please could someone provide me with a best example of how to query
> records from different tables in different databases, where the databases
> are located on different servers on the same network.
> Your assistance is much appreciated.
>|||You may also want to check out OPENQUERY in BOL. Sometimes it yields
better performance than four-part names.
Here's an excerpt from BOL:
This example creates a linked server named OracleSvr against an Oracle
database using the Microsoft OLE DB Provider for Oracle. Then this
example uses a pass-through query against this linked server.
Note This example assumes that an Oracle database alias called ORCLDB
has been created.
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
HTH...
--
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Wed, 18 May 2005 15:13:15 +0200, "Jens Smeyer"
<Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:

>If you want to use different servers you have to build up some linked
>servers (look in the BOL). There you can go with the four-point name to
>query them.
>Select * from [Servername].[Databasename].[owner].[Objectname]|||Thanks guys!
"Joe Webb" <joew@.webbtechsolutions.com> wrote in message
news:67gm815emd4m6g2vb7dr70tsn01vsciaon@.
4ax.com...
> You may also want to check out OPENQUERY in BOL. Sometimes it yields
> better performance than four-part names.
> Here's an excerpt from BOL:
> This example creates a linked server named OracleSvr against an Oracle
> database using the Microsoft OLE DB Provider for Oracle. Then this
> example uses a pass-through query against this linked server.
> Note This example assumes that an Oracle database alias called ORCLDB
> has been created.
>
> EXEC sp_addlinkedserver 'OracleSvr',
> 'Oracle 7.3',
> 'MSDAORA',
> 'ORCLDB'
> GO
> SELECT *
> FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
> GO
>
> HTH...
> --
> Joe Webb
> SQL Server MVP
>
> ~~~
> Get up to speed quickly with SQLNS
> http://www.amazon.com/exec/obidos/t...il/-/0972688811
> I support PASS, the Professional Association for SQL Server.
> (www.sqlpass.org)
>
> On Wed, 18 May 2005 15:13:15 +0200, "Jens Smeyer"
> <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:
>
>

Friday, March 30, 2012

Query advice

query active directory

query active directory

query active directory

query active directory

query active directory

query active directory

query active directory

Query acting strangely

Query across servers

Query Across Servers

Query Across Servers

Query Across Multiple SQL Server Registrations

Query across multiple db's

Query across multiple db's

Query across multiple databases/SQL Servers

Query across multiple databases/SQL Servers

Query across multiple databases/SQL Servers

Query across all colums

Query accross servers

db time conversion

Blog Archive

About Me