August 30, 2005 at 11:02 am
We have been operating under the assumption that the ado object in vb6 wrapped SQL statemtents in a transaction when they are submitted to our SQL server. This seems to have stopped. We are wondering if there are settings that control this that somehow changed. Any ideas?
Example code:
Dim Ado as Connection
...
Ado.Execute "update table1 set x=1, y=2;update table2 set foo=1,bar=2"
We used to see transactions added around that group of statemtents when we looked in the profiler. Now we see no transactions at all.
August 30, 2005 at 8:43 pm
John I'm not sure if this is what you are looking for it comes from ADO my help files
Connection
You gain access from your application to a data source through a connection—the environment necessary for exchanging data. Your application can gain access to a data source directly (sometimes called a two-tier system), or indirectly (sometimes called a three-tier system) through an intermediary, such as Microsoft Internet Information Services (IIS).
The object model embodies the concept of a connection with the Connection object.
A transaction delimits the beginning and end of a series of data access operations that transpire across a connection. ADO ensures that changes to a data source resulting from operations in a transaction either all occur successfully, or not at all.
If you cancel the transaction or one of its operations fails, then the ultimate result will be as if none of the operations in the transaction had occurred. The data source will be as it was before the transaction began.
The object model does not explicitly embody the concept of a transaction, but represents it with a set of Connection object methods.
Mike
August 30, 2005 at 11:57 pm
It's been some time since I used VB6 and ADO, but as I remember there have never been any implicit transactions like you describe them. Executing two statements in one batch does just that, executes a batch. But each statement will be in it's own implicit transaction, unless you also specify begin transaction or use the transaction features of ADO.
August 31, 2005 at 1:03 am
By default SQL Server is set with implicit transactions off.
If you want to have explicit transactions, you can either use the Ado.Connection objects BeginTrans, CommitTrans, and RollbackTrans methods.
Alternatively you can issue a SET IMPLICIT_TRANSACTIONS ON command.
Julian Kuiters
juliankuiters.id.au
August 31, 2005 at 1:39 am
Here is some info from BOL:
mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\acdata.chm::/ac_8_md_06_7j1y.htm
The ODBC API does not support explicit transactions, only autocommit and implicit transactions.
mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\acdata.chm::/ac_8_md_06_2g6r.htm
OLE DB does not have a method to set implicit transaction mode specifically.
ADO does not support implicit transactions. ADO applications use either autocommit mode or explicit transactions.
mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_set-set_7mur.htm
The Microsoft OLE DB Provider for SQL Server and the SQL Server ODBC driver automatically set IMPLICIT_TRANSACTIONS to OFF when connecting.
mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_set-set_1vqe.htm
The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server automatically set ANSI_DEFAULTS to ON when connecting. The driver and Provider then set CURSOR_CLOSE_ON_COMMIT and IMPLICIT_TRANSACTIONS to OFF. The OFF settings for SET CURSOR_CLOSE_ON_COMMIT and SET IMPLICIT_TRANSACTIONS can be configured in ODBC data sources, in ODBC connection attributes, or in OLE DB connection properties that are set in the application before connecting to SQL Server.
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply