SQL Provider:: ExecSQL

Passes SQL and ADO commands to an OLE DB provider on a database server. Binds input and output parameters to the SQL command and returns an XML response. Used by Microsoft Provisioning Framework (MPF).

Arguments

The following table describes the XML schema elements and attributes. Unless otherwise indicated, the data type is string.

Element Description, relationships, and attributes

command

Description:
Command string for the SQL statement.

Whenever the SQL statement contains characters such as "<" and ">" whose SQL usage conflicts with the XML parser, encapsulate the command string in an XML CDATA node. Alternately, you can replace the problematic characters with their corresponding escape sequences, but the resulting code will be harder to read.

If the SQL statement has parameters, they are represented by param nodes that follow the command node. For calls to stored procedures, if a param node is not specified, ExecSQL still passes the parameter but assumes it takes a default value. Otherwise, every parameter in the statement has a param node.

The SQL Provider supports two types of parameter matching: by order and by name. In the following examples, the SQL parameters are simply embedded in the executeData node. In actual procedures, these parameters can be derived from XSL transformations.

Matching by order
Every SQL parameter is represented by a question mark and a corresponding unnamed param node. In the following example, the state parameter binds to the first param node and the au_lname parameter binds to the second param node.
<executeData> <connect Provider="SQLOLEDB" Server="(local)" Database="pubs" Trusted_Connection="yes" /> <command> <![CDATA[ SELECT au_fname, au_lname, phone AS Telephone FROM authors WHERE state=? and au_lname<>? ]]> </command> <param>CA</param> <param>McBadden</param> </executeData>
Matching by name
Every SQL parameter is represented by a named mark and a corresponding named param node. This example is the same as the previous one except that matching is now by name.
<executeData> <connect Provider="SQLOLEDB" Server="(local)" Database="pubs" Trusted_Connection="yes" /> <command> <![CDATA[ SELECT au_fname, au_lname, phone AS Telephone FROM authors WHERE state=@state and au_lname<>@lname ]]> </command> <param name="state">CA</param> <param name="lname">McBadden</param> </executeData>

Parent:
executeData

Attributes:

param	Optional. Enumeration that specifies how ExecSQL executes parameter matching: "auto" (default), "named", "unnamed", or "none". For "auto", it automatically binds all parameters. For "named", it binds only named (@) parameters. For "unnamed", it binds only ordered ("?") parameters. For "none", it binds no parameters.
transactional	Optional. Boolean that identifies whether the SQL command is non-transactional. Some commands such as CREATE DATABASE and DROP TABLE are not transactional; that is, they cannot be reversed by a rollback if there is a failure. Including the transactional attribute with a value of "0" enables you to execute such commands.

connect

Description:
ADO connection parameters required for communication with the SQL database. Most commonly, these parameters are represented in XML with a param node for every property/value pair, as in the following example:

			<connect>
			<param name="Provider">sqloledb</param>
			<param name="Server">myserver</param>
			<param name="Database">pubs</param>
			<param name="Trusted_Connection">yes</param>
			</connect>

ExecSQL concatenates these parameters into the ADO connect string as follows:

Provider=sqloledb;Server=myserver;Database=pubs;Trusted_Connection=yes

Alternately, you can represent some parameters simply as attributes of the connect node. The following example is functionally equivalent to the previous example:

<connect Provider="sqloledb" Server="myserver" Database="pubs" Trusted_Connection="yes"/>

The first format supports all attribute names and is the more common usage. The second can be used only with the ADO parameters that are defined in the input schema for ExecSQL. (See the list following "Atributes.") Both formats can be used in the same request.

Parent:
executeData

Child:
param (minOccurs="0" maxOccurs="*")

Attributes:

The following ADO attributes are defined in the input schema for ExecSQL:

Note These are the only attributes that can be specified as attributes of connect. Any other ADO attributes must be specified as \connect\param nodes.

Provider	Optional. Name of the OLE DB provider to use for the connection.
Driver	Optional. Name of the SQL Server ODBC driver.
Server	Optional. Name of the SQL Server for the connection.
Database	Optional. Name of the SQL database for the connection.
DSN	Optional. Name of an existing ODBC user or system data source.
FileDSN	Optional. Name of an existing ODBC file data source.
URL	Optional. URL of the database server for the connection.
uid	Optional. SQL Server logon account. Not required for Microsoft® Windows® authentication.
pwd	Optional. Password for the SQL Server logon account specified in uid. Not required if the logon has a NULL password or if Trusted_Connection is Yes.
Password	Optional. Same as pwd (password attribute varies by provider).
Trusted_Connection	Enumeration that specifies how the ODBC driver performs logon validation. Values are yes or no (default). If yes, the ODBC driver uses Windows authentication and the uid and pwd attributes are optional. If no, ODBC driver uses the SQL Server username and password, and uid and pwd are required.

Attributes required for a particular usage vary depending on the requirements of the specific OLE DB driver. For more
information, see the ADO documentation.

One ADO attribute, Maximum Rows, corresponds to the SQL Provider's MaxRows registry property, which defines the maximum number of rows that can be returned for a recordset from a SQL table. Specifying a new value for Maximum Rows enables you to override the default MaxRows value for a specific SQL command, as in the following example:

<param name="Maximum Rows">value</param>

executeData

Description:
Encapsulates the procedure's input and output data.

Children:
command (minOccurs="1" maxOccurs="1")
connect (minOccurs="1" maxOccurs="1")
param (minOccurs="0" maxOccurs="*")
timeout (minOccurs="0" maxOccurs="1")

null

Description:
NULL value for a parameter. Cannot be nested in a connect node or used in a param node that already includes a non-null value.

Parent:
param

param

Description:
Parameter for the SQL statement.

Parent:
connect, executeData

Child:
null (minOccurs="0" maxOccurs="1")

Attributes:

name

Name of the parameter. Used for SQL queries involving matching by name. Not required for parameter matching by number.

type

Enumeration that specifies parameter type for calls to stored procedures. Values are "input" (default), "output", "inout", or "return". Not used if param is a child of connect.

explicitNulls

Boolean that determines whether SQL Provider distinguishes between empty strings and NULL values. Examples:

<param type="input" explicitNulls="0"></param>	Passes NULL.
<param type="input" explicitNulls="1"></param>	Passes empty string.
<param type="input" explicitNulls=".."><null/></param>	Ignores explicitNulls setting and passes NULL.
<param type="output" explicitNulls="0"></param>	Returns empty string regardless of whether returned string is empty string or NULL.
<param type="output" explicitNulls="1"></param>	Returns empty string for empty string and <null/> node for NULL.

timeout

Description:
Timeouts for database connection and command execution. If not specified, the default ADO values are used: 15 seconds for connecting and 30 seconds for command execution. Normally, the default values are suitable.

Parent:
executeData

Attributes:

connect	Optional. Connection timeout in seconds.
execute	Optional. Command execution timeout in seconds.

Remarks

Schema Definition

Input

		


		<executeData>1..1
		<connect>1..1
		<param name="..">0..unbounded</param>
		</connect>
		<timeout connect="..." execute=".."/>0..1
		<command param=".." transactional = "..">1..1</command>
		<param name=".." type=".." explicitNulls="..">0..unbounded
		<null/>0..1
		</param>
		</executeData>

Sample Code

Applies To