?
Solved

Database error code: 7391

Posted on 2005-03-21
18
Medium Priority
?
411 Views
Last Modified: 2013-12-26
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
0
Comment
Question by:wspace
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
18 Comments
 
LVL 18

Expert Comment

by:diasroshan
ID: 13598162
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
 
LVL 18

Expert Comment

by:diasroshan
ID: 13598243
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
 
LVL 18

Expert Comment

by:diasroshan
ID: 13598257
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:wspace
ID: 13602895
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
 

Author Comment

by:wspace
ID: 13606731
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
 
LVL 18

Expert Comment

by:diasroshan
ID: 13608576
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
 

Author Comment

by:wspace
ID: 13611285
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
 
LVL 18

Accepted Solution

by:
diasroshan earned 1500 total points
ID: 13611718
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
 
LVL 18

Expert Comment

by:diasroshan
ID: 13618994
hi,


any luck...

do ask if u need any assistance...

Cheers,
Rosh
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13638299
Hi,

<Waiting>


Cheers,
Rosh
0
 

Author Comment

by:wspace
ID: 13671617
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
 
LVL 18

Expert Comment

by:diasroshan
ID: 13778905
hi,

need any more assistance...

Cheers,
Rosh
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 15001150
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
 

Author Comment

by:wspace
ID: 15005971
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
 

Author Comment

by:wspace
ID: 15024224
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our object-oriented world the class is a minimal unit, a brick for constructing our applications. It is an abstraction and we know well how to use it. In well-designed software we are not usually interested in knowing how objects look in memory. …
Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question