Menu
SharePoint Workflow Extensions Kit

Execute SQL Script to Read Data

This activity executes SQL statements from within SharePoint Designer workflows and returns the result back to workflow.

Note: Default is MS SQL Server (System.Data.SqlClient) provider. You can use other providers like ODBC, Oracle, etc.

Activity supports multiple batches separated by GO command. GO is not a Transact-SQL statement; it is a command recognized by the activity. The activity interprets GO as a signal that should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.

To return result from the SQL script to a workflow you should fill the output variable value.

Working with Parameter Placeholders

The syntax for parameter placeholders depends on the data provider. The .NET Framework data providers handle naming and specifying parameters and parameter placeholders differently. This syntax is customized to a specific data provider. The syntax for common data providers is described in the following table.

Data provider Parameter naming syntax
System.Data.SqlClient Uses output parameter in the format @output_result.
System.Data.OleDb Uses positional parameter markers indicated by a question mark (?).
System.Data.Odbc Uses positional parameter markers indicated by a question mark (?).
System.Data.OracleClient Uses output parameter in the format :output_result (or output_result).

Example: Sql script for System.Data.SqlClient provider

GO

declare @var varchar(10)

set @var1 = 'sample'

set @output_result = @var1

GO

Example: Sql script for System.Data.OleDb provider

GO

declare @var1 varchar(10)

set @var1 = 'test'

set ? = @var1

GO

SharePoint Workflow Designer Phrase

Execute SQL Script this sql script using this provider name, this connection string and store result in Variable:variable

Parameters

Parameter Description
this sql script SQL statements. Supports multiple batches separated by GO command.
this provider name The .NET ADO Provider. Default is MS SQL Server (System.Data.SqlClient). You can use other providers like • “System.Data.Odbc” - Odbc Data Provider • "System.Data.OleDb"- OleDb Data Provider • “System.Data.OracleClient" - OracleClient Data Provider • "System.Data.SqlClient" - SqlClient Data Provider • "System.Data.SqlServerCe.3.5" - Microsoft SQL Server Compact Data Provider • Etc Additional information about providers you can find here http://msdn.microsoft.com/en-us/library/dd0w4a2z.aspx
this connection string The connection string, e.g. “Data Source=localhost\SQLExpress;Initial Catalog=test;Integrated Security=SSPI”
Variable:variable Gets or sets the output result.