Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Database error code: 7391

Posted on 2005-03-21
18
Medium Priority
?
415 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
  • 9
  • 6
15 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
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!

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.
Suggested Courses
Course of the Month13 days, 17 hours left to enroll

580 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