Distrubuted Transactions, VB6 & ADO

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Distrubuted Transactions, VB6 & ADO

Author

Topic

mlrp
Starting Member

2 Posts

Posted - 2002-03-18 : 04:37:45

Working with SQL Server 2000, I know how to perform distributed transactions in native SQL .....

SET XACT_ABORT ON
Begin Distributed Transaction

But I need to do this through VB/ADO and ADO doesn't have a BeginDistributedTransaction method.

MSDN says that I "must use the following API to enlist an MS DTC transation:

HRESULT IRepositoryTransaction2::JoinTransaction([in]VARIANT sVArTxn);

where sVArTxn is an iUnknown pointer to the distributed transaction coordinator."

Could anybody explain what this means and how I would actually deploy this solution? I was hoping for an example or some further information but those 3 lines are all I can find on the subject - there's plenty of C++ examples but I'm not that clever

.

Thanks

This is what I've found on MSDN .....
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/repospr/rpmanagingtrans_50s2.asp[/url]

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-18 : 16:27:07

You can enlist the DTC automatically if you're using COM+. You could manage the communication to/from the DTC yourself, but that's quite a bit of work.

setBasedIsTheTruepath
<O>

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-18 : 16:37:46

With VB6 you have two options.

Transaction control with ADO or with TSQL

ADO


Set Conn = New ADODB.Connection
Conn.Open strConnection

Conn.BeginTrans
Conn.Execute("INSERT INTO myTable DEFAULT VALUES;")
Conn.CommitTrans

Conn.Close
'you can also use Conn.RollbackTrans if you want to roll your transaction back

T-SQL


Set Conn = New ADODB.Connection
Conn.Open strConnection

Conn.Execute("BEGIN TRANSACTION")
Conn.Execute("INSERT INTO myTable DEFAULT VALUES;")
Conn.Execute("COMMIT TRANSACTION")

Conn.Close
'you can also use Conn.Execute("ROLLBACK TRANSACTION") if you want to roll your transaction back

Take a look at http://support.microsoft.com/default.aspx?scid=kb;EN-US;q198024

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-19 : 08:55:02

yakoo,
i think mlrp's issue is distributed transactions, using the DTC as the third party coordinator, as opposed to a SQL Server-only transactions created from ADO/T-SQL.

An example would be a SQL Server transaction in combination with an MSMQ transactional message send.

setBasedIsTheTruepath
<O>

mlrp
Starting Member

2 Posts

Posted - 2002-03-19 : 10:18:39

Yakoo -

I haven't tried using the conn.execute method, but I will ASAP.

However, I do know that simply using conn.BeginTrans won't work with a distributed database. Inserts into the locally connected database work OK but as soon as I insert into the "remote" database (attached as a linked server) ADO comes back saying that it does not support such an operation.

I am only using VB, ADO and SQL Server 2000 but the database is distributed across 2 servers (sometimes called Active-Active).

Thanks for your input - the more the merrier.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-19 : 11:47:20

You should start from the beginning here because the terminology you're using leads to confusion with your readers.

when you say:

quote:
but the database is distributed across 2 servers (sometimes called Active-Active).

this is misleading because 'Active/Active' refers to a clustering configuration of server hardware. There is still only one logical server to support the one database. For the purposes of your question the cluster configuration is irrelevant.

If you are executing SQL Server only transactions, then 'BEGIN TRANSACTION' should work fine, as SQL Server will automatically enlist the DTC for you.

setBasedIsTheTruepath
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources