I am deleloping a web site in .Net framework 4, SQC 2008 R2 database. I am using System Transaction for transaction and Enterprise Library data for all Database access, all DB accesses are done through stored procedures.
On one page, I have some operations (insert, update) in a transaction (on the same database), after that I have an inquiry, in this stored procedure there is join to linked server view that querying LDAP. The inquiry gives error: The requested operation could not be performed because OLE DB provider "ADSDSOObject" for linked server "ADSI" does not support the required transaction interface.
My code would have the below structure,
Dim trnscope as TransactionScope = New TransactionScope(TransactionScopeOption.RequiresNew)
transcope = nothing
objC.Inquiry invoke the stored procedures that join with a linked server view that query from LDAP
The same objC.Inquiry is run fine when calling before the transaction occurs.
It seems the connection assigned to the inquiry call still hook with "transaction". Not sure if it is the result of confict when using Connection Pool, System Transaction, Enterprise Library and LDAP together. Run the application in debug mode, I see Transaction.Current is "nothing" after the Dispose and when executing the objC.Inquiry. The error seems to indicate the connection used in objC.inquiry has "transaction" context.
My issue is similar to the post http://forums.lhotka.net/forums/p/7927/37895.aspx but I need a different solution than adding a function in the business layer and use System.Directory Services to query LDAPMonday, March 19, 2012 4:28 AM
For somebody, who is having the same problem as I had and could not find solution anywhere.
I tried couple of things and nothing worked and finally I tried using Transaction Isolation Level and I wrapped my query as shown below in transaction statement.
SET TRANSACTION ISOLATION LEVEL read uncommitted
BEGIN TRANSACTION GetData
//queries go here
COMMIT TRANSACTION GetData
It worked like a charm. Problem solved.Monday, July 23, 2012 1:28 PM