Tag: Database

SharePoint & SQL Server – itgroove Blog Roundup

Get Check-in Comments with Nintex

NOTE: I’ve posted a revised version of this type of method for getting version history. The SQL in this post is not to be trusted! (but the rest is still good so I’ll leave the post as-is for posterity).

I needed to grab the document check-in comment insides of a Nintex 2010 / SharePoint 2010 list workflow. I couldn’t seem to locate the field anywhere Was told by Nintex support the following:

“It is not possible to reference the version history of a document within a workflow similar to how you can reference standard SharePoint fields. Even the web service method GetVersionCollection: http://msdn.microsoft.com/en-us/library/lists.lists.getversioncollection(v=office.12).aspx only returns the version history for a specific field. There may be another web service Microsoft exposes to enable this however this would be a question directly for MS.”

Oh well, right? Never say die! Use the Execute SQL Query widget in Nintex and let’s do some data mining:

1. Add an Execute SQL Query action to wherever in your Nintex workflow you need to access the Check-in comment.

2. Create a variable to store the check-in comment, in this case I call it vCheckInComment.

3. Add your SQL connection string. Considering that using Windows Authentication is probably what you’re going to be dealing with when using SharePoint, you will need the DB service account credentials.

4. Create your SQL query. For getting the check-in comments it is simple : In your SharePoint Content database there will be a table called AllDocs which contains two columns of interest: LeafName and CheckInComment.  You Can also grab the ListID GUID in there if needed – in this example I am already inside a Query List / For Each loop which has filtered the records based on the current list I am working with. The LeafName column stores the document e.g. MyDocument.doc. The CheckInComment column stores the comment for that file. In this example the {WorkflowVariable:vDocumentName} Nintex variable is my document name variable:

And that’s that – the check-in comment query associated with that documents record goes in the vCheckInComment column.

Disclaimer: there’s much more to the DB structure then just the document name and check-in comment. Versioning isn’t this simple. Also, running ad-hoc SQL queries on SharePoint is generally not best practice and you will get chewed out by MVP’s.  Reasons why ad-hoc SQL is bad news (see http://www.sharepoint4arabs.com/AymanElHattab/Lists/Posts/Post.aspx?ID=99 for a good summary)

  1. This is completely unsupported by the EULA you agreed to when you installed SharePoint.
  2. Your queries are not guaranteed to work after applying any patches or service packs to SharePoint since Microsoft could change the database schema anytime.
  3. Directly querying the database can place extra load on a server and hence performance issues.
  4. Direct SELECT statements against the database take shared read locks at the default transaction level so your custom queries might cause deadlocks and hence stability issues.
  5. Your custom queries might lead to incorrect data being retrieved.

Definitely run your SQL queries with NoLock on and keep it to simple, read-only queries if you’re going to pull stunts like running SQL queries from Nintex as described in this post. Also carefully consider versioning and data commit issues inherent from the SharePoint architecture side of things before counting on any of the results you pull out as gospel.