Execute SQL Action (Advanced SharePoint Designer 2010 Workflow Actions)

Execute a SQL statement from within a SharePoint Designer 2010 Workflow. The action and its source can be downloaded here.

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

Configure the Action

• 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}”

Example

Simple insert statement.

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)

Wiki Documentation