servers and Triggers...

I have a read many of the threads that have to do with linked servers and
have not found out how to over come the 7391 error. I have setup up
everything using both openrowset and openquery and the same error comes back
when I have a trigger setup to update a Pervasive table from MSSQL. However
if I run those queries in the query anaylzer they run fine.
Here is the openrowset query:
update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer Master"')
set UDFREF_23='ABC' where custid_23='2400047'
and here is the openquery query:
update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
I have a trigger set up to run the update queries but it fails with the 7391
error.
Why would they work fine in the query analyzer and not from a MSSQL trigger
or stored procedure?
Thank you for any help.Because in Query Analyzer, depending on the environment parameters, you
might not be illiciting a Distributed Transaction. However, in a stored
procedure, and most assuradely a trigger, you will have to begin a
distributed transaction unless you explicitly override the creation of one.
Sincerely,
Anthony Thomas
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
I have a read many of the threads that have to do with linked servers and
have not found out how to over come the 7391 error. I have setup up
everything using both openrowset and openquery and the same error comes back
when I have a trigger setup to update a Pervasive table from MSSQL. However
if I run those queries in the query anaylzer they run fine.
Here is the openrowset query:
update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer Master"')
set UDFREF_23='ABC' where custid_23='2400047'
and here is the openquery query:
update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
I have a trigger set up to run the update queries but it fails with the 7391
error.
Why would they work fine in the query analyzer and not from a MSSQL trigger
or stored procedure?
Thank you for any help.|||Anthony:
So what I use to override the creation of one?
MCR
"AnthonyThomas" wrote:
> Because in Query Analyzer, depending on the environment parameters, you
> might not be illiciting a Distributed Transaction. However, in a stored
> procedure, and most assuradely a trigger, you will have to begin a
> distributed transaction unless you explicitly override the creation of one.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
> I have a read many of the threads that have to do with linked servers and
> have not found out how to over come the 7391 error. I have setup up
> everything using both openrowset and openquery and the same error comes back
> when I have a trigger setup to update a Pervasive table from MSSQL. However
> if I run those queries in the query anaylzer they run fine.
> Here is the openrowset query:
> update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer Master"')
> set UDFREF_23='ABC' where custid_23='2400047'
>
> and here is the openquery query:
> update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
> Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
> I have a trigger set up to run the update queries but it fails with the 7391
> error.
> Why would they work fine in the query analyzer and not from a MSSQL trigger
> or stored procedure?
> Thank you for any help.
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_07D2_01C4F23F.A803CFA0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Explicitly set your environment parameters:
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
There is another one for remote procedure transactions that you can set off
as well. You'll need to look it up.
Also, make sure you do not create any locks on the remote read. Depending
on the platform, you need to set READ UNCOMMITTED isolation levels on your
sources.
The other option is to verify that the linked_server is configured properly
for distributed transactions.
However, I suspect that this worked in QA for you because you did not have
IMPLICIT_TRANSACTIONS set on. These are configurable from the interface
under Connection Properties or Current Query Properties.
Sincerely,
Anthony Thomas
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:6281615A-F7FA-4EAB-BFEA-D0E92C52C3EF@.microsoft.com...
Anthony:
So what I use to override the creation of one?
MCR
"AnthonyThomas" wrote:
> Because in Query Analyzer, depending on the environment parameters, you
> might not be illiciting a Distributed Transaction. However, in a stored
> procedure, and most assuradely a trigger, you will have to begin a
> distributed transaction unless you explicitly override the creation of
one.
>
> Sincerely,
>
>
> Anthony Thomas
>
>
> --
>
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:82AD4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...
> I have a read many of the threads that have to do with linked servers
and
> have not found out how to over come the 7391 error. I have setup up
> everything using both openrowset and openquery and the same error comes
back
> when I have a trigger setup to update a Pervasive table from MSSQL.
However
> if I run those queries in the query anaylzer they run fine.
>
> Here is the openrowset query:
> update openrowset('MSDASQL','DSN=MAXDAT','select * from "Customer
Master"')
> set UDFREF_23='ABC' where custid_23='2400047'
>
>
> and here is the openquery query:
> update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer
> Master"') set UDFREF_23='ZZZ' where custid_23='2400047'
>
> I have a trigger set up to run the update queries but it fails with the
7391
> error.
>
> Why would they work fine in the query analyzer and not from a MSSQL
trigger
> or stored procedure?
>
> Thank you for any help.
>
>
>
--=_NextPart_000_07D2_01C4F23F.A803CFA0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Explicitly set your environment parameters:
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS =OFF
There is another one for remote =procedure transactions that you can set off as well. You'll need to look it up.
Also, make sure you do not create any =locks on the remote read. Depending on the platform, you need to set READ UNCOMMITTED isolation levels on your sources.
The other option is to verify that =the linked_server is configured properly for distributed =transactions.
However, I suspect that this worked =in QA for you because you did not have IMPLICIT_TRANSACTIONS set on. These are configurable from the interface under Connection Properties or Current =Query Properties.
Sincerely,
Anthony Thomas
--
"Mike" wrote in message news:628=1615A-F7FA-4EAB-BFEA-D0E92C52C3EF@.microsoft.com...Anthony:So what I use to override the creation of one? =MCR"AnthonyThomas" wrote:> Because in Query Analyzer, depending on the =environment parameters, you> might not be illiciting a Distributed Transaction. However, in a stored> procedure, and most =assuradely a trigger, you will have to begin a> distributed transaction =unless you explicitly override the creation of one.> > =Sincerely,> > > Anthony Thomas> > > -- > = > "Mike" wrote in message> news:82A=D4E62-5FDE-4EB8-B905-4D3D5C5698D7@.microsoft.com...> I have a read many of the threads that have to do with linked servers and> have not found out how to over come the 7391 error. I have =setup up> everything using both openrowset and openquery and the same =error comes back> when I have a trigger setup to update a Pervasive =table from MSSQL. However> if I run those queries in the query =anaylzer they run fine.> > Here is the openrowset query:> =update openrowset('MSDASQL','DSN=3DMAXDAT','select * from "Customer =Master"')> set UDFREF_23=3D'ABC' where custid_23=3D'2400047'> > => and here is the openquery query:> update openquery(MAXTEST,'select custid_23, udfref_23 from "Customer> Master"') set =UDFREF_23=3D'ZZZ' where custid_23=3D'2400047'> > I have a trigger set up =to run the update queries but it fails with the 7391> error.> => Why would they work fine in the query analyzer and not from a MSSQL trigger> or stored procedure?> > Thank you for =any help.> > >

---

--=_NextPart_000_07D2_01C4F23F.A803CFA0--