Linked Server & XACT_ABORT

Is there anyway to set XACT_ABORT ON for a linked server? We have a linked
server setup between SQL 2000 and DB2. To use transactions we need to turn
XACT_ABORT on in every single stored procedure. Is there a way to turn it on
at the linked server level?
Thanks.
Paula.. If an application has a local transaction and the option
REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
escalates the local transaction to a distributed transaction.
This can be set via sp_configure I beleive... I have never tried this, but
perhaps it is worth a few minutes of your time..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> Is there anyway to set XACT_ABORT ON for a linked server? We have a
linked
> server setup between SQL 2000 and DB2. To use transactions we need to
turn
> XACT_ABORT on in every single stored procedure. Is there a way to turn it
on
> at the linked server level?
> Thanks.
> Paul
>|||Thank you for the reply. Unfortunately that did not work. I ran the
following command to turn that option on:
EXEC sp_configure 'remote proc trans', 1
I ran the following statements:
BEGIN TRAN
UPDATE D SET
D.Location = 999
FROM LS..DB2TDBM.DEPARTMENT D
WHERE D.ADMRDEPT = 'E01'
COMMIT TRAN
And got the following error, which is the same error when the Remote Proc
Trans is truned off:
"Unable to start a nested transaction for OLE DB provider 'IBMDADB2'. A
nested transaction was required because the XACT_ABORT option was set to OFF."
Any ideas?
Thanks.
Paul
"Wayne Snyder" wrote:
>
> a.. If an application has a local transaction and the option
> REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
> escalates the local transaction to a distributed transaction.
> This can be set via sp_configure I beleive... I have never tried this, but
> perhaps it is worth a few minutes of your time..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> >
> > Is there anyway to set XACT_ABORT ON for a linked server? We have a
> linked
> > server setup between SQL 2000 and DB2. To use transactions we need to
> turn
> > XACT_ABORT on in every single stored procedure. Is there a way to turn it
> on
> > at the linked server level?
> >
> > Thanks.
> >
> > Paul
> >
> >
>
>