Solved

DSN-Less connection does not work but DSN connection does

Posted on 2003-11-07
4
595 Views
Last Modified: 2007-12-19
I am trying to work around a problem with the oracle database link issue.  The first issue is that when I am using the following SQL statment in my ASP code ,which works just fine in PLSQL:

select * from (select p.facility, p.chart, p.point_dttm, p.value_1, p.value_2,w.up_ctrl, w.lo_ctrl, w.up_spec, w.lo_spec, w.mean from smsdw.spc_point@smsdwdl6.itg.ti.com p, tbl_weco w Where p.chart = 'AR-MT20NR' and p.facility = 'DL-DMD-1' and p.facility = w.facility and p.chart = w.chart and p.chart_rev = w.chart_rev Order by p.point_dttm desc) Where RowNum <= 200 Order by point_dttm desc;

I get this error using a DSN connection:
ORA-02041: client database did not begin a transaction.  I know that when I tested this, I removed the linked database and any select statement will work.  So I figured that the DSN is not passing the Link information that is set up in the server so I wanted to test the select statement using a DSN-Less connection but here's the error:

ORA-00911: invalid character

Here is my DSN connection string:
Set Conn = Server.CreateObject("ADODB.Connection")
conn.Open "dsn=oraSMSDL6;uid=smsdl6;pwd=******;"


Here's my DSN-less connection string:
conn.open "Provider=MSDAORA; Data Source=dmdparts;User ID=smsdl6;Password=******"

Any ideas?
0
Comment
Question by:gizman2
[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
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 500 total points
ID: 9704941
are you using oracle8i?

then here is the reported bug in metalink and the solution:

Problem Description
-------------------
 
When trying to connect through a database link using ODBC 8.1.X, you
receive the following error:
 
         ORA-2041 client did not begin a transaction.
 
Or
 
SQL state: s1000  
Native error code 2041
 
Driver message: [ORACLE] [ODBC] [ORA] ORA-02041: Client database did not  
                begin a transaction.
 
 
This problem occurs in the Oracle ODBC Test utility.  SQL*Plus does not  
exhibit the problem.
   
 
Solution Description
--------------------
 
Upgrade to the latest ODBC driver and enable the option 'Disable MTS  
support' in the DSN configuration.
 
To do this, perform the following steps:
 
1. Click Start -> Settings -> Control Panel -> ODBC Administrator
2. Select the DSN you are using, then click the Configure button.
3. You should see the 'Disable MTS support 'option listed in the 'Workarounds'
 section.  If you do not see this option, you are probably not on the latest  
version.
4. It is sometimes necessary to either drop and recreate the DSN and/or
 table link in MSAccess, as MSAccess appears to cache DSN settings.
 
The latest version can be obtained from MetaLink, by clicking on the
following:
 
      Technical Libraries -> Tools -> Programmatic Interfaces ->  
         ODBC -> Latest Versions
 

0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9704951
also this:

fact: Oracle ODBC Driver 8.1
fact: MS Windows
symptom: Selecting a table over a database link fails
symptom: ORA-02041: client database did not begin a transaction
cause: <Bug:1041336>:
Querying a table over a dblink gives ORA-02041.

This is fixed in 8.1.5.5



fix:

Apply the patch and modify the Data Source Name (DSN) setting.
1.  Apply the latest Oracle ODBC Driver patch.  This can be found on Metalink.
2.  Set the "Disable MTS Support" option under the Workaround Options
for the DSN.

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

Title # Comments Views Activity
Error querying database link from Oracle 10g to Postgresql 7 138
Procedure syntax 5 64
Oracle cursor lifecycle inside procedure. 2 40
how to tune the query 17 86
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

751 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