Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.
Highlighted
Super Contributor.. AldoH Super Contributor..
Super Contributor..
172 views

How can I get the activity notes of a workflow directly from the tables in SQL Server?

Jump to solution

Hi,

I need to create a query in SQL Server where I can get the activities of certain workflows including the notes that users assigned to each activity. I have no problem with getting the activities, but I don't know how I can get the notes of those activities.

I have reviewed the TSNOTES, TSNOTES2 and TSNOTES3 tables, and after seeing their content I think that the TSNOTES3 table is the one that contains those notes. Is this the correct table? Or should I look in another table?  If that is the correct table, how can this table be related to the TSWKACTIVI table?

The structure of TSNOTES3 includes the n3TableID, n3TableUri and n3Chunk fields. What do these fields store? What do the values they contain mean?

Thank you.

0 Likes
1 Solution

Accepted Solutions
Quinton Bernhardt Super Contributor.
Super Contributor.

Re: How can I get the activity notes of a workflow directly from the tables in SQL Server?

Jump to solution

Is see there is a stored proc FetchNotesPart3 taking 2 parameters, 

TableId - which should be 160 for workflow activities

TableUri - the Uri of the activity.

You can check out the sql for that stored proc.

So for my test - my activity id is 545.  To see all the notes:

 

SELECT * FROM TSnotes3 WITH (NOLOCK) WHERE n3TableUri = 545 AND n3TableId = 160 ORDER BY n3TableUri ASC, n3TableId ASC, n3Chunk ASC

 

I don't know what the n3Chunk value is for.

PS: I used Workgroup Server logging to get to the Sql.

 

Regards,
Quinton Bernhardt
Meniko Records Management Services

View solution in original post

4 Replies
Quinton Bernhardt Super Contributor.
Super Contributor.

Re: How can I get the activity notes of a workflow directly from the tables in SQL Server?

Jump to solution

Is see there is a stored proc FetchNotesPart3 taking 2 parameters, 

TableId - which should be 160 for workflow activities

TableUri - the Uri of the activity.

You can check out the sql for that stored proc.

So for my test - my activity id is 545.  To see all the notes:

 

SELECT * FROM TSnotes3 WITH (NOLOCK) WHERE n3TableUri = 545 AND n3TableId = 160 ORDER BY n3TableUri ASC, n3TableId ASC, n3Chunk ASC

 

I don't know what the n3Chunk value is for.

PS: I used Workgroup Server logging to get to the Sql.

 

Regards,
Quinton Bernhardt
Meniko Records Management Services

View solution in original post

Super Contributor.. AldoH Super Contributor..
Super Contributor..

Re: How can I get the activity notes of a workflow directly from the tables in SQL Server?

Jump to solution

Thank you very much, I have already achieved the result I needed.

An additional question, with the help you gave me now, I know that 160 applies to workflow activities, but where can I get information on what the other values that are stored in the n3TableId field mean?

These are the different values that I can find in the n3TableId field:

0, 29, 38, 43, 60, 61, 148, 159, 160, 167, 213, 1003, 1004, 1007

 

 

0 Likes
Quinton Bernhardt Super Contributor.
Super Contributor.

Re: How can I get the activity notes of a workflow directly from the tables in SQL Server?

Jump to solution

@David Churchland can you provide us with a list of TableIds as per request above?  It would be helpful.

Regards,
Quinton Bernhardt
Meniko Records Management Services
0 Likes
Micro Focus Expert
Micro Focus Expert

Re: How can I get the activity notes of a workflow directly from the tables in SQL Server?

Jump to solution

I have sent the requested list to @Quinton Bernhardt.  The internal schema ids are not something we typically publish on the forum.

I am sure Quinton will be happy to help anyone else who is interested.


Blog | Samples | CM SDK Docs
**Any opinions expressed in this forum are my own personal opinion and should not be interpreted as an official statement on behalf of MicroFocus**
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.