SQL Server 2012 with a linked ADS server using OLDB ver 11.10

[m***@gmail.com]

I have got an SQL Server 2012 connect to an Advantge Server version 11 with OLEDB ver 11.10.

I can run SQL querys on the SQL server and they display/Add to a table on the Advantage Server without a problem.

I am adding the same querys to a trigger but get the following error - The requested operation could not be performed because the OLE DB provider "Advantage OLE DB Provider" for linked server "Advantage" does not support the required transaction type.

Does any know if there is a way of changing the transaction type in triggers?
Or is there another way to fix this problem?

Mark

[Erland Sommarskog]

Post by m***@gmail.com
I have got an SQL Server 2012 connect to an Advantge Server version 11
with OLEDB ver 11.10.
I can run SQL querys on the SQL server and they display/Add to a table
on the Advantage Server without a problem.
I am adding the same querys to a trigger but get the following error -
The requested operation could not be performed because the OLE DB
provider "Advantage OLE DB Provider" for linked server "Advantage" does
not support the required transaction type.
Does any know if there is a way of changing the transaction type in triggers?

When you are in a trigger, you are in a transaction, and by default,
that transaction will be promoted to a distributed transaction. Which
the OLE DB provider for Advantage (a product I'm not familiar with.)

You can use sp_serveroption to set the server property "remote proc
transaction promotion" to false. Now, SQL Server will call Advantage
without a transaction. However, this also means that if the trigger
performs an update on the Advantage server, and this is part of a longer
transaction that later fails, the update on Advantage will not be rolled
back.

[m***@gmail.com]

Post by Erland Sommarskog

Post by m***@gmail.com
I have got an SQL Server 2012 connect to an Advantge Server version 11
with OLEDB ver 11.10.
I can run SQL querys on the SQL server and they display/Add to a table
on the Advantage Server without a problem.
I am adding the same querys to a trigger but get the following error -
The requested operation could not be performed because the OLE DB
provider "Advantage OLE DB Provider" for linked server "Advantage" does
not support the required transaction type.
Does any know if there is a way of changing the transaction type in triggers?

When you are in a trigger, you are in a transaction, and by default,
that transaction will be promoted to a distributed transaction. Which
the OLE DB provider for Advantage (a product I'm not familiar with.)
You can use sp_serveroption to set the server property "remote proc
transaction promotion" to false. Now, SQL Server will call Advantage
without a transaction. However, this also means that if the trigger
performs an update on the Advantage server, and this is part of a longer
transaction that later fails, the update on Advantage will not be rolled
back.
--

Thanks for you help Erland but there is no option of "remote proc
transaction promotion" in server option for the Advantage Linked Server.

Has anyone else got anything i could try.

[Erland Sommarskog]

Post by m***@gmail.com
Thanks for you help Erland but there is no option of "remote proc
transaction promotion" in server option for the Advantage Linked Server.

So what happens if you say:

EXEC sp_serveroption 'SERVER', 'remote prov transaction promotion', false

[m***@gmail.com]

Post by Erland Sommarskog

Post by m***@gmail.com
Thanks for you help Erland but there is no option of "remote proc
transaction promotion" in server option for the Advantage Linked Server.

EXEC sp_serveroption 'SERVER', 'remote prov transaction promotion', false
--

When i run

EXEC sp_serveroption 'ADVANTAGE', 'remote prov transaction promotion', false

i get an error off "An invalid parameter or option was specified for procedure 'sys.sp_serveroption'."

If i run set a system option i know exists with

EXEC sp_serveroption 'ADVANTAGE', 'RPC', FALSE

I get "Query OK" and all works fine

[Erland Sommarskog]

Post by m***@gmail.com
When i run
EXEC sp_serveroption 'ADVANTAGE', 'remote prov transaction promotion', false
i get an error off "An invalid parameter or option was specified for

procedure 'sys.sp_serveroption'."
Sorry, there was a typo. Try:

EXEC sp_serveroption 'SERVER', 'remote proc transaction promotion', false

[m***@gmail.com]

Post by m***@gmail.com

Post by m***@gmail.com
When i run
EXEC sp_serveroption 'ADVANTAGE', 'remote prov transaction promotion', false
i get an error off "An invalid parameter or option was specified for

procedure 'sys.sp_serveroption'."
EXEC sp_serveroption 'SERVER', 'remote proc transaction promotion', false
--
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Well spotted I have now entered the correct line of

EXEC sp_serveroption 'ADVANTAGE', 'remote proc transaction promotion', False

it say "Query OK"

When i run the trigger i still get the error as above

Thanks for your help.

[Erland Sommarskog]

Post by m***@gmail.com
Well spotted I have now entered the correct line of
EXEC sp_serveroption 'ADVANTAGE', 'remote proc transaction promotion', >
False
it say "Query OK"
When i run the trigger i still get the error as above

It seems then that you will need to find a different solution than the
trigger.

I can't say what is going on. It seems silly that SQL Server tries a
distributed transaction when the provider does not support it, and it
when it has been told not to. Possibly, the provider is exposing
inconsistent information. Since I am not all acquainted with this product,
it's hard to tell.

[m***@gmail.com]

Post by Erland Sommarskog

Post by m***@gmail.com
Well spotted I have now entered the correct line of
EXEC sp_serveroption 'ADVANTAGE', 'remote proc transaction promotion', >
False
it say "Query OK"
When i run the trigger i still get the error as above

It seems then that you will need to find a different solution than the
trigger.
I can't say what is going on. It seems silly that SQL Server tries a
distributed transaction when the provider does not support it, and it
when it has been told not to. Possibly, the provider is exposing
inconsistent information. Since I am not all acquainted with this product,
it's hard to tell.
--
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for your help.

[m***@gmail.com]

Post by m***@gmail.com

Post by Erland Sommarskog

Post by m***@gmail.com
Well spotted I have now entered the correct line of
EXEC sp_serveroption 'ADVANTAGE', 'remote proc transaction promotion', >
False
it say "Query OK"
When i run the trigger i still get the error as above

It seems then that you will need to find a different solution than the
trigger.
I can't say what is going on. It seems silly that SQL Server tries a
distributed transaction when the provider does not support it, and it
when it has been told not to. Possibly, the provider is exposing
inconsistent information. Since I am not all acquainted with this product,
it's hard to tell.
--
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for your help.

Do you know another way this could be done as i think i need to find another solution. I have heard you can set up scheduled transaction on SQL Server.

[Erland Sommarskog]

Post by m***@gmail.com
Do you know another way this could be done as i think i need to find
another solution. I have heard you can set up scheduled transaction on
SQL Server.

Then you need to tell us more what you are trying to achieve. That is,
what is this trigger doing, and what is the ultimate purpose to the
Advantage server?