db time conversion: 2012

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:
>
>

Query Access & MS SQL

Are syntax of queries same in MS Access and MS SQL 2005?
I am asking because I have lot of queries in Access database. Do I have to
change every query when I move to MS SQL 2005?
Thanks!!!Hi
No. Most will work the same, but certain Access specific implementations of
queries will not.
If a query does not work with SQL Server 2000, it won't in SQL Server 2005.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John" <john713@.hotmail.com> wrote in message
news:ddkllt$f2g$1@.ss405.t-com.hr...
> Are syntax of queries same in MS Access and MS SQL 2005?
> I am asking because I have lot of queries in Access database. Do I have to
> change every query when I move to MS SQL 2005?
> Thanks!!!
>|||Some basic queries might be portable between MS Access and SQL Server but if
you've used stuff like most of the VBA functions or some of the non-standard
SQL elements of Access then they'll need some work. If you are porting an
application to SQL Server then to get the most out of the platform you
should aim to convert your queries and other data-access code into SQL
stored procedures. That'll almost certainly involve a significant re-write
of your app. How far you need to go will depend partly on what you want to
gain from switching platforms.
Also, if your data model is of more than trivial complexity you should
certainly review it when upsizing. Many people use data models in Access
that are poorly normalized. These are OK in Access because Access often lets
you do non-relational stuff in the database to cope with problems like
missing keys or redundant data. In SQL Server you are more likely to have
serious problems with a weak data model. Of course, you may already be
totally confident that your data is strictly Third Normal Form, which will
give you a head start.
Hope this helps.
David Portas
SQL Server MVP
--|||Thanks David,
there are no VBA elements, because I am using VB 6.0 and Access as database,
but some queries are not in VB code, but in Access database, like cross tab
queries.|||John:
Cross-tab queries in Access have no equivalent in SQL Server. Also, as was
mentioned before by others in this thread, VB functions in Access won't work
in SQL.
You may want to check out the following: "Microsoft Access Developer's Guide
to SQL Server" by Mary Chipman and Andy Baron (ISBN 0-672-31944-6), availabl
e
at Amazon.com for under $10 used.
Going through an upgrade from MDB to ADP/SQL I learned to watch for the
"Now()" function in many Access queries, which were replaced with
"GETDATE()". You will need to see what functions are in use.
Good luck.
Toddsql

query about publisher or subscriber

What is the easy command (if any) to see if the server is a publisher or
a subscriber in a replication environment?
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Hi Darin,
In your Publication database...
select subscriber_type, subscriber_server
from sysmergesubscriptions
order by subscriber_type, subscriber_server
subscriber_type:
1 - Publisher
2 - Subscriber
|||Hi Darin,
In your Publication database...
select subscriber_type, subscriber_server
from sysmergesubscriptions
order by subscriber_type, subscriber_server
subscriber_type:
1 - Publisher
2 - Subscriber
|||issue an sp_helpdistpublisher or sp_helpsubscriberinfo
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Darin" <darin_nospam@.nospamever> wrote in message
news:eC92oo9BGHA.2704@.TK2MSFTNGP15.phx.gbl...
> What is the easy command (if any) to see if the server is a publisher or
> a subscriber in a replication environment?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***

Query about Pages & Extents - Newbie

HI,
Just got puzzled. Was going through SQL Arch. basics and
encountered about pages (8kb) and extents ( 8pg * 8kb).
somewhere it says *Transaction log space is not measured
in extents* . Could any one give details on how SQL pages
& extents concept is used differently on a database and
transaction log file?
Regards
ChipHi Chip
In the data files, the data is organized into tables and table pages are all
8KB. Space is allocated to a table in units of 8 pages (8 * 8 KB, or one
extent) at a time, so there is room for more rows to be added before more
allocation needs to be done. The new extents allocated to a table can can
from anywhere in the file, although SQL Server will try to find an extent
close by to existing extents for the table, wherever possible.
Log files are not organized into tables. Log records are just written to the
log file sequentially as transactions commit. No preallocation of space is
done for the log. It's just a record of the changes to your database.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:006f01c3d225$d0347eb0$a401280a@.phx.gbl...
> HI,
> Just got puzzled. Was going through SQL Arch. basics and
> encountered about pages (8kb) and extents ( 8pg * 8kb).
> somewhere it says *Transaction log space is not measured
> in extents* . Could any one give details on how SQL pages
> & extents concept is used differently on a database and
> transaction log file?
> Regards
> Chip|||Hi Kalen,
Thanks a Lot for the explanation. It was a gunshot.
Best Regards
Chip
>--Original Message--
>Hi Chip
>In the data files, the data is organized into tables and
table pages are all
>8KB. Space is allocated to a table in units of 8 pages (8
* 8 KB, or one
>extent) at a time, so there is room for more rows to be
added before more
>allocation needs to be done. The new extents allocated to
a table can can
>from anywhere in the file, although SQL Server will try
to find an extent
>close by to existing extents for the table, wherever
possible.
>Log files are not organized into tables. Log records are
just written to the
>log file sequentially as transactions commit. No
preallocation of space is
>done for the log. It's just a record of the changes to
your database.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message
>news:006f01c3d225$d0347eb0$a401280a@.phx.gbl...
>> HI,
>> Just got puzzled. Was going through SQL Arch. basics and
>> encountered about pages (8kb) and extents ( 8pg * 8kb).
>> somewhere it says *Transaction log space is not measured
>> in extents* . Could any one give details on how SQL
pages
>> & extents concept is used differently on a database and
>> transaction log file?
>> Regards
>> Chip
>
>.
>

Query about keeping down the size of transaction Logs

Hi I have been struggerling with a problem with the transaction log for a while now and find I'm just getting myself in more of a mess.

My main aim is to get around the problem that the transaction logs just keep getting bigger and bigger.

Now I have look at a few ways around it firstly I tried to use the commands:

BACKUP LOG db_name WITH TRUNCATE_ONLY
DBCC SHRINKFILE (log_name,2)

and this worked really well to reduce the size to a manageable level, however I only just found out the real impact that it has on my recovery ability.

The other solution I have looked at was to backup the database and then to use this command to backup the Transaction Logs and clear them down.

BACKUP LOG db_name
TO Disk = 'C:/example.TRN'
WITH INIT;

However after testing this it doesn't seem to reduce the size of the transaction Logs at all.

It may be a problem with my understand of the way the transaction log works, I persume that when you have a good backup then to get a point in time recovery all you should need is the details of transactions that have happened past the point of the backup and therefore be able to somehow perge the log of all other data. And that is what I persummed the last command I tried would do. However the .LDF file remains the same size.

Am i doing something wrong am I missing something out?

Thanks for any help! EdHowdy

You are on the right track, here are some ideas :

When you take a full backup, it resets all the counters for backups and any tran log backups before the last full backup cant be used after it. OK, so if you want to do a point in time recovery, do a full backup then do tran log backups after that.

What you can do ( during after hours as then you have no activity in the database to cause problems) is this :

(1) start up Query Analyser and connect to the database
(2) execute the command CHECKPOINT
- this will write all inactive entries in the tran log to the database and give you a know starting point for size reduction.

(3) run the command :
DBCC SHRINKFILE('log_file_logical_name',10)

This will attempt to shrink the tran log to 10 MB. This *should* remove all the empty space from the tran log.

****Alternatively ( and this is a big-ish step so make sure no-one is on the database when its happening as while this is going on, you WILL NOT have database recovery capability and will have to go back to your last full backup if problems occur ) :

(1) Take full backup of the database & keep safely to one side ( just in case ) and label it as "BEFORE_<DB_NAME>_SHRINK.BAK" or similar.

(2) Change database into SIMPLE recovery mode. This will allow maximum shrinkage of the tran log. This is the old "truncate log on checkpoint" setting from SQL 6.5/7 .

(3) Open Query Analyser on the database & execute CHECKPOINT .
Then execute :
DBCC SHRINKFILE('<db_data_file_logical_name>',10)
DBCC SHRINKFILE('<db_log_file_logical_name>',10)

(4) Change the database back to FULL recovery mode.

(5) Take a full backup - VERY IMPORTANT. Note that this is the reference point that tran log backups will use if you need to recover the database.

This will be the case untill you do your next normal scheduled full backup, at which point that full backup will be the starting point for any tran logs that follow it, etc.

Post back if anything not clear.

Cheers

SG.|||Thanks SQLguy I think I finally understand now. It all seems to be working okay, that a relieve! Cheers Nixies

Query about Ansi-Syntax

I need to write a stored procedure where I need to joing 3 tables A, B and C having approximately 200K, 500K, 800K rows respectively.

Query:
1) If I use ansi-syntax (inner join) as against non-ansi syntax (A.col1 = B.col1), I get a better performance.
Any idea why?

2) If I write a query (shown below), it tries to join table A and B returning large number of rows.

Select A.Col1, A.Col2
from A, B
where A.Col3 = 'xyz'

Why does it try to join the table B with A though there is no join specified.As far as the second query is concerned this is the same as writing

Select A.Col1, A.Col2
from A join B
where A.Col3 = 'xyz'

Basically, don't put the table in the from clause if you are not going to use it because it makes the query REALLY inefficient. This is a cross join and will probably give you heaps of extra records.|||actually, ejustuss, your query generates an error

if we want a cross join, we have to say CROSS JOIN, not just JOIN

and yes, there are many situations where we want to join one table to another, and yet select columns only from one|||This old school syntax can cause a lot of heartburn when you get into multiple outer joins. I do not think MS SQL 2K supports *=

Select A.Col1, A.Col2
from A, B
where A.Col3 = 'xyz'|||I do not think MS SQL 2K supports *=yes, it (still) does
create table Oldschool1
( id tinyint not null primary key identity
, foo varchar(9)
)
insert into Oldschool1 (foo) values ('Curly')
insert into Oldschool1 (foo) values ('Larry')
insert into Oldschool1 (foo) values ('Moe')
insert into Oldschool1 (foo) values ('Shemp')
insert into Oldschool1 (foo) values ('Joe')
insert into Oldschool1 (foo) values ('Curly Joe')

create table Oldschool2
( id tinyint not null primary key identity
, bar varchar(9)
)
insert into Oldschool2 (bar) values ('Curly')
insert into Oldschool2 (bar) values ('Larry')
insert into Oldschool2 (bar) values ('Moe')

select t1.id,t1.foo,t2.id,t2,bar
from Oldschool1 as t1
, Oldschool2 as t2
where t1.foo *= t2.bar
order by 1 :)|||As far as your first question,

The difference between ANSI (inner join) syntax and non-ANSI is that the optimizer will not try to derive joins that are ANSI compliant. That's because you've explicitly defined the joins. The optimizer will have to derive non-ANSI compliant joins. In many cases the query plans will be the same. However there's a greater risk the optimizer won't use the optimal plan if it has to derive the joins.

There's probably some people here that can explain this a lot better than me. Hope this helps.|||As far as your second question,

The results you're getting from that query are called a cartesian product. Since a join hasn't been specified it will match each record from on table to each record from the other. Thus if you have two tables with 100 rows each 10,000 rows will be returned.|||What I was trying to say was that in example 2 of the original post the join was implicit. Sorry about the error in syntax. Where would you want a query of the form

Select A.Col1, A.Col2
from A cross join B
where A.Col3 = 'xyz'

?|||where would you want a cross join?

1. to generate test data, e.g. a large range of dates from integers

2. with a left outer join to find missing many-to-many rows

3. to join a one-row table with application constants such as today's interest rate|||I was actually refering to the query as originally put with no reference to any column in the second table AT ALL. The generation of test data seems like a legitimate use though the other 2 surely require a reference to a column from the 2nd table in the the query somewhere even if only in the join condition.

Its just that I have seen queries with additional tables in the from clause which were unnecessary and unused and actually increased the amount of time taken to run the query substanitially.|||oh, that question

okay, well, the join would be used to ensure existence of a related row, even if you didn't need to actually return any data from the second table

for example, assume car owners are in one table, and parking tickets are in another, you might say "give me the names of every owner who had a parking ticket" and not return any row from the parking ticket table at all|||Well in this case would you not say something along the lines of

select car.owner
from car,ticket
where car.owner = ticket.reciever

?

my point being that you are actually referencing a column from the ticket table in the where clause (or join condition depending on how you write the query) - which is not what is happening in the original example.|||...which is not what is happening in the original example.that's right, it's a cross join

Query a Table to return Non-Date values

Hi
I have a table with dates stored in a nvarchar(75), I would like to move
this over to datetime (for obvious reasons)
the problem is the field has some 'bad data'
how can i query the nvarchar field to return me anything that isnt a date?
so i can fix and convert
Thanks in advance
MikeMike
Take a look at ISDATE () function
"Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
> Hi
> I have a table with dates stored in a nvarchar(75), I would like to move
> this over to datetime (for obvious reasons)
> the problem is the field has some 'bad data'
> how can i query the nvarchar field to return me anything that isnt a date?
> so i can fix and convert
> Thanks in advance
> Mike
>|||Uri,
I have been looking at that all morning and trying to understand it fully.
using the following query
SELECT MyDate, ISDATE(CommissionDate) AS Expr1 FROM MyTable
this returns a 1 or 0
the problem with ISDATE() is that it seems to rely on an american date
format
so 13/02/2003 is invalid whereas 02/13/2003 is valid
being in the UK the function appears to be as much use as a chocolate fire
guard :)
Thanks
Mike
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OTLvllykIHA.5660@.TK2MSFTNGP02.phx.gbl...
> Mike
> Take a look at ISDATE () function
>
> "Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
> news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
>> Hi
>> I have a table with dates stored in a nvarchar(75), I would like to move
>> this over to datetime (for obvious reasons)
>> the problem is the field has some 'bad data'
>> how can i query the nvarchar field to return me anything that isnt a
>> date? so i can fix and convert
>> Thanks in advance
>> Mike
>|||the UK problem can be fixed by using
SET DATEFORMAT dmy;
- got it working now
Thanks again
Mike
"Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
news:UpudnVw4esuFQG3anZ2dnUVZ8v6dnZ2d@.giganews.com...
> Uri,
> I have been looking at that all morning and trying to understand it fully.
> using the following query
> SELECT MyDate, ISDATE(CommissionDate) AS Expr1 FROM MyTable
> this returns a 1 or 0
> the problem with ISDATE() is that it seems to rely on an american date
> format
> so 13/02/2003 is invalid whereas 02/13/2003 is valid
> being in the UK the function appears to be as much use as a chocolate fire
> guard :)
> Thanks
> Mike
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OTLvllykIHA.5660@.TK2MSFTNGP02.phx.gbl...
>> Mike
>> Take a look at ISDATE () function
>>
>> "Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
>> news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
>> Hi
>> I have a table with dates stored in a nvarchar(75), I would like to move
>> this over to datetime (for obvious reasons)
>> the problem is the field has some 'bad data'
>> how can i query the nvarchar field to return me anything that isnt a
>> date? so i can fix and convert
>> Thanks in advance
>> Mike
>>
>

query a table on different server instance

I have a table TA in database A on SQL server instance IA
and I have a stored procedure in database B on SQL server instance IB.
in stored procedure I want to select all from table TA,
could you tell me what is the correct select statement?
THANKS!
First create a linked server (say, LinkedServerName) for <server name>\IA,
then you can say
SELECT <column names>
FROM [LinkedServerName].A.dbo.TA;
"bangwo" <bangwo@.discussions.microsoft.com> wrote in message
news:98010BE5-6C18-4DE6-9C4F-ADFE8E54DD6B@.microsoft.com...
>I have a table TA in database A on SQL server instance IA
> and I have a stored procedure in database B on SQL server instance IB.
> in stored procedure I want to select all from table TA,
> could you tell me what is the correct select statement?
> THANKS!

query a table on different server instance

I have a table TA in database A on SQL server instance IA
and I have a stored procedure in database B on SQL server instance IB.
in stored procedure I want to select all from table TA,
could you tell me what is the correct select statement?
THANKS!First create a linked server (say, LinkedServerName) for <server name>\IA,
then you can say
SELECT <column names>
FROM [LinkedServerName].A.dbo.TA;
"bangwo" <bangwo@.discussions.microsoft.com> wrote in message
news:98010BE5-6C18-4DE6-9C4F-ADFE8E54DD6B@.microsoft.com...
>I have a table TA in database A on SQL server instance IA
> and I have a stored procedure in database B on SQL server instance IB.
> in stored procedure I want to select all from table TA,
> could you tell me what is the correct select statement?
> THANKS!sql

Query a Sql Server LOG (LDF) File

Hello guys,
Actually i was thinking is there any way to query the LDF ( sqlserver transaction log file) to get the operations performed during that time span?
Hope we can do it!
Regards
Salim
**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...Get LogExplorer from www.lumigent.com , it's the only way AFAIK.
--
Jacco Schalkwijk
SQL Server MVP
"Salim" <salim_belim@.hargreaveslansdown.co.uk> wrote in message
news:%23repm8$kDHA.1096@.TK2MSFTNGP11.phx.gbl...
> Hello guys,
> Actually i was thinking is there any way to query the LDF ( sqlserver
transaction log file) to get the operations performed during that time span?
> Hope we can do it!
> Regards
> Salim
>
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...|||Hi,
You can use this undocumented command:
DBCC log ( {dbid|dbname } [, { -1 | 0 | 1 | 2 | 3 | 4}] )
For example:
DBCC log ('pubs', 2)
I'm sure that you can find more information about this if you search on
google, i don't have the information about what each number means right now.
--
Regards,
Kristofer Gafvert - IIS MVP
http://www.ilopia.com - FAQ & Tutorials for Windows Server 2003, and SQL
Server 2000
Reply to newsgroup only. Remove NEWS if you must reply by email, but please
do not.
Problems with spam and viruses? See
http://www.ilopia.com/security/newsposting.aspx
"Salim" <salim_belim@.hargreaveslansdown.co.uk> wrote in message
news:%23repm8$kDHA.1096@.TK2MSFTNGP11.phx.gbl...
> Hello guys,
> Actually i was thinking is there any way to query the LDF ( sqlserver
transaction log file) to get the operations performed during that time span?
> Hope we can do it!
> Regards
> Salim
>
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

query a sorted view by rows

how can I query a sorted view by rows - show rows 50 to 75
or show last 25 rows ?
thanksSam wrote:
> how can I query a sorted view by rows - show rows 50 to 75
> or show last 25 rows ?
> thanks
Views are NOT sorted. If you are using unsupported and unreliable
tricks like TOP 100 PERCENT ... ORDER BY then you'll find that they
break in SQL Server 2005 or maybe even in 2000 under some future fix or
SP.
The solution is to sort when you query the view:
SELECT A1.au_id, A1.au_lname, A1.au_fname
FROM pubs.dbo.authors AS A1
WHERE
(SELECT COUNT(*)
FROM pubs.dbo.authors AS A2
WHERE A1.au_id >= A2.au_id)
BETWEEN 10 AND 15
ORDER BY A1.au_id ;
If you want some more sophisticated paging examples then take a look
at:
http://www.aspfaq.com/show.asp?id=2120
Hope this helps.
David Portas
SQL Server MVP
--|||First of all, there's no need to create an ordered view. Sort your rows when
querying the view, or on the client.
Look up the TOP keyword in Books Online.
http://msdn.microsoft.com/library/d...r />
_13ec.asp
Also lookup RANK(), ROWNUMBER() and DENSE_RANK() in Books Online 2005, if
you're using SQL Server 2005.
http://msdn2.microsoft.com/en-us/library/ms176102.aspx
http://msdn2.microsoft.com/en-us/library/ms186734.aspx
http://msdn2.microsoft.com/en-us/library/ms173825.aspx
In SQL 2000 these functions do not exist, so you could find a paging method
here:
http://www.aspfaq.com/show.asp?id=2427 (and in the rest of the really nice
neighbourhood of aspfaq.com).
ML

Query a Query with multiple results

Hi,
New to .Net and SQL. I have two tables that I have joined together. RentalControl_Main has the rental informationd and an Adjuster ID that links to the ADjuster table and the adjusters name. I am trying to create a report that gives the "Single" adjuster name and the totals for all of their contracts. I have a details report that gives each contract info. for each specific adjusters rentals. However, I want to just list the adjuster once and give all of their totals. In my SQL statement I have all of it written out and just need to knowwhat to do in place of 'Alex Early' that will give me all of the distinct adjusters.
Do I need to code this on the page with a do while loop?
Appreciate any help.

SELECT SUM(dbo.RentalControl_Main.Rate) / COUNT(dbo.RentalControl_Main.Rate) AS AmtAvg, SUM(dbo.RentalControl_Main.DaysBilled)
/ COUNT(dbo.RentalControl_Main.DaysBilled) AS DayAvg, SUM(dbo.RentalControl_Main.Rate * dbo.RentalControl_Main.DaysBilled)
/ COUNT(dbo.RentalControl_Main.Rate) AS TotAvg
FROM dbo.RentalControl_Main INNER JOIN
dbo.RentalControl_Adjuster ON dbo.RentalControl_Main.AdjusterID = dbo.RentalControl_Adjuster.AdjusterID
WHERE (dbo.RentalControl_Adjuster.AdjusterName = 'Alex Early' AND (dbo.RentalControl_Main.DateClose IS NOT NULL) AND
(dbo.RentalControl_Main.AgencyID = '2')I don't mean to sound off-ish, but do you know much about GROUP BY and DISTINCT?

query a LDAP server from MS SQL

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

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

This resulted in a error meesage:

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [Non-interface error: OLE DB provider ADSDSOObject returned DBPROP_STRUCTUREDSTORAGE without DBPROPVAL_OO_BLOB being supported].
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].

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

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

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

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

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

if you are only wanting users you can specify

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

HTHsql

Query a dynamic table...HELP!

Hello,

I am trying to write a query to pull data from a table that is system generated daily at midnight. I tried:

declare @.tablename varchar(50) set @.tablename = (select name from sysobjects where crdate = '05/01/2003') select * from @.tablename

But I get an error on 'select * from @.tablename' (must declare @.tablename)

Any assistance is GREATLY APPRECIATED!!!!Please check the answer in

http://forums.databasejournal.com/showthread.php?threadid=31446

Query a Date Filed in SQL Server 2000

I want to query a date filed in SQL Server 2000 which its default value is set to GetDate().
My problem is that the data filed saves data and time for example "3/26/2003 5:34:34 PM", Now when I query the table and:

SELECT * FROM tblX WHERE Date='3/26/03' it won't return the record.

However if I use the below:

SELECT * FROM tblX WHERE Date='3/26/2003 5:34:34 PM'

It will return the record.

What should I do so that when I use only date and not time it retuns all records on the specified Data ignoring the time?

Thanksessentialy you want all the records for Mar 26th?

1. If you don't need time only store the date portion of GetDate(). Probably not an option.

select convert(varchar,getdate(),101)

2. Remove the time component in your date attribute in your search. This approach will probably cause a table scan.

WHERE convert(varchar,date,101)='3/26/2003'

3. Use the between test. Some times can be extra work.

WHERE date between '3/26/2003' and '3/26/2003 23:59:59'

Query 6.5 db

I know that 2005 does not support dbs with 6.5 compatibility level. However,
I can see the db and I can run a query against it. If this is possible and
works great, why can't I view/edit the SPs that are on those dbs? It is
basically the same thing as running a query, depending of course on the
difficulty of the SPs. I realize that not all new commands will work on
those older dbs, but still it would be a major help when trying to convert or
rewrite code to new standards before upgrading. Any thoughts on whether or
not I am missing something or if it is not possible no matter how hard I
would like to have it?
Thanks in advance.
cd
Hi Chris
It sounds like you are not using version control software, as this would not
be an issue if you were!
Have you tried sp_helptext?
John
"ChrisD" wrote:

> I know that 2005 does not support dbs with 6.5 compatibility level. However,
> I can see the db and I can run a query against it. If this is possible and
> works great, why can't I view/edit the SPs that are on those dbs? It is
> basically the same thing as running a query, depending of course on the
> difficulty of the SPs. I realize that not all new commands will work on
> those older dbs, but still it would be a major help when trying to convert or
> rewrite code to new standards before upgrading. Any thoughts on whether or
> not I am missing something or if it is not possible no matter how hard I
> would like to have it?
> Thanks in advance.
> cd
|||Let me clarify a little more. We have a program and database which are stuck
currently on level 6.5. Within the SSMS you can see those databases only has
their name and the classification (6.5 compatibility). You cannot drill down
into them. You can right click and choose new query. With this you can use
T-SQL to go and do anything that you can do through Enterprise Manager on the
same db. You cannot however, do anything with the SPs. You cannot even see
them. The question more clearly is whether or not it is possible to view and
edit Stored Procedures despite the limitations?
I am not writing SPs in 6.5, just needing to view and edit without having to
install 2000 tools on subsequent admin machines.
Thanks...cd
"John Bell" wrote:
[vbcol=seagreen]
> Hi Chris
> It sounds like you are not using version control software, as this would not
> be an issue if you were!
> Have you tried sp_helptext?
> John
> "ChrisD" wrote:
|||Chris
You will need to have a separate machine to do this.
John
"ChrisD" wrote:
[vbcol=seagreen]
> Let me clarify a little more. We have a program and database which are stuck
> currently on level 6.5. Within the SSMS you can see those databases only has
> their name and the classification (6.5 compatibility). You cannot drill down
> into them. You can right click and choose new query. With this you can use
> T-SQL to go and do anything that you can do through Enterprise Manager on the
> same db. You cannot however, do anything with the SPs. You cannot even see
> them. The question more clearly is whether or not it is possible to view and
> edit Stored Procedures despite the limitations?
> I am not writing SPs in 6.5, just needing to view and edit without having to
> install 2000 tools on subsequent admin machines.
> Thanks...cd
> "John Bell" 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

Query Access & MS SQL

query about publisher or subscriber

Query about Pages & Extents - Newbie

Query about keeping down the size of transaction Logs

Query about Ansi-Syntax

Query a Table to return Non-Date values

query a table on different server instance

query a table on different server instance

Query a Sql Server LOG (LDF) File

query a sorted view by rows

Query a Query with multiple results

query a LDAP server from MS SQL

Query a dynamic table...HELP!

Query a Date Filed in SQL Server 2000

Query 6.5 db

db time conversion

Blog Archive

About Me