Database error code: 7391

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)"
                             ELSE Trim(description)
                        END description
                        FROM codes
                        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.
wspace
wspaceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

diasroshanCommented:
hi,

ok... so ur saying that ur dddw is utlimately populating with correct data regardless of all the errors that pop up...

lets start with sorting out a few error first...

now the question abt retrieval argument window appearing...
do the following...
Open the dddw....
Now go to View-->Data

Now come to the data section and right click... select Insert Row from the menu... Save the dddw...

Now run ur code... see if any errors have reduced...

also post the code ur using to intilialize ur dddw....


Cheers,
Rosh
0
diasroshanCommented:
hi,

i also assume ur using OLEDB connection...

did u try using native or ODBC connection and see if the errors persist...

One more point...

i dont think u need any function to get the table name of any table from another SQL Serve db to access it...

u can do it directly as follows...
Select * from
servername.databasename.user.tablename ;

Also refer...
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_10194731.html?query=%22remote+database%22&topics=42

Cheers,
Rosh
0
diasroshanCommented:
hi,
one more point...
if its on a local server...
SELECT * FROM [Database].[tableowner].[tablename]

if u use this i guess u will not encounter any errors... and there is no need to use a function...

u can also create a view on...SELECT * FROM [Database].[tableowner].[tablename]
and reference the view to create ur dddw...

Cheers,
Rosh
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

wspaceAuthor Commented:
Rosh,

I tried the [Database].[tableowner].[tablename] method in the SQL and that corrected the problem.

Thanks for your imput on this issue,

wspace
0
wspaceAuthor Commented:
Rosh,

The use of Select * from servername.databasename.user.tablename works and the performance is good as long as the database is MSSQL but when the database is INFORMIX the performance really bad/slow.  I would prefer to use OpenQuery to capitalize on the good performance it provides.

The linked server properties for the Informix database are as follows:

Provider Name: Microsoft OLE DB Provider for ODBC Drivers
Product Name: MSDASQL

Any other ideas why I might be getting the error?

wspace
0
diasroshanCommented:
hi,

i am confused...
u said... <I am in the process of converting a PB application's back end from Informix to SQL Server 2000>...

i understand u want to migrate from Informix to MSSQL...

so why are u askin for....
<when the database is INFORMIX the performance really bad/slow>...

well, u want to use informix or MSSQL....???

is there anything else i need to know like... u want ur application to work on 2 DBs at the same time...

let me know...

btw, u can use synonms for Informix and a view created on [Database].[tableowner].[tablename] for MSSQL...

u can create the synonym in Informix with the same name as a view for  [Database].[tableowner].[tablename] in MSSQL... i guess ur problem will solve...

Cheers,
Rosh
0
wspaceAuthor Commented:
Sorry for the confusion and not being completely clear.

I am migrating from Infromix to MSSQL but my application has need to access data from another application's database that is also being migrated from Informix to MSSQL and to access data from a financial and HR application that will continue using an Informix database. So I my application  has need to work on its migrated MSSQL database (X), another applications migrated MSSQL database (Y), and a third application's database that will remain running against an Informix database (Z):

        MSSQL db X has link to MSSQL db Y (local server)
        MSSQL db X has link to INFORMIX db Z (remote server)

I hope this helps clear up what I need.

Thanks,
wspace
0
diasroshanCommented:
hi,

ok... now i got the issue...

what u can do is create the synonym in Informix with the same name as a view for  [Database].[tableowner].[tablename] in MSSQL...

so the trick is to create a synonym with the name Eg. 'test' in Informix and
create a view on [Database].[tableowner].[tablename] with the name 'test' in MSSQL...


Cheers,
Rosh
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
diasroshanCommented:
hi,


any luck...

do ask if u need any assistance...

Cheers,
Rosh
0
diasroshanCommented:
Hi,

<Waiting>


Cheers,
Rosh
0
wspaceAuthor Commented:
Rosh,

Sorry for the long delay in responding.  I was diverted to other tasks for a short time.

First, I found that by setting SQLCA.Autocommit = TRUE just before retrieving data and then Setting it back to FALSE after the retrieve eliminated the error.  Performance is still slow so I need to look into ways that I can improve it.

Second, I will still try your suggestion of creating a synonym in informix and then creating a view in MSSQL having the same name as the informix synonym.  I will see if this helps improve performance.

Thanks,
wspace
0
diasroshanCommented:
hi,

need any more assistance...

Cheers,
Rosh
0
diasroshanCommented:
Hi,

wspace was given the solution he was lookin for... surprising why he didnt award the points....

So points here go to me...

Cheers,
Rosh
0
wspaceAuthor Commented:
Once again, I'm sorry for not getting back to respond.  I have been diverted to other tasks and this task has taken a lower priority.  I do appreciate all suggestions and when I get back on task, I will attempt what has been suggested.

Regards,
wspace
0
wspaceAuthor Commented:
As I mentioned in my previous post, I have been diverted to other tasks with higher priority and have not had time to experiment with the suggestions given.  And in reply to Rosh's last post, I am not certain that I have been given the solution I was looking for and that is why I haven't awarded any points.  In my mind, the issue is still open. Since the question needs to be closed, I will re-post it at a later date if necessary.  My thanks go out to Rosh for the input given.

Regards,
wspace
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.