This project is read-only.

Execute SQL Action

Execute a SQL statement from within SharePoint Designer 2010 Workflow.

1. Add “Execute SQL” Action to a SharePoint Designer 2010 Workflow

image

2. Configure the Action

image

SQL: The SQL statement that you want to execute. You could also use stored procedures e.g. “EXEC [test_Proc] @var1=N’test’ ”-You can use two special tokens in the statement that can not be inserted via the UI: [WebUrl] and [WorkflowInstanceId].

Provider: The .NET ADO Provider. Default is MS SQL Server (System.Data.SqlClient). You can use other providers like ODBC, Oracle…

Connection: The connection string e.g. “Server=localhost\SQLExpress;Database=test;User ID=sa;Password=P@ssw0rd”. Integrated Security is for security reasons not supported! You could either specify the password  in clear text or encrypted like described in [Encrypt Passwords for SharePoint Designer Workflow Actions].

Result: The first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

AppId: The The Secure Store Application ID. The Secure Store App have to define 2 Fields. One of type “User Name” and one of type “Password”. The Field Name doesn’t matter, important is the Field Type! You have to map the credentials of the SharePoint Service Accounts (AppPool/owstimer.exe). The secure store option will only works with SharePoint Server not with Foundation! In order to use the credentials in the Connection String you can use the token {0} for the User Name and {1} for the password. E.g. “Server=localhost;Database=test;User ID={0};Password={1}”

3. Example

Simple insert statement.

image

Configuration of “Execute SQL”
  • SQL: INSERT INTO MyTable (Column1, Column2) VALUES ([%Current Items:ID%],’[%Current Items:Title%]’)
  • Provider: System.Data.SqlClient
  • Connection String: Server=localhost; Database=MyDatabase;User ID={0}; Password={1}
  • Secure Store AppId: DBCred ({0} in the connection string will be replaced with User Name and {1} with Password defined in the Secure Store App)
  • Output: Workflow Variable “Result” (will return 0)

 

Last edited Jan 15, 2011 at 9:42 PM by cglessner, version 2

Comments

chris_ofienbor Dec 19, 2016 at 1:40 PM 
Thanks for this post.
Can this work with sharepoint designer 2013?

aardvrk Nov 20, 2013 at 9:37 PM 
Hello, Is there a way to use windows Authentication or Secure Store Credentials?
This uses SQL credentials correct?

f_hamed Sep 1, 2013 at 1:32 PM 
Hi,
Is there any string limitation for SQL Command?