Linked Server and Trigger - Error

[medmed]

Hi all,

i have following problem:
- I created a linked server to a Oracle DB.
- I created a MSSQL view with a query to a linked OraDB Table. The linked Server as well as the view is working correctly.
- then i created a MSSQL table with an insert trigger, which has a query on the created view. I can execute this query whithin the query analyzer.

--> when i try to insert a new row into the table with the trigger, following message appears:

"OLE DB-Fehlertrace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b]."

Does anyone has a clue?

Thanks a lot
Dani

 

[mrdenny]

I beleive that doing what you want to do requires that support for distributed transactions be setup on both machines. Is the Oracle box a Windows box or a *nix box?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[medmed]

thanks,

Oracle is Setup on a Windows Machine, if this was your question.

I don't understand why i can run a query against this view from the linked server out of query-analyzer, but cannot run the same query against the same view out of a MSSQL insert trigger.

Dani

 

[mrdenny]

Because when you run the basic insert by it self, no transactional integrity between the machines needs to exist. When the trigger fires off the insert statement, if the trigger fails then the insert into the table must also fail. If after the trigger completes the insert into the SQL Server table fails, then the Oracle insert statement must also be rolled back to maintain the transactional consistancy between the machines.

On both Windows machines you will need to enable the DTS Network Access in Add/Remove programs, and configure it in the Component Services applet in the Administrative Tools window. Oracle may also need to have some of there Distributed Transaction software configured (you'll need to check with the folks in the Oracle forums about that).

Unfornitually this isn't going to be the easiest thing to get setup.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[medmed]

Hi,

below my Trigger. I actually do not change anything whithin the Oracle DB, just read.

the vi_relFirma is the view i created in MSSQL with a select statement on the linked server (Oracle). Means this view shows Oracle Data.

CREATE TRIGGER t_check_FirmaNr ON dbo.T_Firma
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
SELECT COUNT(FirmaNr) FROM vi_relFirma WHERE FirmaNr = (SELECT FNr FROM INSERTED)
IF 1 > (SELECT COUNT(FirmaNr) FROM vi_relFirma WHERE FirmaNr = (SELECT FNr FROM INSERTED))
BEGIN
RAISERROR ('Number does not exist in vi_relFirma', 15, 1);
ROLLBACK TRANSACTION;
END;
END;

 

[mrdenny]

You don't have to do any data changes to invoke a transaction. You can try specifying a WITH (NOLOCK) hint on the tables, but I'm 99% sure that it will not help, as the SQL engine it self is escelating the transaction to a distributed transaction automatically.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com