Re: Multi-Database Transactions

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 03/08/05 

Date: Tue, 8 Mar 2005 08:45:31 -0800

SQL Server has supported the concept of cross-database (or even cross
server) transactions for ages. It implements this behind the scenes by using
"2-phase commit". Generally, when I want to do this, I do so in a Stored
Procedure that links to the other server (data source) and database. I would
see books online for information on linked servers and cross-server
transactions.

hth 

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"David Wimbush" <david_wimbush@hotmail.com> wrote in message 
news:1110294831.018869.212910@z14g2000cwz.googlegroups.com...
> I've been looking into adding transaction capabilities into my object
> model framework. I always understood that, with SQL Server, a
> transaction could only cover one database, regardless of whether the
> transaction was done in a stored procedure or ADO ('classic ADO' or
> ADO.Net). But when I started reading in detail it got vague. For
> example, people were talking about data _sources_ instead of databases.
> So I did some testing and I can do a transaction in either SQL or ADO
> that successfully commits or rolls back updates to two databases. I'm
> using a single SQL Server 2000 Standard Edition with multiple databases
> on it and .Net 1.1.
>
> Before I get carried away, can anybody tell me the facts or point me to
> some definitive information about this, please? Thanks.
>

Relevant Pages

  • Re: Locking and Delay in a Bottleneck
    ... Invoices, Purchasing, etc) are generating a journal voucher accompanying ... the saving transaction, this journal voucher is having a header table ... number in a table whereupon you commit. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.programming)
  • Re: Locking and Delay in a Bottleneck
    ... requirement that there be no gaps. ... the saving transaction, this journal voucher is having a header table ... number in a table whereupon you commit. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL 7 vs. 2000 issue -trigger and nulls
    ... >We're having trouble with a trigger updating some tables. ... course be locked by the current transaction, ... locking data and updating rows when the COMMIT inside the trigger is ... I seriously hope that SQL Server 7.0 simply disregarded these two ...
    (microsoft.public.sqlserver.mseq)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • Multi-Database Transactions
    ... I've been looking into adding transaction capabilities into my object ... transaction was done in a stored procedure or ADO ('classic ADO' or ... people were talking about data _sources_ instead of databases. ... using a single SQL Server 2000 Standard Edition with multiple databases ...
    (microsoft.public.dotnet.framework.adonet)