Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Location
    BA, Argentina
    Posts
    39

    Unanswered: Distributed transaction error, need help please!!

    Hi, I have configured a linked server, and i have a procedure which makes an UPDATE in a local table using the data in the linked server.

    Specifically, I have a function which checks if a given code exists in a linked server's table. The UPDATE changes the value of a column in a local table, if the function returns 1.

    I've run the procedure and it gave an error after a few hours cause a simple conversion error inside the function. I solved the error. After this, the procedure did not work more. It gives me the following message:

    Server: Msg 7391, Level 16, State 1, Procedure EXISTEONC, Line 16
    The operation could not be performed because the OLE DB provider 'MSDASQL'
    was unable to begin a distributed transaction.

    (EXISTEONC is the function, and in the line 16 there is an OPENQUERY)
    Im sure MSDTC is working... i'm lost because i dont know why it worked the first time and not now. Ive also wrote the function again as it was before, but it still doesent works.

    Thanks a lot...

  2. #2
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    don't mean to ask a silly question but have you recompiled the stroed procedure ?

  3. #3
    Join Date
    Oct 2003
    Location
    BA, Argentina
    Posts
    39
    Do you mean opening the procedure and closing it again??
    Yes, I did that...

  4. #4
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    i'm trying to understand - is the function being called from a stroed proc

    wording is not too clear

  5. #5
    Join Date
    Oct 2003
    Location
    BA, Argentina
    Posts
    39
    i'll try to explain me...

    I have a stored proc, it looks like this:

    BEGIN TRANS
    UPDATE CodeTable SET Found='Yes' WHERE EXISTEONC(ONCCode)=1
    COMMIT

    the function EXISTEONC takes a code (ONCCode is a field in CodeTable) and searchs it in a table in the linked server. If it was found, it returns 1.

    The error appears in line 16 of the function (in the openquery statement to search the code), but only if i execute the Procedure:
    If i write this in the Analyzer:
    PRINT EXISTEONC('1234')
    there is no error, i think, the error appears only if the function is executed inside a transaction ...

  6. #6
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    First thing is I would be 100% sure that DTC is running ok

    If this is OK go backa and drop and recreate everything i.e. stroed proc and the function

    If this is still not working then it may be some sort of corruption in the data access components on your server thats messed up the DLL's responsible for OLE DB.

    Not sure how to approach this - Reinstall the MDAC???

    Any ideas folks ??

  7. #7
    Join Date
    Oct 2003
    Location
    BA, Argentina
    Posts
    39
    I'm 100% sure DTC is running (i stopped it, and restarted it about 500000 times).
    I compiled the function and the procedure again...
    With SQL server I can see the data in the linked server. Remember: if I execute the function outside the UPDATE, for example:
    PRINT dbo.EXISTEONC('1111')
    it works, so, the connection is working and the data is not corrupted.

    but, the *&#%#@ procedure still doesent work...

    I forgot to say this: the server database is a Sybase Adaptiver Server Anywhere 6.0, and I connect to it through an ODBC... i access the ODBC in Sql Server 2000.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •