Friday, June 16, 2006

'ADSDSOObject' does not support the required transaction interface

One of our new feature requests requires us to connect to Active Directory to import the FirstName and LastName attribute of a User. Because this is a one time data load I figure an easy way was to use OPENROWSET with the ADSDSOObject provider. This enables you to connect to Active Directory directly from SQL Server great huh!

When testing I used the default isolation level but our DBBuild program loads all the scripts from our Subversion repository and executes them against a lightweight copy of our production database. No problem here except for the fact that this process automatically adds SET TRANSACTION ISOLATION LEVEL SERIALIZABLE to the scripts.

Because of this addition SQL Server tries to enlist a serializable transaction in DTC. Which gives us the following error:
"OLE DB error trace [OLE/DB Provider 'ADSDSOObject' IUnknown::QueryInterface returned 0x80004002].
Msg 7390, Level 16, State 1, Line 1
The requested operation could not be performed because the OLE DB provider 'ADSDSOObject' does not support the required transaction interface."


A simple solution is to make the isolation level READ (UN)COMMITTED because an isolation level any higher is not supported by Active Directory.

6 comments:

Anonymous said...

I must thank you for this post. You have made my day.

Anonymous said...

I second that. Found this right away and it worked perfectly.

pridmorej said...

I also encountered this message when I tried to create a stored procedure that used a view which in turn utilised a linked server to ADSI. I had to use the PARSEONLY option. I was logged in as a Windows User and I'm sure I've encountered it working before as a SQL Server user, however, I was not able to login as one to test. Using PARSEONLY helped me work around the error and create the proc.

pridmorej said...

I just realised that my post above isn't right - obviously PARSEONLY doesn't actually create the stored proc. Please delete these posts if you can :)

pridmorej said...

In fact my suspicion was correct, when connected as a Windows user I encountered the error. When I changed connection to a SQL Server user, the stored procedure was created correctly.

HTH :)

WesleyB said...

Thanks PridmoreJ. Hope it helps other people too ;-)