Using Transactions with SOAP Sessions
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
A transaction is frequently made up of a series of batches submitted one after and that require ordered execution. If any batch in the transaction is not completed, the transaction can be rolled back, undoing changes made by earlier batches within the scope of the transaction and restoring the affected data to its previous state.
In traditional SQL data access, the underlying network connection is relied upon to service all batches in a multibatched transaction as it is processed and executed. For example, consider the following as an example of the relationship between a SQL Server connection and three different transactions:
SQL connection(1)
--> SQL batch(1)
--> transaction(1)
--> SQL batch(2)
--> transaction(2a)
--> SQL batch(3)
--> transaction(2b), transaction(3)
Note that transactions (1) and (3) execute and are contained and committed within the same batch; however, transaction (2) extends across batches (2) and (3). The underlying connection provides the context for making sure that the transaction executes all batches in the order required. With HTTP SOAP access, transaction processing cannot be performed assuming the context of a single underlying network connection. Therefore, to support processing of multibatched transactions, a single SOAP session serves the same purpose. For example, the following code shows how the same pattern of batches and transactions might execute under HTTP SOAP access.
SOAP session(1)
--> SQL batch(1)
--> transaction(1)
--> SQL batch(2)
--> transaction(2a)
--> SQL batch(3)
--> transaction(2b), transaction(3)
As long as the same SOAP session (1) remains active, each batch can be executed in separate SOAP request/response message pairs by using it as the underlying context.
How SOAP-based Transactions Are Managed by SQL Server
SQL transactions are initiated at the instance of SQL Server when a transaction state change occurs. This can be caused by any one of the following events occurring because of the server processing a SOAP request from a client:
Begin Transaction
Commit Transaction
Rollback Transaction
DTC Enlist in Transaction
DTC Defect from Transaction
By default, the server operates in autocommit transaction mode. This behavior assumes a fairly simple one-to-one, batch-to-transaction ratio and does not return any transaction information (headers, descriptors) to the client.
Autocommit transaction mode could be sufficient for transactions (1) and (3) in the previous example. However, transaction (2) in that example required more than one batch and therefore would require manually managing the transaction.
Managing Transactions Manually
To manually manage transaction commitment and rollback, the SOAP client must set the sqloptions:environmentChangeNotification option before initiating the SOAP session, and within that header set the value of its transactionBoundary attribute to true, as shown in the following example SOAP request message example:
<SOAP-ENV:Envelope xmlns:SOAP-ENV="https://schemas.xmlsoap.org/soap/envelope/"
xmlns:sql="https://schemas.microsoft.com/sqlserver/2004/SOAP"
xmlns:xsi="http://www.w3.org/2004/XMLSchema-instance"
xmlns:sqlparam="https://schemas.microsoft.com/sqlserver/2004/sqltypes/SqlParameter"
xmlns:sqlsoaptypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes"
xmlns:sqloptions="https://schemas.microsoft.com/sqlserver/2004/SOAP/Options">
<SOAP-ENV:Header>
<sqloptions:environmentChangeNotifications transactionBoundary="true" />
<sqloptions:sqlSession initiate="true" timeout="60"/>
</SOAP-ENV:Header>
<SOAP-ENV:Body>
<sql:sqlbatch>
<sql:BatchCommands>
USE master
BEGIN TRANSACTION
CREATE TABLE session_table (col1 int);
</sql:BatchCommands>
</sql:sqlbatch>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
This informs the server to disable for autocommit transaction mode for the current session. The server will then send a SOAP response similar to the following in which the session is identified (jGqn3/X73EGHjFxZ12zovw==) and a SqlTransaction value is returned that confirms a BEGIN TRANSACTION event at the server and a transaction descriptor (AQAAADMAAAA=) for the client to use in subsequent SOAP requests that will be part of the same transaction.
<SOAP-ENV:Envelope xml:space="preserve"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:SOAP-ENV="https://schemas.xmlsoap.org/soap/envelope/"
xmlns:sql="https://schemas.microsoft.com/sqlserver/2004/SOAP"
xmlns:sqlsoaptypes="https://schemas.microsoft.com/sqlserver/2004/SOAP/types"
xmlns:sqlrowcount="https://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlRowCount" xmlns:sqlmessage="https://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlMessage" xmlns:sqlresultstream="https://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlResultStream" xmlns:sqltransaction="https://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlTransaction" xmlns:sqltypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes">
<SOAP-ENV:Header xmlns:sqloptions="https://schemas.microsoft.com/sqlserver/2004/SOAP/Options">
<sqloptions:sqlSession sessionId="jGqn3/X73EGHjFxZ12zovw==" timeout="1200">
</sqloptions:sqlSession>
</SOAP-ENV:Header>
<SOAP-ENV:Body>
<sql:sqlbatchResponse>
<sql:sqlbatchResult>
<sqlresultstream:SqlTransaction xsi:type="sqltransaction:SqlTransaction">
<sqltransaction:Descriptor>AQAAADMAAAA=</sqltransaction:Descriptor>
<sqltransaction:Type>Begin</sqltransaction:Type>
</sqlresultstream:SqlTransaction>
</sql:sqlbatchResult>
</sql:sqlbatchResponse>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
The client will then be able to manually join the transaction in subsequent SOAP requests by using the same session ID and transactional descriptor that the server returned in the previous response. This is shown in the following example.
<SOAP-ENV:Envelope xmlns:SOAP-ENV="https://schemas.xmlsoap.org/soap/envelope/"
xmlns:sql="https://schemas.microsoft.com/sqlserver/2004/SOAP"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:sqlparam="https://schemas.microsoft.com/sqlserver/2004/sqltypes/SqlParameter"
xmlns:sqlsoaptypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes"
xmlns:sqloptions="https://schemas.microsoft.com/sqlserver/2004/SOAP/Options">
<SOAP-ENV:Header>
<sqloptions:sqlSession sessionId="jGqn3/X73EGHjFxZ12zovw==" transactionDescriptor="AQAAADMAAAA="/>
</SOAP-ENV:Header>
<SOAP-ENV:Body>
<sql:sqlbatch>
<sql:BatchCommands>
INSERT INTO session_table values (2005)
COMMIT TRANSACTION
</sql:BatchCommands>
</sql:sqlbatch>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
A SOAP request can explicitly join a transaction only if the batch is executing within the same SOAP session under which the transaction was started. Otherwise, a SOAP fault is returned if any one of the following conditions is true:
A different SOAP session ID is specified.
The SOAP session ID is not specified.
The transaction descriptor is not valid for the current SOAP session.
Only one transaction at a time can be used in the transactionDescriptor attribute for sqlSession headers. To create multiple independent transactions within the same session, you can enlist in the session by using the sqlSession header without specifying the transactionDescriptor attribute. Note that this approach assumes that the client application is keeping track of the different transactionDescriptor values. When multiple independent transactions are active within the same session, there is no difference in joining a transaction, just specify the transactionDescriptor attribute on the sqlSession header in a SOAP request.
Note
To determine the level of nested transactions that are active, you can read and use the value of the Transact-SQL @@TRANCOUNT function.
XSD Schema for sqlTransaction
The following is the XSD schema for the sqlTransaction header that is used in SOAP messages:
<xsd:schema
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
attributeFormDefault="qualified"
elementFormDefault="qualified"
targetNamespace="https://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlTransaction">
<xsd:annotation><xsd:documentation xml:lang="en">
(c) Copyright 2004, Microsoft Corporation

The following schema for Microsoft SQL Server is presented in XML format and is for informational purposes only. Microsoft Corporation ("Microsoft") may have trademarks, copyrights, or other intellectual property rights covering subject matter in the schema.

Microsoft does not make any representation or warranty regarding the schema or any product or item developed based on the schema. The schema is provided to you on an AS IS basis. Microsoft disclaims all express, implied and statutory warranties, including but not limited to the implied warranties of merchantability, fitness for a particular purpose, and freedom from infringement. Without limiting the generality of the foregoing, Microsoft does not make any warranty of any kind that any item developed based on the schema, or any portion of the schema, will not infringe any copyright, patent, trade secret, or other intellectual property right of any person or entity in any country. It is your responsibility to seek licenses for such intellectual property rights where appropriate.

MICROSOFT SHALL NOT BE LIABLE FOR ANY DAMAGES OF ANY KIND ARISING OUT OF OR IN CONNECTION WITH THE USE OF THE SCHEMA, INCLUDING WITHOUT LIMITATION, ANY DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL (INCLUDING ANY LOST PROFITS), PUNITIVE OR SPECIAL DAMAGES, WHETHER OR NOT MICROSOFT HAS BEEN ADVISED OF SUCH DAMAGES.
</xsd:documentation></xsd:annotation>
<xsd:complexType name="SqlTransaction">
<xsd:sequence minOccurs="1" maxOccurs="1">
<xsd:element name="Descriptor" type="xsd:base64Binary" />
<xsd:element name="Type">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:enumeration value="Begin"/>
<xsd:enumeration value="Commit"/>
<xsd:enumeration value="Rollback"/>
<xsd:enumeration value="EnlistDTC"/>
<xsd:enumeration value="Defect"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
The XSI type for an SQL Transaction is xsi:type="typesNs:SqlTransaction", where typesNs is bound to the https://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlTransaction namespace.
See Also