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.
UPDATE CodeTable SET Found='Yes' WHERE EXISTEONC(ONCCode)=1
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:
there is no error, i think, the error appears only if the function is executed inside a transaction ...
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:
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.