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?
gizman2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.