Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 606
  • Last Modified:

DSN-Less connection does not work but DSN connection does

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
gizman2
Asked:
gizman2
  • 2
1 Solution
 
seazodiacCommented:
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
 
seazodiacCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now