MSDN Magazine article on Windows Workflow tracking is live

HI Matt,

I read with a lot of interest your MSDN article and did download your .net solution.
unfortunately the most interesting topic you cover in your article , the possibility to query the tracking db for 'extracted' data.
i did load your example tracking profile in my sqlserver (express) db and did run several workflows including your email activity.
The problem is that when I call the QueryForEmailWorkflows method i never get any hits (used various queries).

question: is there any way via native sqlserver sql queries (or something else)to find out that the 'extraction' of the properties specified in the tracking profile really work and the data really end up in the db ?

since i agree with you that the tracking is one of the most interesting features of the workflow foundation, i would really appreciate your help to see that feature working.

thanks a lot for any feedback in advance.
regards
hansh

hansh,
You can look into your sqlexpress tracking database and open the TrackingDataItem table to view any values that were tracked. Keep in mind that if you tracked objects, they must be serializable or they won't get stored. Also, queries on your properties are only going to work for simple types that are stored as a value in the column, not complex types (classes) that are serialized into the blob field.
Matt

Hi Matt,

thanks for your reply.
Question: In the of the sendemail activity you use in your msdn article- i assume the properties (like to and subject etc.) are stored in the blob field since the sendemail is a class - is this assumption correct ?

if yes, and if you can not query for data stored in the blob field- how do the query for data in the "To" field that you use in your msdn article work resp. what do i need to do to query for the data in the blob field. i assume there must be a way to use the workflow api to query those data otherwise
it would make no sense to populate this field by the tracking service.

if the properties exposed by your sendemail activity you use in the msdn article are not stored in the blob field i have a fundamental problem , because all the entries in the trackingdataitem table do not contain any dataitems that i can associate with the sendemail activity- therefore i do assume that all the data are stored in the blob field.

would be great if you could help me in understanding this issue.

final question: is the "equal" operator the only valid operator i can use for extracted data or is there a way to use other operators like greater than or like ?

i personally think that the tracking and the corresponding services and API's are very powerfull , but if the only supported query you can use is "is equal to" i do not see lots of use cases for the featre of querying extracted data.

hansh

hansh,
your first assumption is actually incorrect. The values being stored are strings (to, subject, etc.) and not objects. If I was tracking an activity that had a property of type Customer, where customer is a custom class of mine, then I could store that whole object in the tracking database. That would go in the blob field. But, if I were tracking the customer ID, the value is a simple type and would get stored in the column as a readable value.
If you are not getting values stored, make sure the profile is deployed to the right tracking database (the sample profile editor points to a different database by default I think).
I should also be more clear. You can query FOR the blob data, but you cannot use the blob in your filter criteria. So I could search for records based on the customer name, but have access to the entire customer object if it had been tracked. So there is a difference between what you can track and read from a query, and what you can use in the filter criteria.
As for the operators, equal is the only one supported currently. Basically, the items you specify are put into a temp table which is joined to the instances returned. you can write your own stored procedure, and API for that matter, to query the database; but that does involve more work on your part.

HI Matt,

again thanks for your feedback.
Unfortunately I still do not see any entries in the trackingdataitem table.
I checked again that I did load your EmailTrackingProfile.xml file in the correct db (i also named my db workflowtracking).

I get more and more the feeling that my database is the cause of all my problems. I am not very experienced with SQLServer (my background is Oracle RDBMS) but i think i did the necessary steps to create the tracking db.
Here is what I did:
- create new db workflowtracking
- run script tracking_schema.sql from C:\WINDOWS\Microsoft.NET\Framework\v3.0\Windows Workflow Foundation\SQL\EN
- run script tracking_logic from
C:\WINDOWS\Microsoft.NET\Framework\v3.0\Windows Workflow Foundation\SQL\EN

- used trackingprofile designer to load the profile xml

i verified that the profile is present in the trackingprofile table and that the profile is associated with the sendemail activity.

after run of your workflowtracking project (looking at the console output the execution did succeed) there are no entries in the trackingdataitem table and (and here i get the feeling there is something fundamentally wrong) i also do not get any entries in the workflowinstance table.

really not sure what i should do and slowly i get very frustrated (sigh).

are there any things i can check in the db to find out if i have a problem ?

is the owner of the db important. i used (i know this generaly not a good idea but i use a development db) the sa account to create the db i.e. sa owns the db (all tables in the db are owned by dbo).
is that a problem ?

regards
hansh

PROBLEM (somewhat) SOLVED

i now get the entries in the trackingdataitem table and i now see the 'plain' fieldnames and data_str values- all as expected.

this is what i did. i told the trackingservice to use the DEFAULT profile and i copied the 'sendemail' activitytrackpoint from your msdn profile into the default profile (and updated the db with the modified profile).

now everything works !

this of course can not be the final solution, but now it seems clear that the tracking service does not associate the sendemail activity with the correct profile. no idea why that happens, but it could well be the trackingprofiledesigner that cause the problem.

at least i now see tracking of extracted data working in principle and i have a profile that i know works.

will now go ahead and try to find out why the profile (even thoug it is present in the db and eventhoug the associated workflowtypeactivity is the one referenced in the type table is not correctly associated.

in any case i thank you for all your feedback and explanations.

regards
hansh