Solved

DSN-Less connection does not work but DSN connection does

Posted on 2003-11-07
4
592 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
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: 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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

685 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