Distributed Transaction problem

Hi,
We have a procedure that delete the records from a local
database table and then delete the records from another
database table that can be on local or remote machine. I
am testing the procedure with both the databases on same
local machine but getting following error when trying to
execute procedure:
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I have a databaseA on local machine HARV and databaseB
also on HARV
I created a linked server QA1 for databaseB
Following select statement works fine:
select * from qa1.databaseB.dbo.tab1
and following delete statement also works fine:
delete from qa1.databaseB.dbo.tab1 where col1 = 1
BUT when I want to use begin and commit tran it doesn't
worked:
I tried using begin distributed tran but didn't helped
OS is Windows 2000 and standard edition (not win 2003 or
enterprise edition)
MSDTC is running
@.@.servername is not null
There are no triggers on tables
Any feedback will be appreciated?
Thanks
--HarvinderThat is a known limitation of loopback linked servers and DTC
Look up loopback linked server in BOL. Scroll down and read:
--snip--
Loopback linked servers cannot be used in a distributed transaction.
Attempting a distributed query against a loopback linked server from within
a distributed transaction causes an error:
Msg: 3910 Level: 16 State: 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction conte
xt in use by
another session.
--snip--Bummer.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Harvinder" <hs@.metratech.com> wrote in message
news:1259b01c410fd$d1a135b0$a101280a@.phx
.gbl...
> Hi,
> We have a procedure that delete the records from a local
> database table and then delete the records from another
> database table that can be on local or remote machine. I
> am testing the procedure with both the databases on same
> local machine but getting following error when trying to
> execute procedure:
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> I have a databaseA on local machine HARV and databaseB
> also on HARV
> I created a linked server QA1 for databaseB
> Following select statement works fine:
> select * from qa1.databaseB.dbo.tab1
> and following delete statement also works fine:
> delete from qa1.databaseB.dbo.tab1 where col1 = 1
> BUT when I want to use begin and commit tran it doesn't
> worked:
> I tried using begin distributed tran but didn't helped
> OS is Windows 2000 and standard edition (not win 2003 or
> enterprise edition)
> MSDTC is running
> @.@.servername is not null
> There are no triggers on tables
> Any feedback will be appreciated?
> Thanks
> --Harvinder
>