I am in the process of converting a PB application's back end from Informix to SQL Server 2000. The
appliction has need to retrieve data from another SQL Server 2000 db. In Informix, this was
accomplished by creating an synonym to the other databases table and then the synonym could be
used like a table in any query. Since SQL Server 2000 does not support synonyms, I have decided to
use a user difined function that returns a table where data is selected into it from a linked
server. The following is the udf properties:
CREATE FUNCTION dbo.udf_Codes ()
RETURNS table AS return
SELECT code, description
FROM OpenQuery(maintenance,'SELECT Trim(code) code,
CASE WHEN Trim(code) = "31" THEN "Access Control (Badging Office)"
WHERE department = "35"
AND area = "3"
AND code_type = "APPROP"
AND code <= 41')
The function works great when used in a SQL Query Analyzer query or in a PowerBuilder Database
Painter query but when attempting to use the function within a datawindow query (dddw), the
following error is generated at runtime when the datawindow is being initialized:
Database error code: 7391
Database error Message:
Select error: The operation could not be performed because the OLE DB Provider 'MSDASQL' was
unable to begin a distributed transaction.
After clicking OK to the error message, a Specify Retrieval Arguments window appears. I click Cancel on this window and control is passed to the window where the dddw resides. I then click on the dddw and the error appears again. I click OK to the error message and click awaw from the dddw to take focus off it and then after clicking on the dddw once more, the dropdown appears with the correct data populated in it.
Please advise me as to what I can do to resolve this problem.
Thanks in advance.