Using ODBC Provider for Oracle from Microsoft

[cRODEEkrank]

I'm currently building an ASP application which interacts with a Microsoft database with linked tables to Oracle. I've created a connection string using the ODBC Oracle provider from Microsoft and I've received the following error:

TNS:could not resolve service name

Here's my connection code:
Set conn = server.CreateObject("ADODB.Connection&quot
strConnInfo = "Provider=msdaora;"
strConninfo = strconninfo & "Data Source=OracleContractorDB;"
strConninfo = strconninfo & "User Id=8789;"
strConninfo = strconninfo & "Password=8789"
conn.Open strConnInfo

Just as some background information, I can't access the Oracle database, as I'm not permitted to but do have permission to link tables via Access, so this is really my only option. Any thoughts??

CK

 

[Veep]

I'd be interested in an answer to this one too. I've got two servers. One for staging and one for production. The Oracle client is allegedly installed identically on both. I can access the Oracle instance with no problems on the staging server but get the above error on the production box. A help request to website support returned "duh".

My solution was to create a linked server of the oracle instance and run a scheduled DTS package that brings over the data that I anticipate needing. If you are able to do that another way to get at the data is to create a view on the Oracle database using the T-SQL OpenQuery syntax and call for records from the view on your web page. I don't do it that way because it's way slower that having local tables.

 

[cRODEEkrank]

Unfortunately I don't have (and can't have) access to the Oracle DB, so I can't create any views on it. I'm hoping for a solution where maybe my connection string is incorrect or something.

 

[Veep]

Unfortunately your connection string is fine.