Worked on one computer now SqlWorkflowPersistenceService fails to persist on new computer.

Worked on one computer now SqlWorkflowPersistenceService fails to persist on new computer.

• Worf

  Thank you for responding! I am at a critical point now and really need to get this going. The only thing I changed in the config file is the connection strings to workflow and my employee database.

  Here is another thought. I fired up SQL Profiler to see what happens when “exec [dbo].[InsertWorkflowInstance]” is fired and yet when I check table “WorkflowInstance” the data never persisted. Another point is that when I copy the SQL SP from SQL Profiler and run manually the entry makes it to table “WorkflowInstance”. Last point about this anomaly is that when I look at the database column “WorkflowInstanceInternalId”, after I run manually, the number is 17. Knowing that the column “WorkflowInstanceInternalId” is an bigint, not null, primary key, identity increment of 1, tells me there is a record being created and then some how deleted.

  Community please respond if you have a fix. From my web.config file:

  <WorkflowRuntime Name="WorkflowServiceContainer">

  <CommonParameters>

  <add name="ConnectionString" value="Initial Catalog=TrackingStore;Data Source=DATASTORE\SQLEXPRESS;User ID=Workflow; Password=workflow"/>

  </CommonParameters>

  <Services>

  <add type="System.Workflow.Runtime.Hosting.ManualWorkflowSchedulerService, System.Workflow.Runtime, Version=3.0.00000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>

  <add type="System.Workflow.Runtime.Hosting.SqlWorkflowPersistenceService, System.Workflow.Runtime, Version=3.0.00000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" UnloadOnIdle="true" LoadIntervalSeconds="5"/>

  <add type="System.Workflow.Runtime.Tracking.SqlTrackingService, System.Workflow.Runtime, Version=3.0.00000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>

  </Services>

  </WorkflowRuntime>

  
  
  

• GaV56

  Some of this will be obvious but I'm compelled to ask anyway:

  1. Did you verify the config files are the same (if you used configuration to add services to the runtime using the WorkflowRuntimeSection )
  2. Still setting UnloadOnIdle to true on the SqlWorkflowPersistenceService instance (through config )

  If all this looks good could you post the pertinent portions of the config file and the code where you start up your runtime instance

  
  
  

• den2005

  The stored proc you want to watch is InsertInstanceState.  It will be fired by the persistence service whenever a persistence point is reached in your workflow (either an event wait or end of an activity marked with PersistOnClose like the TransactionScopeActivity.)  InsertWorkflowInstance is a stored procedure used by the SqlTrackingService.

  The table you want to watch for persisting workflows is called InstanceState.  The WorkflowInstance table is for tracking only.  Are there records in InstanceState

  As you watch the SQL Profiler you should see a call to InsertInstanceState which will have the @state parameter full (not 0x) and the @status parameter will be 0.  Then sometime later you will see a call with @state set to 0x and the @status parameter will either be 1 once your workflow has completed (reached final state.)  Are you seeing any calls to InsertInstanceState

  One other thing to note (added after my initial reply):
  You probably need to add the SharedConnectionWorkflowCommitWorkBatchService to the runtime.  You can see the configuration that I'm working with here.  This is important if you are using a database instance on a remote machine to avoid DTC cost and configuration issues.  I'm not sure that you can even use a remote SQL Server for the SQL persistence and tracking services without it based on this post.

  
  
  

• Thurein

  Thank you All for replying. UPDATE - a coworker Geoff pulled the code out of source control and we were successful with the build and persistence. The difference between the computer that is not working at the moment has SQLExpress instead of development like I had on the machine that was working. I am going to install SQL Development and give it a whirl.

  Stan

  
  
  

• Jim P.

  Another Update – I finally accomplished starting persistence on the stubborn machine. The last thing I did was add ASPNET in IIS. I thought in debug we did not have to set up a virtual directory but in the end I did set up a virtual directory and in the Directory Security I added ASPNET. Another note was at the same time of setting up the virtual directory I also enabled tracing. Don’t forget to give permission to your log file in the root of your web.

  Disclaimer: This is what I did on my computer but may not be the end all fix for everyone. I was successful to get it working on a co-workers machine the first try yet on a new machine I ran into problems.

  All my steps to get persistence working with ASP.NET StateMachine.

  1. Install .NET 3.0

  2. Transferred my working code from the original computer that the persistence was working to the new computer.

  3. Created a database called, (Use your own name) , “TrackingStore”.

  4. Run the scripts found “C:\WINDOWS\Microsoft.NET\Framework\v3.0\Windows Workflow Foundation\SQL\EN” First run the schemas then the logic.

  5. Add your parameters

  The “SharedConnectionWorkflowCommitWorkBatchService” ran with this and with out. I need to do more research on this element.

  http://msdn2.microsoft.com/en-US/library/system.workflow.runtime.hosting.sharedconnectionworkflowcommitworkbatchservice.aspx

  <WorkflowRuntime Name="WorkflowServiceContainer">

  <CommonParameters>

  <add name="ConnectionString" value="Initial Catalog=TrackingStore;Data Source=.\SQL2005;User ID=Workflow; Password=workflow"/>

  </CommonParameters>

  <Services>

  <add type="System.Workflow.Runtime.Hosting.ManualWorkflowSchedulerService,

  System.Workflow.Runtime,

  Version=3.0.00000.0,

  Culture=neutral,

  PublicKeyToken=31bf3856ad364e35"/>

  <add type="System.Workflow.Runtime.Hosting.SharedConnectionWorkflowCommitWorkBatchService,

  System.Workflow.Runtime,

  Version=3.0.00000.0,

  Culture=neutral,

  PublicKeyToken=31bf3856ad364e35" />

  <add type="System.Workflow.Runtime.Hosting.SqlWorkflowPersistenceService,

  System.Workflow.Runtime,

  Version=3.0.00000.0,

  Culture=neutral,

  PublicKeyToken=31bf3856ad364e35"

  UnloadOnIdle="True"

  LoadIntervalSeconds="5"/>

  <add type="System.Workflow.Runtime.Tracking.SqlTrackingService,

  System.Workflow.Runtime,

  Version=3.0.00000.0,

  Culture=neutral,

  PublicKeyToken=31bf3856ad364e35"/>

  </Services>

  </WorkflowRuntime>

  6. Add tracing in the beginning to see watch what is happening

  <system.diagnostics>

  <switches>

  <add name="System.Workflow LogToTraceListeners" value="1" />

  <add name="System.Workflow.Runtime.Hosting" value="Verbose" />

  <add name="System.Workflow.Runtime" value="Verbose" />

  <add name="System.Workflow.Runtime.Tracking" value="Verbose" />

  <add name="System.Workflow.Activities" value="Verbose" />

  <add name="System.Workflow.Activities.Rules" value="Verbose" />

  </switches>

  <trace autoflush="true" indentsize="4">

  <listeners>

  <add name="myListener" type="System.Diagnostics.TextWriterTraceListener" initializeData="WFTrace.log" />

  </listeners>

  </trace>

  </system.diagnostics>

  7. Start SQL Profiler

  8. In IIS create a virtual directory and set the user to aspnet.

  9. Once everything works then you can disable the tracing and profiling.

  I hope this helps.

  Stan

  
  
  

• Luke R

  You may have to use the AllowUserInstance='True' and in your Connection String if that is the case or Attach to the .mdf File.

  It all depends how you have SQLEXPRESS Setup

  
  
  
• Worked on one computer now SqlWorkflowPersistenceService fails to persist on new computer.