Distributed Query

I would like to perform Distributed Queries on Active Directory via views in
SQL Server. I have created a linked server by executing:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
However whenever I run even the simplest query:
SELECT *FROM OPENQUERY( ADSI,
'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
I get the following error:
OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
that the user did not have the permission to perform the operation.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e09: The provider indicates that the user did not have the
permission to perform the operation.].
I am logged into the server as an Administrator and logging into query
analyzer as sa - so I should have all required permissions.
Any ideas? Any help or direction is really appreciated - Thanks in advance,
J. Shane Kunkle
jkunkle@.vt.edu
What account is SQL Server running under? The query is run in it's context.
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/
"J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>I would like to perform Distributed Queries on Active Directory via views
>in SQL Server. I have created a linked server by executing:
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject', 'adsdatasource'
> However whenever I run even the simplest query:
> SELECT *FROM OPENQUERY( ADSI,
> 'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
> I get the following error:
> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
> that the user did not have the permission to perform the operation.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that
> the user did not have the permission to perform the operation.].
> I am logged into the server as an Administrator and logging into query
> analyzer as sa - so I should have all required permissions.
> Any ideas? Any help or direction is really appreciated - Thanks in
> advance,
> J. Shane Kunkle
> jkunkle@.vt.edu
>
|||Thanks for the response - I think we've gotten somewhere - under properties
of the linked server (under the security tab) i set all logins to use an
Administrator account (for Active Directory).
Now when i run the same basic query i get the following error:
Could not execute query against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute
returned 0x80040e22].
Unfortunately it's not very informative but I'm guessing that it means that
there is a problem with my query (syntax, LDAP string, etc)? I have tried
changing around the query a bit but no success yet.
Any ideas? Am I headed in the wrong direction? Thanks again for the help!
J. Shane Kunkle
jkunkle@.vt.edu
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uEYPMEAXFHA.1468@.tk2msftngp13.phx.gbl...
> What account is SQL Server running under? The query is run in it's
> context.
> 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/
> "J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
> news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>

Distributed Query

I would like to perform Distributed Queries on Active Directory via views in
SQL Server. I have created a linked server by executing:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
However whenever I run even the simplest query:
SELECT *FROM OPENQUERY( ADSI,
'SELECT SN FROM ''LDAP:// OU=myOu,DC=subdomain,DC=mydomain,DC=com'
' ')
I get the following error:
OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
that the user did not have the permission to perform the operation.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prep
are
returned 0x80040e09: The provider indicates that the user did not have the
permission to perform the operation.].
I am logged into the server as an Administrator and logging into query
analyzer as sa - so I should have all required permissions.
Any ideas? Any help or direction is really appreciated - Thanks in advance,
J. Shane Kunkle
jkunkle@.vt.eduWhat account is SQL Server running under? The query is run in it's context.
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/
"J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>I would like to perform Distributed Queries on Active Directory via views
>in SQL Server. I have created a linked server by executing:
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject', 'adsdatasource'
> However whenever I run even the simplest query:
> SELECT *FROM OPENQUERY( ADSI,
> 'SELECT SN FROM ''LDAP:// OU=myOu,DC=subdomain,DC=mydomain,DC=com'
' ')
> I get the following error:
> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
> that the user did not have the permission to perform the operation.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that
> the user did not have the permission to perform the operation.].
> I am logged into the server as an Administrator and logging into query
> analyzer as sa - so I should have all required permissions.
> Any ideas? Any help or direction is really appreciated - Thanks in
> advance,
> J. Shane Kunkle
> jkunkle@.vt.edu
>|||Thanks for the response - I think we've gotten somewhere - under properties
of the linked server (under the security tab) i set all logins to use an
Administrator account (for Active Directory).
Now when i run the same basic query i get the following error:
Could not execute query against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute
returned 0x80040e22].
Unfortunately it's not very informative but I'm guessing that it means that
there is a problem with my query (syntax, LDAP string, etc)? I have tried
changing around the query a bit but no success yet.
Any ideas? Am I headed in the wrong direction? Thanks again for the help!
J. Shane Kunkle
jkunkle@.vt.edu
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uEYPMEAXFHA.1468@.tk2msftngp13.phx.gbl...
> What account is SQL Server running under? The query is run in it's
> context.
> 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/
> "J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
> news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>

Distributed Query

I would like to perform Distributed Queries on Active Directory via views in
SQL Server. I have created a linked server by executing:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
However whenever I run even the simplest query:
SELECT *FROM OPENQUERY( ADSI,
'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
I get the following error:
OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
that the user did not have the permission to perform the operation.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e09: The provider indicates that the user did not have the
permission to perform the operation.].
I am logged into the server as an Administrator and logging into query
analyzer as sa - so I should have all required permissions.
Any ideas? Any help or direction is really appreciated - Thanks in advance,
J. Shane Kunkle
jkunkle@.vt.eduWhat account is SQL Server running under? The query is run in it's context.
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/
"J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>I would like to perform Distributed Queries on Active Directory via views
>in SQL Server. I have created a linked server by executing:
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject', 'adsdatasource'
> However whenever I run even the simplest query:
> SELECT *FROM OPENQUERY( ADSI,
> 'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
> I get the following error:
> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
> that the user did not have the permission to perform the operation.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that
> the user did not have the permission to perform the operation.].
> I am logged into the server as an Administrator and logging into query
> analyzer as sa - so I should have all required permissions.
> Any ideas? Any help or direction is really appreciated - Thanks in
> advance,
> J. Shane Kunkle
> jkunkle@.vt.edu
>|||Thanks for the response - I think we've gotten somewhere - under properties
of the linked server (under the security tab) i set all logins to use an
Administrator account (for Active Directory).
Now when i run the same basic query i get the following error:
Could not execute query against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute
returned 0x80040e22].
Unfortunately it's not very informative but I'm guessing that it means that
there is a problem with my query (syntax, LDAP string, etc)? I have tried
changing around the query a bit but no success yet.
Any ideas? Am I headed in the wrong direction? Thanks again for the help!
J. Shane Kunkle
jkunkle@.vt.edu
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uEYPMEAXFHA.1468@.tk2msftngp13.phx.gbl...
> What account is SQL Server running under? The query is run in it's
> context.
> 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/
> "J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
> news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>>I would like to perform Distributed Queries on Active Directory via views
>>in SQL Server. I have created a linked server by executing:
>> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
>> 'ADSDSOObject', 'adsdatasource'
>> However whenever I run even the simplest query:
>> SELECT *FROM OPENQUERY( ADSI,
>> 'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
>> I get the following error:
>> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
>> that the user did not have the permission to perform the operation.
>> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
>> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates
>> that the user did not have the permission to perform the operation.].
>> I am logged into the server as an Administrator and logging into query
>> analyzer as sa - so I should have all required permissions.
>> Any ideas? Any help or direction is really appreciated - Thanks in
>> advance,
>> J. Shane Kunkle
>> jkunkle@.vt.edu
>>
>

Distributed FULL OUTER JOIN results in INNER join

Hi,
I am trying to perform a full outer join across two linked
servers - LINK1 and LINK2. If I put the following query in:
SELECT
*
FROM
LINK1.mydb.dbo.test AS C1 FULL OUTER JOIN
LINK2.mydb.dbo.test AS C2
ON
C1.n = C2.n AND C1.s = C2.s
- the query plan gives me a MERGE JOIN/FULL OUTER JOIN
which is what I expect. If, however, I add a WHERE clause:
SELECT
*
FROM
LINK1.mydb.dbo.test AS C1 FULL OUTER JOIN
LINK2.mydb.dbo.test AS C2
ON
C1.n = C2.n AND C1.s = C2.s
WHERE C1.r = 0 and C2.r = 0
- I get a MERGE JOIN/INNER JOIN, which gives me the wrong
result. The table definition is:
create table test (n char(8) not null, s int not null, r
tinyint not null)
go
alter table test add primary key (n,s)
go
I have obviously simplified the problem as much as
possible. Am I misunderstanding something about FULL OUTER
JOINS or is this a feature? I can obviously circumvent by
selecting into two temporary tables on the local server
and doing the join there.
regards
KenMany thanks
Ken
>--Original Message--
>The problem is nothing to do with the query being a
distributed one. Because
>your WHERE clause references both sides of the join, the
result set will
>include only the INNER joined rows - WHERE C1.r = 0 AND
C2.r = 0 means that
>both r columns must be non-NULL in the result. Perhaps
what you intended
>was:
>...
>WHERE COALESCE(C1.r, C2.r)=0 AND COALESCE(C2.r, C1.r)=0
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>

Distributed APP strategies-What to do where is killing me...

I just read this quote at DevX.Com
"It's generally better to use the stored procedure option to perform the
transaction against the database rather than initiate a transaction on a
Connection object. When possible, you should isolate transactions to the
data tier, because it's the DBMS that actually performs the transactions."
OK, that seems reasonbale to me. Also, it makes sense to me that Business
Rule edits should be performed in the Application tier.
Howver, what if the Business edit requires a DB lookup, should it be
performed in the database in a SP or in a Buiness Object on the app tier?
I expect the "correct" answer is in the App Tier? Hower, if the edit is
performed in the App tier and if it passes the edit, and a SP is called to
perform the update, it seems to me that unless we have placed the Business
Rule lookup AND the UPDATE in a TRANSACTION by intiatiating a Transaction on
the Connection object, that we cannot guarantee that at the time of the db
update, that the Business Rule edit check would still pass. Some other user
may have snuck in a performed an update in between the Business Rule check
on the application Tier and the actual update to the database!
I guess I need to understand more about LOCKING. If the records queried by
the Business Edit were still locked at the time of the update, we could
assume that the state of the database is still such that an update is still
permitted. I assumed that enclosing the Business edit and the update in a
transaction would ensure that.
Now my head starts hurting when I throw into the mix the fact that we are
using .NET Remoting (Well Known Single Call) STATELESS objects.
I understand that STATELESS remote objects allow for increase scalability
and load balancing, and so that is why this approach was probably taken.
Hwoever, since the objects ae stateless, each time we instantiate an object
to perform a database call, we are establishing a new connection. And this
presents the problem of how do you tie the Business Edit lookup and the Data
Update into a single transaction by initiating a Transaction on the
Connection object is a new Connection is established with each call to the
database!
Furthermore, if you have two business abojects, Account and User, and you
want to call the ACCOUNT.AddNewAccount method and the User.AddNewUser method
and have both operation performed in a transaction, how do you do this if a
each object is establishing its own connection!?
The answer, it seems to me, is that you can't, that you need to create a new
object to do both, but this throws away the ability to reuse existing
objects.
It seems that the more granular an Object is the more reusable it is, but by
using SingleCall Remoting we do not have the ability to tie multiple updates
into a transaction. Not to mention that the Account object need to perform
an BO edit before adding an Account (to make sure it doesnt already exist in
the db) and the User object needs to peform an edit before adding a user,
that this will require *TWO* round trips to the server, which hurts
performance. By writing a new object to do both at the same time, you
increase performance by making one trip by throw away reusability since most
of the time you will need to do one operation or the otehr, not both.
I could go on and on. I hope you were able to follw the jist it.
I wish you MVPs were sitting next to my cube.
Many thanks for all the help you've given me in just a short time..
Chad
"Chad" wrote:

> I just read this quote at DevX.Com
> "It's generally better to use the stored procedure option to perform the
> transaction against the database rather than initiate a transaction on a
> Connection object. When possible, you should isolate transactions to the
> data tier, because it's the DBMS that actually performs the transactions."
> OK, that seems reasonbale to me. Also, it makes sense to me that Business
> Rule edits should be performed in the Application tier.
> Howver, what if the Business edit requires a DB lookup, should it be
> performed in the database in a SP or in a Buiness Object on the app tier?
>
Thats where you need to the data to apply your rules, you're right..

> I expect the "correct" answer is in the App Tier? Hower, if the edit is
> performed in the App tier and if it passes the edit, and a SP is called to
> perform the update, it seems to me that unless we have placed the Business
> Rule lookup AND the UPDATE in a TRANSACTION by intiatiating a Transaction
on
> the Connection object, that we cannot guarantee that at the time of the db
> update, that the Business Rule edit check would still pass. Some other use
r
> may have snuck in a performed an update in between the Business Rule check
> on the application Tier and the actual update to the database!
This is a concurrency issue, I create a BINARY_CHECKSUM containing the rows
that will be updated when the data is loaded into the business object, it's
a
private readonly property, this gets passed back to the procedure that
updates if the checksum is different at the time of update then you know tha
t
the data has changed since the read, do whatever to handle it then.

> I guess I need to understand more about LOCKING. If the records queried by
> the Business Edit were still locked at the time of the update, we could
> assume that the state of the database is still such that an update is stil
l
> permitted. I assumed that enclosing the Business edit and the update in a
> transaction would ensure that.
The locking will only occur when you do the update.

> Now my head starts hurting when I throw into the mix the fact that we are
> using .NET Remoting (Well Known Single Call) STATELESS objects.
>
Don't let this confuse you these are totaly seperate techonologies, have you
looked at CSLA...? It's a very well thought out object oriented framework of
which there are many users...
http://groups.msn.com/CSLANET
http://www.apress.com/book/bookDisplay.html?bID=198
Also take a look at Ingo Rammers remoting book, very good stuff must have if
your doing .Net remoting.
http://www.apress.com/book/bookDisplay.html?bID=108

> I understand that STATELESS remote objects allow for increase scalability
> and load balancing, and so that is why this approach was probably taken.
> Hwoever, since the objects ae stateless, each time we instantiate an objec
t
> to perform a database call, we are establishing a new connection. And this
> presents the problem of how do you tie the Business Edit lookup and the Da
ta
> Update into a single transaction by initiating a Transaction on the
> Connection object is a new Connection is established with each call to the
> database!
> Furthermore, if you have two business abojects, Account and User, and you
> want to call the ACCOUNT.AddNewAccount method and the User.AddNewUser meth
od
> and have both operation performed in a transaction, how do you do this if
a
> each object is establishing its own connection!?
> The answer, it seems to me, is that you can't, that you need to create a n
ew
> object to do both, but this throws away the ability to reuse existing
> objects.
In these cases where you have more than one business object that you want to
enlist in the same transaction you might create a controller class that
orchestrates the transaction using the EnterpriseServices.ServicedComponent
functionality which is basically MTS for .Net. This is not an ADO.NET
transaction anymore, even though your still using an ADO transaction in your
business object and maybe even T-SQL transactions in your sp's. Nonetheless
they'll be controlled by the serviced component, keep in mind this creates a
LOT of overhead, don't do this unless you have to.

> It seems that the more granular an Object is the more reusable it is, but
by
> using SingleCall Remoting we do not have the ability to tie multiple updat
es
> into a transaction. Not to mention that the Account object need to perform
> an BO edit before adding an Account (to make sure it doesnt already exist
in
> the db) and the User object needs to peform an edit before adding a user,
> that this will require *TWO* round trips to the server, which hurts
> performance. By writing a new object to do both at the same time, you
> increase performance by making one trip by throw away reusability since mo
st
> of the time you will need to do one operation or the otehr, not both.
> I could go on and on. I hope you were able to follw the jist it.
> I wish you MVPs were sitting next to my cube.
> Many thanks for all the help you've given me in just a short time..
> Chad
>
>

displaying extra characters with COLUMN-defined number format?

Here's a tricky SQL question that has definitely driven me to the end of
my rope. I'm using Oracle 9i and I need to perform some simple
multiplication on a field and then display it with a percent sign using
the COLUMN command. Here's the code thus far:

COLUMN price format 9,999.99 HEADING 'Charged%'
SELECT pricecharged * .231 as price
FROM VT_examdetail

The output from this reads:

Charged%
---
23.10
34.65
34.65
...

The kicker here is that I need to add a percent sign to the right of the
output, so that it reads:

Charged%
---
23.10%
34.65%
34.65%
...

I thought I could do this by just adding "|| ('%')" into the SELECT
statement, but when I do this the decimal position defined in the COLUMN
command is lost. Does anyone know another way around this?

Thanks,
AlexOn Tue, 07 Oct 2003 08:58:56 -0700, Alex <raindogs_1@.yahoo.com> wrote:

>Here's a tricky SQL question that has definitely driven me to the end of
>my rope. I'm using Oracle 9i and I need to perform some simple
>multiplication on a field and then display it with a percent sign using
>the COLUMN command. Here's the code thus far:
>COLUMN price format 9,999.99 HEADING 'Charged%'
>SELECT pricecharged * .231 as price
>FROM VT_examdetail
>The output from this reads:
> Charged%
>---
> 23.10
> 34.65
> 34.65
>...
>
>The kicker here is that I need to add a percent sign to the right of the
>output, so that it reads:
>
> Charged%
>---
> 23.10%
> 34.65%
> 34.65%
>...
>I thought I could do this by just adding "|| ('%')" into the SELECT
>statement, but when I do this the decimal position defined in the COLUMN
>command is lost. Does anyone know another way around this?
>Thanks,
>Alex
TO_CHAR(number, '99.99') || '%'|||Hi,

You could do this:

select cast( charged as varchar(10)) + '%' from orders

Regards,
-Manoj|||"Alex" <raindogs_1@.yahoo.com> wrote in message
news:3F82E2C0.4030204@.yahoo.com...
> Here's a tricky SQL question that has definitely driven me to the end of
> my rope. I'm using Oracle 9i and I need to perform some simple
> multiplication on a field and then display it with a percent sign using
> the COLUMN command. Here's the code thus far:

Couple of comments, this is a ms-sqlserver forum, not Oracle.

However, the other answers should work.

But, I'd say you're going about this the wrong way. Formatting should be
done at an entirely different level than the DB.

> COLUMN price format 9,999.99 HEADING 'Charged%'
> SELECT pricecharged * .231 as price
> FROM VT_examdetail
> The output from this reads:
> Charged%
> ---
> 23.10
> 34.65
> 34.65
> ...
>
> The kicker here is that I need to add a percent sign to the right of the
> output, so that it reads:
>
> Charged%
> ---
> 23.10%
> 34.65%
> 34.65%
> ...
> I thought I could do this by just adding "|| ('%')" into the SELECT
> statement, but when I do this the decimal position defined in the COLUMN
> command is lost. Does anyone know another way around this?
> Thanks,
> Alex

Displaying Backup file locations

Hi,

I have an application that I want to allow a user to backup their sql 2005 database. I use the SMO classes to actually perform the backup, but my application can run on a client machine, not just the database server. As I allow the user to select the location for their backup I would really like to show a folder list of server locations, not local (i.e. client) folders.

Basically I wondered if there were any UI objects available for Sql 2005 ( in the SMO namespaces perhaps), something that will display a list of server folders and could be used from a client machine.

Thanks for your help

Graham

You can get the server's default backup directory within SMO from the server's .Settings.BackupDirectory property. You should be able to find other directories using WMI queries, I'd think.

|||As Allan said, this is done through WMI.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de