Does anyone use the OraclePersistence to replace the sqlPersistence in the pageflow

Sep 30, 2007 at 8:49 AM
Hello,All
I have a project , i used the release OraclePersistence to replace the SqlWorkflowPersistenceService in the pageflow . it works well in the Debug mode. but when i deploy it server , sometimes it will encounter the problem ..

Event Queue operation failed with MessageQueueErrorCode QueueNotFound for queue 'Message Properties
Interface Type:Microsoft.Practices.PageFlow.WorkflowFoundation.IDataExchangeService
Method Name:SetData
CorrelationValues:


The strange is it happend after it deploy to the server for a period , but when i re-start the IIS , or clean the data in the table of instancestate, and PAGEFLOWINSTANCESTORE. it can work fine but the issue will re-occur one/two hours later.


And when i change to use the SqlWorkflowPersistenceService , it works fine !!

This is very urgent ,hope some one can help .


Lizzy

Sep 30, 2007 at 8:55 AM
So dose it means the OraclePersistence did not work
Coordinator
Sep 30, 2007 at 5:13 PM
Hi Lizzy,

I haven't tried the PageFlow stuff out just yet, I'll get it installed shortly and take a look on my way to work tomorrow morning.

Cheers,

Dean
Oct 9, 2007 at 12:45 AM
Dean,
Do you have any finding ?
my DB is Oracle , so i have to use the OraclePersistence in the project .
this is very urgent for my project , hope you can help me .



Thanks
Lizzy
Oct 9, 2007 at 7:40 AM
Hello , Dean
can you tell me what does the method attach below in the DefaultAdoResourceProvider.cs used

/// <summary>
/// Creates an ADO connection and enlists it in the specified transaction.
/// </summary>
/// <param name="transaction"></param>
/// <returns></returns>
public DbConnection CreateEnlistedConnection(Transaction transaction)
{
DbConnectionStringBuilder dbConnectionStringBuilder = dbProviderFactory.CreateConnectionStringBuilder();
dbConnectionStringBuilder.ConnectionString = connectionStringSettings.ConnectionString;
// make sure the connection isn't enlisted by default
if (dbConnectionStringBuilder.ContainsKey("enlist"))
dbConnectionStringBuilder.Remove("enlist");

AddEnlistToConnectionString(dbConnectionStringBuilder);

DbConnection dbConnection = dbProviderFactory.CreateConnection();
// enlist the connection in the specified transaction
dbConnection.ConnectionString = dbConnectionStringBuilder.ToString();
dbConnection.Open();
// Change by Lizzy
//dbConnection.EnlistTransaction(transaction);

return dbConnection;
}

I comment the dbConnection.EnlistTransaction(transaction); because if i do not do this , will have encounter the issue , like the The Transaction Manager is not available. but if i comment it ,it pass. so i do not know if because I comment the code caused the oracle persistence service can not work .....


Thanks
Lizzy
Coordinator
Oct 9, 2007 at 8:39 PM
Edited Oct 9, 2007 at 8:41 PM
Hi Lizzy,

My apologies, I haven't had the opportunity to look at PageFlow yet, my laptop decided to breakdown so I've been spending my spare time fixing it!

The EnlistTransaction method is used to make sure the specified connection participates in the transaction created by the current WorkflowCommitBatchService. Unfortunately, as you've discovered, Oracle's implementation of this requires the use of distributed transactions. This means that the Microsoft Transaction Co-ordinator (MTS) needs to be involved which requires communication outside of the current process (and sometimes even the same machine).

By commenting the line of code you are effectively preventing the workflow from working within a transaction - this means that if any part of the persistence fails you may have incomplete data in your database. This could very well produce the effects you specified in your first post.

Your only option right now is to ensure that your architecture supports the distributed transactions required by the Oracle driver. A simple test case would be something along the lines of...

using (TransactionScope txScope = new TransactionScope())
{
using (OracleConnection oracleConnection = new OracleConnection(myConnectionString))
{
oracleConnection.Open();
// do some stuff
}
txScope.Complete();
}

If this throws exceptions about transactions then there is a problem with MTS on the machine you're running it on. If you're using ODP.NET It may be beneficial to make sure you have Oracle Services for MTS installed as this is what helps Oracle co-ordinate with MTS. Otherwise, if you're using System.Data.OracleClient then search for MTS troubleshooting and you should get some hits on how to fix things.

In the next release I intend to use a workaround that will allow Oracle to use a local transaction, but until then, you'll need to do things using MTS!

Let me know how it goes.

Dean