Active Directory/ADSI connection problems...Here's the correct syntax

I am having weird problems using ADSI and SQL Server. Our local intranet is ASP with a SQL database on Windows 2003 Server. It uses Active Directory (via ADSI linked server) to get authenicate users, etc.

Every now and then (about once a month) the SQL connection to AD will "crash". The following error is what I see when it has crashed:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute returned 0x80040e37].

Sometimes it will come back on its own later, others are solved by a server reboot. Here is how my SQL query looks:

SELECT adspath
FROM OPENQUERY(ADSI,
'<LDAP://DC=servername,DC=net>;(&(objectCategory=Person)(objectClass=user));adspath;subtree')
Rowset_1
WHERE (sn <> '')

Where can I start to get to the bottom of this?Anyone?|||

Is your issue resolved?

Thanks

|||I have the same problem, please if you solve it please help me fix it.|||

Have you gotten anywhere with your problem?

This is what was found in our SQL Error logs: "Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b".

Our problem occurs from our weekly reboot - so we see the error toward the top of the log. This error seems to be occuring frequently on our servers lately. It is not only causing outage problems on our ADSI linked servers but we have a new implementation of an application that uses impersonation(Kerberos) from Web & Sharepoint servers to SQL Server that is about to go to production that recently shared in this problem also.

It seems to be due to the fact that MSDTC is not available when SQL Server starts. We are going to be trying a registry key fix to force a dependency that was recommended. We will see after this weekend.

|||

Just to follow-up:

It's been a month now and the fix seems to have worked. The originator of this thread had problems with SQL Server on Windows 2003. But in our systems, the servers affected are SQL Servers on Windows 2000 only, both SQL Server 2000 and 2005.

Add the "MSDTC" to the registry to force SQL Server to be dependent on MSDTC service (where MSSQL is the default instance):

Key: [HKEY_LOCAL_MACHINE\SYSTEM\CURRENTCONTROLSET\SERVICES\MSSQL]

Value Name: DependOnService

Value Type : REG_MULTI_SZ

Value: MSDTC

|||

Almost the whole issue is syntax related.

Linking SQL Server to Active Directory is tricky. and Microsoft support/help isn't very intuitive for this.
Check a few things first...
1. two single quotes not double.
2. Make connection w/out security context.

Follow this syntax:
EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI',
@.srvproduct=N'Active Directory Services', @.provider=N'ADsDSOObject',
@.datasrc=N'Servername.domain.com' --AKA the full computer name of the AD server

Then execute the openquery like this:

select * from openquery
(
ADSI,'SELECT name
FROM ''LDAP://Servername.domain.com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')

Active Directory User's Member Groups from SQL Server 2000

I can create a Linked Server for ADSI sucessfully but cannot retrieve group
membership for an indivdual user.
I've seen some examples of syntax but can't get it to work.
Does anyone have an example of some code I could use
LindaOLE DB provider for Directory Services does not support multi-valued
attributes such as: members and memberOf. sorry...
"Linda Lou" wrote:

> I can create a Linked Server for ADSI sucessfully but cannot retrieve grou
p
> membership for an indivdual user.
> I've seen some examples of syntax but can't get it to work.
> Does anyone have an example of some code I could use
> Linda

Activating the Queue

Hi,

I have created Queue with the following syntax. But it is not getting activated itself. What I have to do to get it activated itself, and what could be the frequency by default.

CREATE QUEUE NewCustomerQueue
WITH ACTIVATION
(PROCEDURE_NAME = prProcessNewCustomers,
STATUS = ON,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF)
GO

If I execute the prProcessNewCustomers procedure manually it is showing that the Queue has been activated. What change I have to make in the syntax to get it activated itself.

Actually I have two scenarios in my requirement,

1. One Queue processing immediately when it receives data (Order Processing)

2. Another Queue, Process when the server is idle i.e., off-peak time (for mailing)

What syntax I have to use for these.

Please help.

Thanks in advance

Babu

Activation can be turned on and off using ALTER QUEUE

ALTER QUEUE NewCustomerQueue WITH ACTIVATION (STATUS = ON|OFF);

So in your first scenario you can keep activation on always. In your second scenario, you can keep it off by default and turn it on whenever desired.

Activation launches the specified stored procedure in the background upon receipt of incoming messages. If the procedure cannot keep with the rate of incoming messages, it can launch concurrent instances of the stored procedure (you need to set max_queue_readers > 1 for that).

Hope that helps,

Rushi

|||

Hi Rushi,

Thanks for your reply.

I have created the Queue with the ACTIVATION (STATUS = ON) only.

Currently I'm trying with a typical example what I got from some books. When a new customer is added, the trigger in the Customer table will "BEGIN DIALOG CONVERSATION" and send a message. I think when a message is sent, the Queue should get activated. It is not happening in this case. What could be the reason.

Please help.

Regards

Babu

|||

Hi Babu:

First make sure the trigger runs by checking in query analyser. Check the sql profiler to see whats the errror your getting.

Pramod

|||

Do you see the message in the target queue?

select * from [target queue]

(where [target queue] is the name of your target queue)

Rushi

|||

Hi Rushi, Pramod,

Thank you both for the responses.

The problem was occured because the 'Idle CPU Condition' was not defined.

I'll come back with new doubts . Thank you once again.

Regards

Babu