May 15, 2003 at 8:12 am
Stored proc used to update Oracle table from SQL Server:
CREATE PROCEDURE [nontouch].[insert_sal_emp_hours] AS
INSERT INTO OPENQUERY([risexw.caemilusa_test.local],
'SELECT * FROM LaborOwner.salary_employee_hours_backup')
SELECT site_code,
week_ending_date,
employee_id,
record_date,
project_number,
labor_type_code,
regular_hours,
overtime_hours,
sunday_hours,
casual_hours,
rate_used,
processed_flag,
date_added,
added_id,
comment_text,
charged_obs,
cost_element,
charged_bid_category,
home_obs,
home_bid_category,
shift_diff_hours,
shift_diff_type,
labor_type,
shift_ot_hours from nontouch.salary_employee_hours
where trans_to_ora = '1'
IF @@ERROR <> 0
BEGIN
RETURN 1
END
ELSE BEGIN
RETURN 0
END
GO
Stored Proc used to reset data transfer flag in SQL Server:
CREATE PROCEDURE [nontouch].[reset_sal_emp_hours_flag] AS
update nontouch.salary_employee_hours set trans_to_ora='0'
IF @@ERROR <> 0
BEGIN
RETURN 1
END
ELSE BEGIN
RETURN 0
END
GO
Stored proc used to ensure that both transactions occur as a team or not at all:
CREATE PROCEDURE [nontouch].[trans_sal_emp_hours_to_ora] AS
Declare @r tinyint
begin tran
exec @r = insert_sal_emp_hours
if @r <> 0 begin
rollback tran
return
end
exec @r = reset_sal_emp_hours_flag
if @r <> 0 begin
rollback tran
return
end
commit tran
GO
You may receive error 7391 while working with linked servers.
In SQL Server 2000, the error message you receive is:
Server: Msg 7391, Level 16, State 1, <ObjectName>, Line xx
The operation could not be performed because the OLE DB provider '%ls'
was unable to
begin a distributed transaction.
Common Resolutions
Here is a list of the most common resolutions for error 7391.
Note: It is a good idea to limit your code in a transaction that
involves a distributed query only to the
remote server. In most cases, you may separate locally executed steps
from remote steps to reach
this goal.
This is from Technet regarding error 7391. Do you think because access
to the linked server has been combined with access to the local SQL
server caused this error to occur? As I said each piece works alone -
just not when I join the 2 steps.
Need both steps to be done together to ensure that they happen as a team or not at all. The problem seems to be that when you perform a linked server transaction combined with local database transaction SQL Server has issues. Any experience out there with writes to Oracle from SQL Server?
May 15, 2003 at 10:58 am
Try doing:
SET XACT_ABORT ON
before BEGIN TRANSACTION in your master procedure (the one that calls the other two). This may help.
RD Francis
R David Francis
May 15, 2003 at 11:00 am
Oh, and
SET XACT_ABORT OFF
after you rollback/commit.
Note: this tells SQL Server to rollback everything if any enclosed transaction fails. I think I have noticed stored procedures running as implicit transactions.
RD Francis
R David Francis
May 15, 2003 at 11:42 am
Added SET XACT_ABORT ON/SET XACT_ABORT OFF to the stored proc and no change. Still the same error message.
May 22, 2003 at 3:01 pm
Did you ever get this resolved? I had this exact same problem between 2 linked sql servers and was never able to get it to work. I use SET XACT_ABORT ON everywhere to no avail. Would love to be able to use triggers for updating data between the servers.
June 4, 2003 at 12:58 pm
Response from Microsoft:
280106 HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in SQL Server
http://support.microsoft.com/?id=280106
Your Registry settings should be:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"
MS SQL Server HKLM\SOFTWARE\Microsoft\MSDTC\MTxOCI settings default to speak to old Oracle 7.3. Must update registry to talk with Oracle 8i. Don't even bother with Oracle 9i driver settings - MS does not support that anyway. Must use 8i drivers to get to Oracle 91 database.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply