Fill your SQL boots - New Execute SQL action for O365

  • 22 January 2015
  • 6 replies
  • 9 views
Dan_Stoll
Userlevel 7
Badge +11

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.. happy.png

 

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

https://www.linkedin.com/in/stolldan

6 replies

X
Userlevel 5
Badge +12

Huge step forward for O365, way to go!

O
Badge +3

Awesome!

B
Badge +11

Great!

O
Badge +3

Hi Sergio Martínez,

I wouldn't recommend using the "Send an Email" action to view the XML returned because it will show the "u000du000a" (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.

O
Badge +3

Hi Sergio Martínez​,

Yep, I noticed it will add these "u000du000a" (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

A

Hi,


 


Does anyone here know how to make SQL Server accessible from O365? I am struggling on finding a guideline on how to to this in on-premise SQL as most of the post I googled is always targeting Azure SQL.


 


Thank you in advance,


 


Regards,


Argal

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings