Solved

DSN-Less connection does not work but DSN connection does

Posted on 2003-11-07
4
583 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
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 500 total points
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now