October 25, 2005 at 10:23 am
Hi
I am using SQL Server 2000
I have a function which returns a value from an Oracle server, as shown below. I need the function to populate a field in a (temp) table.
In Query Analyser I have written the following:
select *,dbo.fn_findptid(course_code,group_code)as PT
into #MyTempTable
from #sty1_ft
where group_code is not null
Unfortunately it returns the error message:
'The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.' And cites Msg 7391
All works fine without the 'into #MyTempTable'.
I can't work out from BOL or other searches quite how to overcome this. Does anybody have any ideas, or has solved this in the past?
Many thanks
Paul
---------------------
CREATE FUNCTION fn_FindPTid (@CoC varchar(10), @GC varchar(10))
RETURNS int
AS
BEGIN
DECLARE @FindPT int
set @FindPT = (
select Empnumber
from OPENROWSET('MSDAORA',
'studs';'pass';'pass','select Event_Number,Person_code,Type,Session_Code,Person_Function
from FES.EVENT_DETAILS') as ED_link
inner join tempebs_pt
on ed_link.person_code=tempebs_pt.person_code
where exists (select event_number
from OPENROWSET('MSDAORA',
'studs';'pass';'pass','select Event_Number,uio_group_code, uio_ui_code from FES.EVENT_DETAILS')
where uio_group_code = @gc
and uio_ui_code = @coc
and ed_link.event_number=event_number)
and type ='person'
and session_code='05/06'
and person_function='grp tut')
RETURN(@FindPT)
END
October 25, 2005 at 10:36 am
Is the server OS windows 2003 or client OS windows XP SP2?
October 25, 2005 at 1:03 pm
If WIN2003 or XPSP2 it may be a problem with MS DTC.
October 26, 2005 at 4:39 am
SOLUTION TO THE PROBLEM
Many thanks for your replies, and it appears that the solution was on the Microsoft website. However, I think it is explained quite concisely below.
I managed to find this on the Web, and hope that it might help others who encounter this problem. It works fine. My thanks to the author....
-------------------
jms8222 (Programmer) 8 Apr 04 9:01
I have FINALLY been able to find the solution to this error!!
In the end, it turns out that the solution was right before me all along (DOH!)
First, I needed to update the registry settings (as per Microsoft knowledge base article 280106), but I had to adjust it slightly because we are using Oracle 9!
These are the settings I modified:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient9.dll"
"OracleSqlLib"="orasql9.dll"
"OracleOciLib"="oci.dll"
(I also had the machine rebooted before I was able to see the settings take effect. Though now, as an afterthought, I wonder if just stopping and starting the DTS would have done the trick. Oh well.)
Second, I had to include the following line in my trigger:
SET XACT_ABORT ON
This statement specifies to SQL Server to automatically roll back the current transaction if the SQL statement raises a run-time error.
I can only hope that my posting of this solution can help someone out there avoid all the trials I've gone through with this issue!!! Thanks to all that provided feedback!
toekneel (Programmer) 8 Jun 05 17:27
Thanks for your foresight in thinking someone else might have this issue!
In my environment, I had to also set the following:
SET ANSI_NULL_DFLT_ON ON
SET ANSI_WARNINGS ON
Again, THANKS!!!!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply