Nintex Workflow – Starting a Nintex Workflow from SQL

I had a proof of concept recently that required me to look into starting workflows from SQL Server. It’s an interesting idea which I’d never played around with before. I’ve done development inside SQL, and have created plenty of Stored Procedures, but I’ve never had to deal with communicating outside of SQL itself.

The following is my life story…. ok maybe not. But it shows what I needed to do, to get this to work.

Visual Studio project to make a SQL CLR Stored Procedure

In order to make a web service call, you will need to first create a stored procedure, but one using CLR.

Open up Visual Studio 2010 and create a new project (Database->Visual C# CLR Database Project).

Next, you want to right-click on the References in the new project, and select Add Service Reference.

In this window, click on the button at the bottom left (Advanced…), then the “Add Web Reference” button.

You can then insert the url to the Nintex Workflow web service in your environment.

eg.

http:/[siteurl]//_vti_bin/NintexWorkflow/Workflow.asmx

Give the web service a name, and then click the Add Reference button.

We now have a project that has a reference to the Nintex Workflow web service in your SharePoint environment.

The next step to create a Stored Procedure.

Right click on your project in Visual Studio and select Add->Stored Procedure:

Give your Stored Procedure a name, and you should now have a C# (cs) file for your Stored Procedure. The class name will be named “StoredProcedures”, and there will be a static method with the name you gave your stored procedure.

What we need to do, is add some code to make our web service call. We’ll keep this simple by having some requirements. You can modify this as you wish and make it as flexible as necessary. But in this example, we are going to hardcode values in.

Requirements

List on my site named: SQL Test

Workflow on that list named: SQL Test Workflow

 An item in that list with ID: 1

 The code is quite simple. We instantiate an instance of the workflow web service, put in some credentials and make a call to the StartWorkflowOnListItem.

Make sure you added a “using” direction to the top of the file, so that you have access to the NintexWorkflowWS class. Or, fully qualify it.

We’re not quite finished. Now we need to make some modification to the project properties.

 Right click on the project in Visual Studio and click on Properties.

Sign your Project

This is probably not a requirement, but it’s a good habit to get into when developing C# assemblies.

In properties view, select the “Signing” tab on the left and check the box “Sign the assembly”. If you don’t have a key, you can create on by selecting from the drop down.

Modify Permission Level

Click on the “Database” tab and in the Permission Level drop down, select “External”.

Because we are going to make an external call (Web Service), this permission is required.

Serialization

Web Service calls use SOAP (xml), so the data being sent and coming back needs be serialized and deserialized. In order to this from our SQL Stored Procedure, we need generate the required serialization assembly.

Click on the “Build” tab, and the check the box “Generate serialization assembly”.

You can now compile the project and you should get 2 assemblies in the bin\Release (or Debug) folders. In my case, I called my project “VTStoredProcedureTest2”, so I have 2 assemblies.

VTStoredProcedureTest2.dll

VTStoredProcedureTest2.XmlSerializers.dll

Configuring SQL Server

CLR in not enabled on SQL Server, so you will need to enable this. The easiest way is in SQL Management Studio, and run something like this:

exec sp_configure ‘clr enabled’, ‘1’; reconfigure;

You see what is currently configued, by just running the following:

exec sp_configure;

Just for our test, I’m going add my new Stored Procedure to my Nintex Workflow 2010 database (NW2010DB). Unless you really need to, I’d recommend creating your own database rather than adding to the Nintex Workflow one, but as long as you don’t manipulate out of the box objects, you should be fine.

 Our Stored Procedure is going to make a call to an external assembly (which contains our code to make the web service call). To be allowed to do this, you will need to make sure the database is trusted.

 You can do this by running the folllowing in SQL Management Studio:

ALTER DATABASE

NW2010DB

SET TRUSTWORTHY ON;

Replace “NW2010DB” with your own database.

 Now that we have all the permission we need, it’s time to add the assemblies to our database. We want to add both the main assembly and the XmlSerializer assembly.

 This is done with the following SQL Script:

USE NW2010DB;

CREATE ASSEMBLY MyStoredProcedures
FROM ‘C:\Users\Vadim\Documents\Visual Studio 2010\Projects\Support\VTStoredProcedureTest2\bin\Release\VTStoredProcedureTest2.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE ASSEMBLY MyXmlSerializers
FROM
‘C:\Users\Vadim\Documents\Visual Studio 2010\Projects\Support\VTStoredProcedureTest2\bin\Release\VTStoredProcedureTest2.XmlSerializers.dll’
WITH PERMISSION_SET = SAFE;
GO

Once this is done, yo

u’ll see the 2 new assemblies in the NW2010DB database:

So the assemblies are pointed to by the database and now we want to create a Stored Procedure this is in essence a pointer to the StoredProcedure we created in the assembly.

 This can be done with the following SQL Query:

CREATE PROCEDURE spStartWorkflow
AS EXTERNAL NAME MyStoredProcedures.StoredProcedures.spStartNintexWorkflow;

GOThis is telling SQL to create a Stored Procedure that is in fact just a pointer to our developed stored procedure named “spStartNintexWorkflow” in our assembly.

When you run this, you’ll find in the Stored Procedures for this database, the icon for this SP looks a little different.

Remember, make sure the List (“SQL Test”) exists on the site, and there is an item with ID (1) and a published Workflow named (“SQL Test Workflow”).

 My Workflow is simple. It’s just a Log in the History List action, that logs the date, time and a simple message.

We can now test the Stored Procedure. Right click on it in SQL Management Studio, and select Execute. It should look like this (or similar):

EXEC [dbo].[spStartWorkflow]

When you run this, you can now look in your List in SharePoint, look at the Workflow History for the item with ID (1), and you should see a logged message.

So that concludes how to get a Nintex Workflow to start, from SQL. Not too hard right? Well ok, it’s a little more complicated than a simple web service call, but at least it’s possible.

There are a number of things I didn’t go in to, like input and output parameters etc, but I’m leaving that up to the reader :).

I can’t end it like this….

Are you ever going to manually start a Stored Procedure? I highly doubt it. So how would you do it automatically?

 Well, you could put the call into another Stored Procedure, or….. how about using a SQL Trigger?

 The following will show a simple trigger that kicks off when an item is updated in a table.

SQL Table

First, I create a table that I’m interested in: tblValues

Next, I’m going to right click on the Triggers node and create a new trigger. The trigger query looks like this:

CREATE TRIGGER triggerTblvalues
ON tblValues
AFTER INSERT
AS
BEGIN — SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
EXEC [dbo].[spStartWorkflow]
END
GO

This trigger is configured for the table “tblValues” and is to run when there is an Insert into that table. When that happens, it will execute my “spStartWorkflow” stored procedure.

Test

Run an Insert query and insert a new entry into that table.

INSERT INTO

[NW2010DB].[dbo].[tblValues] 

([ID] 

,[Name])
VALUES

(1,

‘Wombat’

) 

GO 

A new entry will be added to the table. A trigger will run be hit and this will run our stored procedure. This stored procedure will try to make a web service call to the Nintex Workflow web service, to start a workflow instance.

 We look at our Workflow History and see:

Now, everytime a new item is added to that table, a workflow instance will run.

Conclusion

This just goes to show how you can do some amazing things with all these tools. If was an admin, and had underlings, I’d look into adding a trigger to something like the Nintex Workflow Activities or StringFunction table. Then I could easily design a workflow that built some logic around notifying people when custom actions or inline functions were added to the Nintex Workflow database. Just to make sure my underlings aren’t adding untested functionality to a production system.

 So this opens up a whole new world to how to start a workflow and from where.

I haven’t included the code here, because it’s really straight forward and I’ve outlines all the bits anyone would need.