distributed transaction conundrum

The following statement joins two tables in databases on different
servers
(n.b. rose is the 'linked' server):
select top 10 hhid, c.s21_cust_num
from rose.customer.dbo.customer rc
join customer c
on rc.s21_cust_num = c.s21_cust_num
where c.store_num = 232
When I run it interactively from query analyzer, it executes without
error and produces a result set.
When I create a stored procedure and run it in the SQL debugger, I
receive the following set of DTC errors (which is the same thing that
happens when our program tries to update the table on the 'remote'
server).:
create procedure selectcustomer
as
select top 10 hhid, c.s21_cust_num
from rose.customer.dbo.customer rc
join customer c
on rc.s21_cust_num = c.s21_cust_num
where c.store_num = 232
go
Server: Msg 7391, Level 16, State 1, Procedure selectcustomer, Line 5
[Microsoft][ODBC SQL Server Driver][SQL Server]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].
There is a firewall between the two servers, but it seems strange that
the queries would run interactively but fail when running under
program control.
Is there an explanation for that?| The following statement joins two tables in databases on different
| servers
| (n.b. rose is the 'linked' server):
|
| select top 10 hhid, c.s21_cust_num
| from rose.customer.dbo.customer rc
| join customer c
| on rc.s21_cust_num = c.s21_cust_num
| where c.store_num = 232
|
| When I run it interactively from query analyzer, it executes without
| error and produces a result set.
|
| When I create a stored procedure and run it in the SQL debugger, I
| receive the following set of DTC errors (which is the same thing that
| happens when our program tries to update the table on the 'remote'
| server).:
|
| create procedure selectcustomer
|
| as
|
| select top 10 hhid, c.s21_cust_num
| from rose.customer.dbo.customer rc
| join customer c
| on rc.s21_cust_num = c.s21_cust_num
| where c.store_num = 232
|
| go
|
| Server: Msg 7391, Level 16, State 1, Procedure selectcustomer, Line 5
| [Microsoft][ODBC SQL Server Driver][SQL Server]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].
|
| There is a firewall between the two servers, but it seems strange that
| the queries would run interactively but fail when running under
| program control.
--
This Technet article might help:
HOWTO: Troubleshoot MS DTC Firewall Issues
http://support.microsoft.com/default.aspx?scid=kb;en-us;306843
Cheers,
--
Eric Cárdenas
SQL Server support|||"Eric Cardenas" <ecard@.anonymous.com> wrote in message
news:<MmeQtGrvDHA.3520@.cpmsftngxa07.phx.gbl>...
> This Technet article might help:
> HOWTO: Troubleshoot MS DTC Firewall Issues
> http://support.microsoft.com/default.aspx?scid=kb;en-us;306843
> Cheers,
> --
> Eric Cárdenas
> SQL Server support
Been there, and yes, I've had that problem. But I have another problem, too.
I created a linked server pointed to the SQL server on my workstation. In
the following code both tables t1 and t2 are in the same db, but in the
sproc I'm referencing one through a linked server (called SQL01). It runs
from a query analyzer window but fails in the debugger, with the same 7391
error. Can you tell me why does it fail in the debugger?
drop table t1
create table t1
(k1 int identity, nk1 int, att1 char(10), date1 datetime, varchar1 varchar
(30))
drop table t2
create table t2
(nk2 int, att2 char (10), date2 datetime, varchar2 varchar (30))
insert into t1
(nk1, att1, date1, varchar1)
values
(100, 'pot', getdate(), 'this is a test record')
insert into t1
(nk1, att1, date1, varchar1)
values
(200, 'pan', getdate(), 'this is another test record')
go
drop procedure movedata
go
create procedure movedata
as
set xact_abort on
insert into sql01.pos_rawdata.dbo.t2
(nk2, att2, date2)
select nk1, att1, date1 from t1
update rt2
set varchar2 = t1.varchar1
from t1, sql01.pos_rawdata.dbo.t2 rt2 where t1.nk1 = rt2.nk2 and nk1 = 100
go
select * from t2
Thanks for any insights you might have.|||This is just a thought, but doesn't the debugger have to begin a transaction while debugging in order to allow you teh rollback option? If this is the case, then everything you need in order to begin a DISTRIBUTED TRANSACTION needs to be in place. I notice that you included the SET XACT_ABORT ON command, which I know is one of the required conditions. The rest can be found in the BOL.
I hope this helps somehow.
Matthew Bando
BandoM@.(REmove)CSCTechnologies.com
>--Original Message--
>"Eric Cardenas" <ecard@.anonymous.com> wrote in message
>news:<MmeQtGrvDHA.3520@.cpmsftngxa07.phx.gbl>...
>> This Technet article might help:
>> HOWTO: Troubleshoot MS DTC Firewall Issues
>> http://support.microsoft.com/default.aspx?scid=3Dkb;en-
us;306843
>> Cheers,
>> --
>> Eric C=E1rdenas
>> SQL Server support
>Been there, and yes, I've had that problem. But I have another problem, too.
>I created a linked server pointed to the SQL server on my workstation. In
>the following code both tables t1 and t2 are in the same db, but in the
>sproc I'm referencing one through a linked server (called SQL01). It runs
>from a query analyzer window but fails in the debugger, with the same 7391
>error. Can you tell me why does it fail in the debugger?
>drop table t1
>create table t1
>(k1 int identity, nk1 int, att1 char(10), date1 datetime, varchar1 varchar
>(30))
>drop table t2
>create table t2
>(nk2 int, att2 char (10), date2 datetime, varchar2 varchar (30))
>insert into t1
>(nk1, att1, date1, varchar1)
>values
>(100, 'pot', getdate(), 'this is a test record')
>insert into t1
>(nk1, att1, date1, varchar1)
>values
>(200, 'pan', getdate(), 'this is another test record')
>go
>drop procedure movedata
>go
>create procedure movedata
>as
>set xact_abort on
>insert into sql01.pos_rawdata.dbo.t2
>(nk2, att2, date2)
>select nk1, att1, date1 from t1
>update rt2
>set varchar2 =3D t1.varchar1
>from t1, sql01.pos_rawdata.dbo.t2 rt2 where t1.nk1 =3D rt2.nk2 and nk1 =3D 100
>go
>select * from t2
>Thanks for any insights you might have.
>
>.
>|||I think that was a really good call.
"Matthew Bando" <anonymous@.discussions.microsoft.com> wrote in message
news:02af01c3bf4a$bf73fa80$a101280a@.phx.gbl...
This is just a thought, but doesn't the debugger have to
begin a transaction while debugging in order to allow you
teh rollback option? If this is the case, then
everything you need in order to begin a DISTRIBUTED
TRANSACTION needs to be in place. I notice that you
included the SET XACT_ABORT ON command, which I know is
one of the required conditions. The rest can be found in
the BOL.
I hope this helps somehow.
Matthew Bando
BandoM@.(REmove)CSCTechnologies.com