SQL Server using data from Oracle

  • 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

     

     

  • Is the server OS windows 2003 or client OS windows XP SP2?

  • If WIN2003 or XPSP2 it may be a problem with MS DTC.

    http://support.microsoft.com/?kbid=839279

  • 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