cancel
Showing results for 
Search instead for 
Did you mean: 

Fill your SQL boots - New Execute SQL action for O365

Workflow Hero
9 8 5,417

One of the most exciting announcements with the latest drop from the product teams this month would have to be "Execute SQL" from O365 no less..
http://help.nintex.com/en-US/O365/Default.htm#O365WorkFlow/Workflow%20Actions/Execute%20SQL.htm

YES, this means you can execute SQL from the SharePoint Online platform to your internally hosted SQL servers. This does have some caveats though. Note: The database must be internet-accessible.

SQL.png

For this post I'm going to connect to my Azure SQL server..

Ok so first of all get your DB setup with some tables.. I used this to get me started. Getting started with SQL Database - Azure

I was then able to use SQL Server Management Studio to connect to the server and check out the tables etc..

Heading into Nintex Workflow for O365, and adding the "Execute SQL" action to the canvas we can get into configuring it.. Now if you are familiar with the "Execute SQL" action with the Nintex Workflow for SharePoint (2007, 2010, 2013) this is pretty much the same to configure.

SQL1.png

What I have done with this test workflow, is setup a custom list and used to state the query I wish to execute. The workflow then goes off and execute my query against the Azure SQL Database specified in my connection string and returns the results.. See below for a short video displaying the magic in real time..

For help with connection strings I highly recommend http://www.connectionstrings.com

8 Comments
Workflow Hero

Huge step forward for O365, way to go!

Workflow Hero

Awesome!

Workflow Hero

Great!

Not applicable

Hi!

I connect to database Access on access web app and retrieve data in a XML results variable (with bad format) and sent it to email:

["\u000d\u000a 1<\/Id>\u000d\u000a true<\/Estado>\u000d\u000a JBR<\/Codigo>\u000d\u000a Nombre<\/Nombre>\u000d\u000a Apellido1<\/Apellidos>\u000d\u000a<\/item>","\u000d\u000a 2<\/Id>\u000d\u000a true<\/Estado>\u000d\u000a MGO<\/Codigo>\u000d\u000a Nombre<\/Nombre>\u000d\u000a Apellido1<\/Apellidos>\u000d\u000a<\/item>","\u000d\u000a 3<\/Id>\u000d\u000a true<\/Estado>\u000d\u000a SOM<\/Codigo>\u000d\u000a Nombre<\/Nombre>\u000d\u000a O<\/Apellidos>\u000d\u000a<\/item>","\u000d\u000a 4<\/Id>\u000d\u000a true<\/Estado>\u000d\u000a DGN<\/Codigo>\u000d\u000a Nombre<\/Nombre>\u000d\u000a Apellido1<\/Apellidos>\u000d\u000a<\/item>","\u000d\u000a 5<\/Id>\u000d\u000a true<\/Estado>\u000d\u000a CBG<\/Codigo>\u000d\u000a Nombre<\/Nombre>\u000d\u000a Apellido1<\/Apellidos>\u000d\u000a<\/item>","\u000d\u000a 6<\/Id>\u000d\u000a true<\/Estado>\u000d\u000a JGARCIA<\/Codigo>\u000d\u000a Jose Nombre<\/Nombre>\u000d\u000a Apellido1<\/Apellidos>\u000d\u000a<\/item>","\u000d\u000a 7<\/Id>\u000d\u000a false<\/Estado>\u000d\u000a PPM<\/Codigo>\u000d\u000a Nombre<\/Nombre>\u000d\u000a Apellido1<\/Apellidos>\u000d\u000a<\/item>","\u000d\u000a 8<\/Id>\u000d\u000a false<\/Estado>\u000d\u000a ECM<\/Codigo>\u000d\u000a Nombre<\/Nombre>\u000d\u000a Apellido1<\/Apellidos>\u000d\u000a<\/item>","\u000d\u000a 9<\/Id>\u000d\u000a true<\/Estado>\u000d\u000a JMC<\/Codigo>\u000d\u000a Nombre<\/Nombre>\u000d\u000a Apellido1<\/Apellidos>\u000d\u000a<\/item>"]

The idea was to consult with the Nintex XML component "Query XML"

Why do returns the XML data in that format? a bug ? I have no idea..

Thank you!!

Not applicable

Great !

Workflow Hero

Hi Sergio Martínez,

I wouldn't recommend using the "Send an Email" action to view the XML returned because it will show the "\u000d\u000a" (line separator) characters in the email.

You can view the XML by uploading each of the XML into a document library within the site (example similar to query list)

If already know the columns that will be returned from the SQL statement executed, the XML will be returned in this structure:

<item>

  <ColumnName>ColumnValue</ColumnName>

  ...

  ...

  ...

</item>

For example, if I have a Products table with ID & Name fields, running the "select * from dbo.Products" will return each of the XML as:

<item>

  <ID>1</ID>

  <Name>Book</Name>

</item>

So in the Query XML action you will specify the path as "item/ID" to retrieve the product ID, and "item/Name" to retrieve the product name.

Not applicable

Hello Danny,

But if I make an update in a column in the current list with the content of the variable also insert these characters.

Sin título.png

Thank you!

Workflow Hero

Hi Sergio Martínez​,

Yep, I noticed it will add these "\u000d\u000a" (line separator) if you write the dictionary into a column directly.

The dictionary output returned represents the data in an array of XMLs. So if the query contains 2 results, it will bring back the following XMLs:

XML item 2

<item>

  <ID>1</ID>

  <Name>Book</Name>

</item>

XML item 2

<item>

  <ID>2</ID>

  <Name>Pen</Name>

</item>

These characters will not be visible if the workflow is designed to read each item in the dictionary using the "For Each" action, and within the loop, you can create an item using "Create List Item" to store the each XML returned into a results list for reference.

Below are some screenshots of the design and configurations.

Sample workflow design overview for logging the fields XML

Overview.png

Sample configuration of "For Each" to loop through the XML dictionary

ForEachXml.png

Sample configuration of "Create List Item" to store the result for each field XML, into "Query Results" list where "Output" is set as Multiple lines of text column (plain text).

CreateItemWithCurrentXml.png

Sample configuration of "Query XML" for Name

QueryXmlForName.png